DATE_FORMAT(date, format)

SELECT 
    DATE_FORMAT(NOW(), '%Y-%m-%d') as date_standard,        -- 2024-04-15
    DATE_FORMAT(NOW(), '%d/%m/%Y') as european_date,        -- 15/04/2024
    DATE_FORMAT(NOW(), '%m/%d/%Y') as us_date,              -- 04/15/2024
    DATE_FORMAT(NOW(), '%Y年%m月%d日') as chinese_date;     -- 2024年04月15日

SELECT 
    DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') as datetime_full,     -- 2024-04-15 14:30:45
    DATE_FORMAT(NOW(), '%W, %M %D, %Y') as readable_date,         -- Monday, April 15th, 2024
    DATE_FORMAT(NOW(), '%b %d, %Y at %h:%i %p') as friendly_time; -- Apr 15, 2024 at 02:30 PM

SELECT 
    DATE_FORMAT(NOW(), '%H:%i') as time_24h,           -- 14:30
    DATE_FORMAT(NOW(), '%h:%i %p') as time_12h,        -- 02:30 PM
    DATE_FORMAT(NOW(), '%T') as time_full;             -- 14:30:45

SELECT 
    DATE_FORMAT(NOW(), '%Y-Q%q') as quarter,           -- 2024-Q2
    DATE_FORMAT(NOW(), 'Week %U of %Y') as week_num;   -- Week 15 of 2024

-- 按年月分组统计订单
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') as month_group,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders 
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month_group;

-- 用户注册时间格式化
SELECT 
    username,
    DATE_FORMAT(created_at, '%M %D, %Y') as join_date,
    DATE_FORMAT(created_at, 'Joined %b %Y') as short_join_date
FROM users;

-- 系统日志时间戳格式化
SELECT 
    log_id,
    DATE_FORMAT(log_time, '%Y-%m-%d %H:%i:%s') as log_datetime,
    message
FROM system_logs
WHERE DATE_FORMAT(log_time, '%Y-%m-%d') = '2024-04-15';

SELECT TIME_FORMAT(NOW(), '%H:%i:%s') as current_time;  -- 14:30:45

SELECT STR_TO_DATE('15/04/2024', '%d/%m/%Y') as date_value;  -- 2024-04-15
SELECT STR_TO_DATE('02:30 PM', '%h:%i %p') as time_value;    -- 14:30:00

SELECT 
    FROM_UNIXTIME(1713174645) as normal_date,                          -- 2024-04-15 14:30:45
    DATE_FORMAT(FROM_UNIXTIME(1713174645), '%Y-%m-%d %H:%i') as formatted_date;  -- 2024-04-15 14:30

-- 根据日期自动显示季度信息
SELECT 
    order_date,
    CASE 
        WHEN DATE_FORMAT(order_date, '%m') IN ('01','02','03') THEN DATE_FORMAT(order_date, '%Y-Q1')
        WHEN DATE_FORMAT(order_date, '%m') IN ('04','05','06') THEN DATE_FORMAT(order_date, '%Y-Q2')
        WHEN DATE_FORMAT(order_date, '%m') IN ('07','08','09') THEN DATE_FORMAT(order_date, '%Y-Q3')
        ELSE DATE_FORMAT(order_date, '%Y-Q4')
    END as quarter
FROM orders;

-- 使用SET lc_time_names支持多语言
SET lc_time_names = 'zh_CN';  -- 设置为中文

SELECT DATE_FORMAT(NOW(), '%M %d, %Y') as chinese_month;  -- 四月 15, 2024

SET lc_time_names = 'ja_JP';  -- 设置为日文

SELECT DATE_FORMAT(NOW(), '%M %d, %Y') as japanese_month; -- 4月 15, 2024

-- 查询今天的数据
SELECT * FROM logs 
WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d');

-- 查询本月的数据
SELECT * FROM orders 
WHERE DATE_FORMAT(order_date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m');

SELECT * FROM orders 
WHERE DATE_FORMAT(order_date, '%Y-%m-%d') = '2024-04-15';

SELECT * FROM orders 
WHERE order_date >= '2024-04-15' AND order_date < '2024-04-16';

-- 创建包含格式化日期的视图
CREATE VIEW order_summary AS
SELECT 
    order_id,
    order_date,
    DATE_FORMAT(order_date, '%Y-%m') as order_month,
    DATE_FORMAT(order_date, '%Y-Q%q') as order_quarter
FROM orders;

SELECT 
    DATE_FORMAT(COALESCE(order_date, NOW()), '%Y-%m-%d') as safe_date
FROM orders;

SELECT 
    DATE_FORMAT(CONVERT_TZ(NOW(), '+00:00', '+08:00'), '%Y-%m-%d %H:%i:%s') as beijing_time;

-- 生成最近7天的日期列表
SELECT 
    DATE_FORMAT(DATE_SUB(NOW(), INTERVAL n DAY), '%Y-%m-%d') as date_list
FROM (
    SELECT 0 as n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
) numbers;