顺子の杂货铺
生命不息，折腾不止，且行且珍惜~
        找回密码
当前位置：順子の杂货铺 > BigData > 正文

10-日期格式转换

2026-01-18 分类：BigData / BLOG / Database / SQL 阅读(6) 评论(0)
DMIT VPS

日期格式转换

日期格式转换是数据处理中的常见需求，本篇介绍各数据库的日期格式转换方法。

📊 支持情况

数据库 DATE_FORMAT TO_CHAR FORMAT_DATE 备注
MySQL ✅ – ✅ 完整支持
Oracle – ✅ ✅ TO_CHAR函数
ClickHouse – ✅ ✅ formatDateTime
Hologres – ✅ ✅ 兼容PG
MaxCompute – – ✅ FROM_UNIXTIME
Hive – – ✅ date_format

1. 日期转字符串

MySQL DATE_FORMAT

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');           -- 2024-01-14
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');        -- 2024年01月14日
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');           -- 14:30:45
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');  -- 2024-01-14 14:30:45
SELECT DATE_FORMAT(NOW(), '%Y%m%d');             -- 20240114
SELECT DATE_FORMAT(NOW(), '%W');                 -- Sunday（星期名）
SELECT DATE_FORMAT(NOW(), '%a');                 -- Sun（缩写）

Oracle TO_CHAR

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;           -- 2024-01-14
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') FROM dual; -- 2024年01月14日
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM dual;           -- 14:30:45
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') FROM dual;             -- 20240114
SELECT TO_CHAR(SYSDATE, 'DAY') FROM dual;                  -- SUNDAY（星期全名）
SELECT TO_CHAR(SYSDATE, 'DY') FROM dual;                   -- SUN（缩写）

ClickHouse formatDateTime

SELECT formatDateTime(NOW(), '%Y-%m-%d');           -- 2024-01-14
SELECT formatDateTime(NOW(), '%Y年%m月%d日');        -- 2024年01月14日
SELECT formatDateTime(NOW(), '%H:%M:%S');           -- 14:30:45

Hive / MaxCompute date_format

SELECT date_format(NOW(), 'yyyy-MM-dd');           -- 2024-01-14
SELECT date_format(NOW(), 'yyyy年MM月dd日');        -- 2024年01月14日
SELECT date_format(NOW(), 'HH:mm:ss');             -- 14:30:45
SELECT date_format(NOW(), 'yyyyMMdd');             -- 20240114
SELECT date_format(NOW(), 'EEEE');                 -- Sunday

2. 字符串转日期

MySQL STR_TO_DATE

SELECT STR_TO_DATE('2024-01-14', '%Y-%m-%d');           -- 2024-01-14
SELECT STR_TO_DATE('2024年01月14日', '%Y年%m月%d日');    -- 2024-01-14
SELECT STR_TO_DATE('14/01/2024', '%d/%m/%Y');           -- 2024-01-14
SELECT STR_TO_DATE('20240114', '%Y%m%d');               -- 2024-01-14

Oracle TO_DATE

SELECT TO_DATE('2024-01-14', 'YYYY-MM-DD') FROM dual;
SELECT TO_DATE('2024年01月14日', 'YYYY"年"MM"月"DD"日"') FROM dual;
SELECT TO_DATE('14/01/2024', 'DD/MM/YYYY') FROM dual;
SELECT TO_DATE('20240114', 'YYYYMMDD') FROM dual;

ClickHouse toDate / parseDateTime

SELECT toDate('2024-01-14');                                     -- 2024-01-14
SELECT parseDateTime('20240114', 'yyyyMMdd');                    -- 2024-01-14
SELECT parseDateTime('2024-01-14 14:30:45', 'yyyy-MM-dd HH:mm:ss');

Hive / MaxCompute to_date

SELECT to_date('2024-01-14');                                    -- 2024-01-14
SELECT to_date('2024年01月14日', 'yyyy年MM月dd日');               -- 2024-01-14

3. 时间戳转换

MySQL

-- UNIX时间戳转日期
SELECT FROM_UNIXTIME(1705225800);              -- 2024-01-14 14:30:00
SELECT FROM_UNIXTIME(1705225800, '%Y-%m-%d');  -- 2024-01-14

-- 日期转UNIX时间戳
SELECT UNIX_TIMESTAMP('2024-01-14 14:30:00');  -- 1705225800
SELECT UNIX_TIMESTAMP();                       -- 当前时间戳

Oracle

-- 时间戳转日期
SELECT TO_DATE('1970-01-01', 'YYYY-MM-DD') + (SYSTIMESTAMP - TIMESTAMP '1970-01-01') / 86400
FROM dual;

-- 使用NUMTODSINTERVAL
SELECT SYSTIMESTAMP - NUMTODSINTERVAL(1705225800, 'SECOND') FROM dual;

ClickHouse

SELECT toDateTime(1705225800);                              -- 2024-01-14 14:30:00
SELECT toUnixTimestamp(toDateTime('2024-01-14 14:30:00'));  -- 1705225800

Hive / MaxCompute

SELECT from_unixtime(1705225800);                           -- 2024-01-14 14:30:00
SELECT from_unixtime(1705225800, 'yyyy-MM-dd');             -- 2024-01-14
SELECT unix_timestamp('2024-01-14 14:30:00');               -- 1705225800
SELECT unix_timestamp();                                    -- 当前时间戳

4. 格式码对照表

含义 MySQL Oracle Hive/ClickHouse
4位年 %Y YYYY yyyy
2位年 %y YY yy
月(01-12) %m MM MM
月名 %M MONTH MMMM
月名缩写 %b MON MMM
日(01-31) %d DD dd
星期名 %W DAY EEEE
星期缩写 %a DY E
小时(24) %H HH24 HH
小时(12) %h HH –
分钟 %i MI mm
秒 %s SS ss
季度 %q Q Q
周数 %u WW w
一年中的第几天 %j DDD D

5. 常用格式转换

日期格式标准化

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS standardized_date
FROM orders;

-- Oracle
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS standardized_date
FROM orders;

中文格式转换

-- MySQL
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点%i分');  -- 2024年01月14日 14点30分

-- Oracle
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日" HH24"点"MI"分"') FROM dual;

-- Hive
SELECT date_format(NOW(), 'yyyy年MM月dd日 HH点mm分');

时间提取

-- 提取年/月/日/时/分/秒
-- MySQL
SELECT
    YEAR(NOW()) AS year,
    MONTH(NOW()) AS month,
    DAY(NOW()) AS day,
    HOUR(NOW()) AS hour,
    MINUTE(NOW()) AS minute,
    SECOND(NOW()) AS second,
    QUARTER(NOW()) AS quarter,
    WEEK(NOW()) AS week;

-- Oracle
SELECT
    EXTRACT(YEAR FROM SYSDATE) AS year,
    EXTRACT(MONTH FROM SYSDATE) AS month,
    EXTRACT(DAY FROM SYSDATE) AS day,
    EXTRACT(HOUR FROM SYSTIMESTAMP) AS hour,
    EXTRACT(MINUTE FROM SYSTIMESTAMP) AS minute,
    EXTRACT(SECOND FROM SYSTIMESTAMP) AS second
FROM dual;

6. 实战案例

场景1：报表日期格式

-- MySQL
SELECT
    DATE_FORMAT(order_date, '%Y-%m-%d') AS order_date,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m-%d');

场景2：按周汇总

-- MySQL - 周起始于周一
SELECT
    DATE_FORMAT(DATE_SUB(order_date, INTERVAL WEEKDAY(order_date) DAY), '%Y-%m-%d') AS week_start,
    COUNT(*) AS weekly_orders
FROM orders
GROUP BY week_start;

-- Oracle
SELECT
    TRUNC(order_date, 'IW') AS week_start,
    COUNT(*) AS weekly_orders
FROM orders
GROUP BY TRUNC(order_date, 'IW');

场景3：中文报表

-- Hive
SELECT
    CONCAT(
        date_format(stat_date, 'yyyy年MM月dd日'),
        ' - ',
        date_format(DATE_ADD(stat_date, 6), 'yyyy年MM月dd日')
    ) AS week_range,
    SUM(sales) AS weekly_sales
FROM weekly_stats
GROUP BY date_format(stat_date, 'yyyy年MM月dd日');

场景4：日志时间解析

-- 解析 Apache 日志格式
-- [14/Jan/2024:14:30:45 +0800]
-- MySQL
SELECT
    STR_TO_DATE(
        SUBSTRING_INDEX(SUBSTRING_INDEX(log_line, '[', -1), ']', 1),
        '%d/%b/%Y:%H:%i:%s'
    ) AS access_time
FROM access_logs;

⚠️ 注意事项

1. 时区问题

-- MySQL
SET time_zone = '+08:00';
SELECT NOW();  -- 按服务器时区

-- Oracle
SELECT SYSTIMESTAMP FROM dual;      -- 服务器时区
SELECT CURRENT_TIMESTAMP FROM dual; -- 会话时区

-- 转换时区
SELECT FROM_TZ(TIMESTAMP '2024-01-14 14:00:00', 'UTC')
       AT TIME ZONE 'Asia/Shanghai' FROM dual;

2. 月末日期

-- MySQL - 2月最后一天
SELECT LAST_DAY('2024-02-01');  -- 2024-02-29

-- Oracle
SELECT LAST_DAY(TO_DATE('2024-02-01', 'YYYY-MM-DD')) FROM dual;

3. 日期解析严格模式

-- MySQL 8.0 严格模式
SET sql_mode = 'STRICT_TRANS_TABLES';

-- 无效日期会报错而非截断
SELECT STR_TO_DATE('2024-02-30', '%Y-%m-%d');  -- NULL（严格模式下可能报错）

📝 练习题

-- 建表
CREATE TABLE events (
    event_id INT,
    event_time STRING,
    event_type STRING
);

INSERT INTO events VALUES
(1, '2024-01-14 08:30:00', 'login'),
(2, '2024-01-14 09:15:30', 'purchase'),
(3, '2024-01-15 10:00:00', 'logout'),
(4, '2024-01-15 14:20:00', 'login');

要求：

  1. 将event_time转换为标准日期格式
  2. 提取年、月、日、小时
  3. 按日期分组统计事件数
  4. 计算相邻事件的时间差
-- 参考答案（MySQL）
-- 1. 格式转换
SELECT
    event_id,
    DATE_FORMAT(event_time, '%Y-%m-%d %H:%i:%s') AS formatted_time
FROM events;

-- 2. 时间提取
SELECT
    event_id,
    YEAR(event_time) AS year,
    MONTH(event_time) AS month,
    DAY(event_time) AS day,
    HOUR(event_time) AS hour
FROM events;

-- 3. 按日期分组
SELECT
    DATE(event_time) AS event_date,
    COUNT(*) AS event_count
FROM events
GROUP BY DATE(event_time);

-- 4. 时间差计算
SELECT
    event_id,
    event_time,
    TIMESTAMPDIFF(
        MINUTE,
        LAG(event_time) OVER (ORDER BY event_time),
        event_time
    ) AS minutes_since_last
FROM events;
赞(0)
未经允许不得转载：順子の杂货铺 » 10-日期格式转换
标签：
Avatar photo

shunzi

一半忧伤，一半明媚！喜欢看书，听歌，写字，打游戏，交朋友！
搬瓦工VPS

相关推荐

评论 抢沙发

Avatar photo

取消

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
吐血推荐

New

热门文章

热门标签

sql (48)mysql (22)oracle (21)python (15)Hive (13)MaxCompute (13)Hologres (13)ClickHouse (12)rm (6)password (5)port (5)install (4)linux (4)log (4)join (3)pos (3)google (3)vi (3)tab (3)redis (3)deepin (3)htaccess (2)master (2)centos (2)kangle (2)easypanel (2)list (2)django (2)plsql (2)docker (2)

网站统计

  • 日志总数：88
  • 评论总数：55
  • 标签总数：125
  • 最后更新：2026-01-18

分享创造快乐

联系我们联系我们