SQL时间函数大全 日期处理与格式化输出完整指南

如何在不同数据库中获取当前日期和时间？1.在mysql中使用now()、current_timestamp()获取当前日期和时间，curdate()获取日期，curtime()获取时间；2.在postgresql中使用now()或current_timestamp获取日期和时间，current_date获取日期，current_time获取时间；3.在sql server中使用getdate()或sysdatetime()获取当前时间，精度不同；4.在oracle中使用sysdate获取日期和时间，systimestamp提供更高精度。

SQL中的时间函数，是数据处理领域里一个绕不开的话题，它们是处理、操作和格式化日期与时间数据的核心工具。在我看来，这些函数远不止是简单的语法糖，它们是让时间数据真正“活起来”的关键，无论是计算两个事件间隔、分析趋势，还是仅仅为了让报表上的日期看起来更清晰，它们都扮演着至关重要的角色。掌握它们，就像是拥有了一把时间魔术的钥匙。

解决方案

处理SQL中的日期和时间，核心在于理解不同数据库系统（如MySQL、PostgreSQL、SQL Server、Oracle等）虽然在函数命名和具体参数上有所差异，但其背后的逻辑和需求是高度一致的：获取当前时间、提取时间组成部分、进行时间加减、格式化输出以及比较时间。

以获取当前时间为例，这是最基础也最常用的操作。在MySQL里，我们通常用 NOW() 或 CURRENT_TIMESTAMP() 来获取当前日期和时间，它们会包含毫秒精度。如果只需要日期，CURDATE() 就足够了；如果只要时间，CURTIME()。PostgreSQL则更倾向于 NOW() 或 CURRENT_TIMESTAMP，它们同样提供了高精度的时间戳。SQL Server则有 GETDATE() 和 SYSDATETIME()，后者提供了更高的精度。Oracle数据库则使用 SYSDATE。这些函数各有侧重，但目的都是一致的：将系统当前的精确时刻带入你的查询或操作中。

更进一步，我们经常需要从一个完整的日期时间中“抠出”某个部分，比如年份、月份或小时。MySQL提供了 YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND() 等一系列直观的函数。PostgreSQL则有 EXTRACT(part FROM source) 这样的通用函数，你可以指定 part 为 YEAR, MONTH, DAY, HOUR 等。SQL Server则提供了 DATEPART(part, date)。这些提取功能对于按时间维度进行数据聚合或筛选非常有用，比如统计每个月的新增用户数，或者找出每天某个时间段的订单高峰。

时间加减是另一个高频需求。比如，我想知道某个事件发生30天后是什么日期，或者计算某个任务在5小时前开始的时间。MySQL的 DATE_ADD(date, INTERVAL expr unit) 和 DATE_SUB(date, INTERVAL expr unit) 用起来非常灵活，unit 可以是 DAY, MONTH, YEAR, HOUR, MINUTE, SECOND 等。PostgreSQL则支持更简洁的语法，直接 date + INTERVAL 'X unit' 或 date - INTERVAL 'Y unit'。SQL Server则用 DATEADD(unit, number, date)。这些操作是预测、规划和历史数据分析的基础。

最后，也是我个人觉得非常关键的一点，就是日期时间的格式化输出。数据库里存的日期时间，往往是标准格式，但展示给用户时，可能需要变成“2023年10月26日 星期四 下午3点半”这样的友好形式。MySQL的 DATE_FORMAT(date, format) 提供了丰富的格式化选项，例如 %Y-%m-%d %H:%i:%s。PostgreSQL和Oracle则有强大的 TO_CHAR(date, format) 函数，格式化字符串的规则与MySQL有所不同，但功能同样强大。SQL Server则有 FORMAT(value, format) 或 CONVERT(data_type, expression, style)。反过来，将一个字符串解析成日期时间，也同样重要，比如从CSV导入数据时。MySQL有 STR_TO_DATE(string, format)，PostgreSQL和Oracle有 TO_DATE(string, format)，SQL Server则依赖 CONVERT 或 PARSE。这部分操作，是连接后端数据和前端展示的桥梁，也是数据清洗和导入时绕不开的步骤。

如何在不同数据库中获取当前日期和时间？

在实际开发中，我们最常遇到的一个问题就是，如何跨越不同的数据库系统，准确无误地获取到当前的日期和时间。这看似简单，但每个数据库都有自己的一套“方言”。我曾因此踩过一些小坑，比如在MySQL里用惯了 NOW()，结果在SQL Server里直接写就报错了。

对于 MySQL 用户来说，获取当前日期和时间是相当直接的。

• NOW()：返回当前日期和时间，通常精确到秒或毫秒，取决于系统配置。

  SELECT NOW();
  -- 示例输出: 2023-10-26 15:30:00

  登录后复制
• CURDATE()：只返回当前日期。

  SELECT CURDATE();
  -- 示例输出: 2023-10-26

  登录后复制
• CURTIME()：只返回当前时间。

  SELECT CURTIME();
  -- 示例输出: 15:30:00

  登录后复制
• CURRENT_TIMESTAMP()：功能与 NOW() 类似。

而对于 PostgreSQL，它的时间函数设计哲学略有不同，但同样清晰。

• NOW() 或 CURRENT_TIMESTAMP：两者都可以获取当前日期和时间，通常带有时区信息，并精确到微秒。

  SELECT NOW();
  -- 示例输出: 2023-10-26 15:30:00.123456+08

  登录后复制
• CURRENT_DATE：只返回当前日期。

  SELECT CURRENT_DATE;
  -- 示例输出: 2023-10-26

  登录后复制
• CURRENT_TIME：只返回当前时间，通常带有时区信息。

  SELECT CURRENT_TIME;
  -- 示例输出: 15:30:00.123456+08

  登录后复制

当你在 SQL Server 环境下工作时，你会遇到 GETDATE() 和 SYSDATETIME()。

• GETDATE()：返回当前日期和时间，精度通常到毫秒。

  SELECT GETDATE();
  -- 示例输出: 2023-10-26 15:30:00.123

  登录后复制
• SYSDATETIME()：提供了更高的精度，可以达到纳秒级别，这在需要极高时间精度的场景下非常有用。

  SELECT SYSDATETIME();
  -- 示例输出: 2023-10-26 15:30:00.1234567

  登录后复制

最后，Oracle 数据库则有 SYSDATE。

• SYSDATE：返回当前日期和时间，通常精确到秒。

  SELECT SYSDATE FROM DUAL; -- Oracle查询通常需要FROM DUAL
  -- 示例输出: 26-OCT-23 15.30.00.000000000 PM

  登录后复制
• SYSTIMESTAMP：如果需要更高精度或时区信息，可以使用这个函数。

理解这些差异至关重要，它能帮助你避免跨数据库迁移时的不必要麻烦，也能在日常开发中更高效地选择合适的函数。

SQL日期函数如何进行日期加减和间隔计算？

日期和时间的加减运算，是SQL时间函数中最常用的功能之一。想象一下，你需要计算一个订单的预计送达时间（下单时间加三天），或者找出某个活动开始前一周的日期。这些都离不开日期加减。不同数据库的实现方式各有特色，但核心思想都是在某个日期或时间点上增加或减少一个时间间隔。

在 MySQL 中，DATE_ADD() 和 DATE_SUB() 是主力军。

• DATE_ADD(date, INTERVAL expr unit)：向日期添加一个时间间隔。

  -- 某个日期加7天
  SELECT DATE_ADD('2023-10-26', INTERVAL 7 DAY);
  -- 示例输出: 2023-11-02
  
  -- 当前时间加3小时30分钟
  SELECT DATE_ADD(NOW(), INTERVAL '3:30' HOUR_MINUTE);
  -- 示例输出: 2023-10-26 18:00:00 (假设当前是15:30)

  登录后复制

• DATE_SUB(date, INTERVAL expr unit)：从日期减去一个时间间隔。

  -- 某个日期减1个月
  SELECT DATE_SUB('2023-10-26', INTERVAL 1 MONTH);
  -- 示例输出: 2023-09-26

  登录后复制

  这里的 unit 可以是 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 等多种组合，非常灵活。

PostgreSQL 的日期加减语法则显得更加直观和“自然语言化”。

• 直接使用 + 或 - 运算符配合 INTERVAL 关键字。

  -- 某个日期加5天
  SELECT '2023-10-26'::date + INTERVAL '5 days';
  -- 示例输出: 2023-10-31
  
  -- 当前时间减2小时
  SELECT NOW() - INTERVAL '2 hours';
  -- 示例输出: 2023-10-26 13:30:00 (假设当前是15:30)
  
  -- 复杂的间隔，比如1年2个月3天
  SELECT '2023-01-01'::date + INTERVAL '1 year 2 months 3 days';
  -- 示例输出: 2024-03-04

  登录后复制

  PostgreSQL的 INTERVAL 语法非常强大，可以组合多种时间单位，非常符合我们日常表达时间间隔的方式。

在 SQL Server 中，DATEADD() 函数是进行日期加减的核心。

文心大模型

百度飞桨-文心大模型 ERNIE 3.0 文本理解与创作

56

查看详情

• DATEADD(datepart, number, date)：向指定日期部分添加或减去一个数值。

  -- 某个日期加10天
  SELECT DATEADD(day, 10, '2023-10-26');
  -- 示例输出: 2023-11-05
  
  -- 当前时间减30分钟
  SELECT DATEADD(minute, -30, GETDATE());
  -- 示例输出: 2023-10-26 15:00:00 (假设当前是15:30)

  登录后复制

  datepart 参数可以是 year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond 等。

Oracle 则主要依赖 INTERVAL 关键字，或者直接对 DATE 类型进行加减（加减的单位是天）。

• date + number / date - number：直接对日期进行加减，number 单位为天。

  -- 某个日期加5天
  SELECT TO_DATE('2023-10-26', 'YYYY-MM-DD') + 5 FROM DUAL;
  -- 示例输出: 2023-10-31
  
  -- 某个日期减0.5天（即12小时）
  SELECT TO_DATE('2023-10-26 15:00:00', 'YYYY-MM-DD HH24:MI:SS') - 0.5 FROM DUAL;
  -- 示例输出: 2023-10-26 03:00:00

  登录后复制

• 使用 INTERVAL 关键字：

  -- 某个日期加2个月
  SELECT SYSDATE + INTERVAL '2' MONTH FROM DUAL;
  -- 示例输出: 26-DEC-23
  
  -- 某个日期加1小时30分钟
  SELECT SYSDATE + INTERVAL '1:30' HOUR TO MINUTE FROM DUAL;
  -- 示例输出: 26-OCT-23 17.00.00.000000000 PM (假设当前是15:30)

  登录后复制

在实际操作中，日期加减还需要注意闰年、月份天数不同（比如2月和3月）等情况，这些函数通常会智能地处理这些“边缘”问题，但偶尔也需要我们手动检查结果是否符合预期。

如何将日期格式化为自定义字符串或从字符串转换日期？

日期格式化和解析，在我看来，是数据展示和数据导入导出时最容易出错，但也最能体现你对数据“掌控力”的地方。我们从数据库里拿到的日期，通常是标准的内部格式，但用户可能需要“2023年10月26日 下午3点半”这样的中文格式，或者“10/26/23”这样的美式简写。反过来，从外部文件读取的日期字符串，也需要准确无误地转换成数据库能理解的日期类型。

在 MySQL 中，DATE_FORMAT() 是格式化输出的利器，而 STR_TO_DATE() 则负责解析。

• DATE_FORMAT(date, format)：将日期格式化为字符串。

  -- 格式化为 'YYYY-MM-DD HH:MM:SS'
  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
  -- 示例输出: 2023-10-26 15:30:00
  
  -- 格式化为中文日期
  SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点%i分%s秒');
  -- 示例输出: 2023年10月26日 15点30分00秒

  登录后复制

  这里 %Y 代表四位年份，%m 两位月份，%d 两位日期，%H 24小时制小时，%i 分钟，%s 秒。

• STR_TO_DATE(string, format)：将字符串解析为日期。

  -- 将 '2023-10-26 15:30:00' 字符串转换为日期时间
  SELECT STR_TO_DATE('2023-10-26 15:30:00', '%Y-%m-%d %H:%i:%s');
  -- 示例输出: 2023-10-26 15:30:00 (日期时间类型)
  
  -- 将 '2023年10月26日' 转换为日期
  SELECT STR_TO_DATE('2023年10月26日', '%Y年%m月%d日');
  -- 示例输出: 2023-10-26 (日期类型)

  登录后复制

  重要的是，format 字符串必须与输入字符串的格式完全匹配。

PostgreSQL 和 Oracle 在这方面有着相似的 TO_CHAR() 和 TO_DATE() 函数。

• PostgreSQL:

  • TO_CHAR(date, format)：格式化日期为字符串。

    -- 格式化为 'YYYY-MM-DD HH24:MI:SS'
    SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
    -- 示例输出: 2023-10-26 15:30:00
    
    -- 格式化为中文日期（注意格式符与MySQL不同）
    SELECT TO_CHAR(NOW(), 'YYYY"年"MM"月"DD"日" HH24"点"MI"分"SS"秒"');
    -- 示例输出: 2023年10月26日 15点30分00秒

    登录后复制

    PostgreSQL的格式符通常是全大写字母，例如 YYYY, MM, DD, HH24 (24小时制), MI, SS。

  • TO_DATE(string, format) / TO_TIMESTAMP(string, format)：将字符串解析为日期或时间戳。

    -- 将 '2023-10-26 15:30:00' 字符串转换为时间戳
    SELECT TO_TIMESTAMP('2023-10-26 15:30:00', 'YYYY-MM-DD HH24:MI:SS');
    -- 示例输出: 2023-10-26 15:30:00 (时间戳类型)

    登录后复制

• Oracle:

  • TO_CHAR(date, format)：与PostgreSQL类似。

    SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
    -- 示例输出: 2023-10-26 15:30:00

    登录后复制
  • TO_DATE(string, format)：与PostgreSQL类似。

    SELECT TO_DATE('2023-10-26 15:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
    -- 示例输出: 26-OCT-23 15.30.00.000000000 PM

    登录后复制

SQL Server 则提供了 FORMAT() 函数（SQL Server 2012+）和更传统的 CONVERT() 函数。

• FORMAT(value, format)：格式化日期为字符串。

  -- 格式化为 'YYYY-MM-DD HH:MM:SS'
  SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss');
  -- 示例输出: 2023-10-26 15:30:00

  登录后复制

  SQL Server的格式字符串与.NET的格式字符串类似。

• CONVERT(data_type, expression, style)：通用类型转换，也可用于日期格式化和解析。

  -- 将日期格式化为

  登录后复制

以上就是SQL时间函数大全 日期处理与格式化输出完整指南的详细内容，更多请关注php中文网其它相关文章！