五种主流数据库：常用日期函数

时间：2024-04-16 08:27:14

日期函数用于操作日期和时间数据，例如获取当前日期、计算两个日期之间的间隔以及获取日期的部分信息等。

本文比较五种主流数据库常用日期函数的实现和差异，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

日期函数 函数功能 MySQL Oracle SQL Server PostgreSQL SQLite
CURRENT_DATE 返回系统当前日期 ✔️ ✔️ GETDATE() ✔️ ✔️
CURRENT_TIME 返回系统当前时间 ✔️ ❌ GETDATE() ✔️ ✔️
CURRENT_TIMESTAMP 返回系统当前日期和时间 ✔️ ✔️ ✔️ ✔️ ✔️
EXTRACT(p FROM dt) 提取日期中的部分信息 ✔️ ✔️ DATEPART(p, dt) ✔️ STRFTIME
dt1 - dt2 计算两个日期之间的天数 DATEDIFF(dt2, dt1) ✔️ DATEDIFF(p, dt1, dt2) ✔️ STRFTIME
dt + INTERVAL 日期加上一个时间间隔 ✔️ ✔️ DATEADD(p, n, dt) ✔️ STRFTIME

下面我们通过一些示例来说明这些函数的作用和注意事项。

返回当前日期和时间

CURRENT_DATE、CURRENT_TIME 以及 CURRENT_TIMESTAMP 函数分别返回了数据库系统当前的日期、时间以及时间戳（日期和时间）。例如：

-- MySQL、PostgreSQL 以及 SQLite
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

查询返回的结果取决于我们执行语句的时间。

CURRENT_DATE|CURRENT_TIME|CURRENT_TIMESTAMP 
------------|------------|-------------------
  2021-06-20|    15:32:44|2021-06-20 15:32:44

Oracle 中的日期类型包含了日期和时间信息，Oracle 不支持 CURRENT_TIME 函数。例如：

-- Oracle
SELECT CURRENT_DATE, CURRENT_TIMESTAMP
FROM dual;

查询返回的结果如下：

CURRENT_DATE       |CURRENT_TIMESTAMP 
-------------------|-------------------
2021-06-20 15:40:27|2021-06-21 15:40:27

在 Microsoft SQL Server 中，需要使用 GETDATE 函数返回当前时间戳，然后通过类型转换函数 CAST(expr AS type)将结果转换为日期或者时间类型。例如：

-- Microsoft SQL Server
SELECT CAST(GETDATE() AS DATE), CAST(GETDATE() AS TIME), CURRENT_TIMESTAMP;

下一篇我们将会介绍类型转换函数，查询返回的结果如下：

DATE      |TIME    |CURRENT_TIMESTAMP 
----------|--------|-------------------
2021-06-20|15:47:47|2021-06-20 15:47:47

提取日期中的部分信息

EXTRACT(p FROM dt)函数提取日期时间中的部分信息，例如年、月、日、时、分、秒等。例如：

-- Oracle、MySQL 以及 PostgreSQL
SELECT EXTRACT(YEAR FROM hire_date)
FROM employee
WHERE emp_id = 1;

函数参数中的 YEAR 表示提取年份信息，查询返回的结果如下：

EXTRACT(YEAR FROM hire_date)
----------------------------
 2000

除提取年份信息外，我们也可以使用 MONTH、DAY、HOUR、MINUTE、SECOND 等参数提取日期中的其他信息。

Microsoft SQL Server 使用 DATEPART(p, dt)函数提取日期中的信息。例如：

-- Microsoft SQL Server
SELECT DATEPART(YEAR, hire_date)
FROM employee
WHERE emp_id = 1;

函数参数中的 YEAR 表示提取年份信息，同样也可以使用 MONTH、DAY、HOUR、MINUTE、SECOND 等参数提取日期中的其他信息。查询返回的结果与上面的示例相同。

SQLite 提供了日期格式化函数 STRFTIME，可以提取日期中的信息。例如：

-- SQLite
SELECT STRFTIME('%Y', hire_date)
FROM employee
WHERE emp_id = 1;

函数中的第一个参数%Y 代表 4 位数的年份，我们也可以使用%m、%d、%H、%M、%S 等参数提取日期中的其他信息。查询返回的结果与上面的示例相同。

日期的加减运算

日期的加减运算主要包括两个日期相减以及一个日期加/减一个时间间隔。例如：

-- Oracle 和 PostgreSQL
SELECT DATE '2021-03-01' - DATE '2021-02-01',
       DATE '2021-02-01' + INTERVAL '-1' MONTH
FROM employee
WHERE emp_id = 1;

在 Oracle 和 PostgreSQL 中，两个日期相减就可以得到它们之间相差的天数，日期加上一个时间间隔（INTERVAL）就可以得到一个新的日期。查询返回的结果如下：

DATE'2021-03-01'-DATE'2021-02-01'|DATE'2021-02-01'+INTERVAL'-1'MONTH
---------------------------------|----------------------------------
                               28| 2021-01-01 00:00:00

2021 年 2 月有 28 天，2021 年 2 月 1 日减去一个月是 2021 年 1 月 1 日。

MySQL 使用 DATEDIFF(dt2, dt1) 函数计算日期 dt2 减去日期 dt1 得到的天数，例如：

-- MySQL
SELECT DATEDIFF(DATE '2021-03-01', DATE '2021-02-01'),
       DATE '2021-02-01' + INTERVAL '-1' MONTH;

查询返回的结果和上面的示例相同。

Microsoft SQL Server 使用 DATEDIFF(p, dt1, dt2)函数计算日期 dt2 减去日期 dt1 得到的时间间隔，使用 DATEADD(p, n, dt)函数为日期增加一个时间间隔。例如：

-- Microsoft SQL Server
SELECT DATEDIFF(DAY, '2021-02-01', '2021-03-01'),
       DATEADD(MONTH, -1, '2021-02-01');

DATEDIFF 函数中的第一个参数（DAY）表示计算第二个日期减去第一个日期的天数，也可以返回月数（MONTH）或者年数（YEAR）等。DATEADD 函数在 2021 年 2 月 1 日的基础上增加了-1 个月，也就是减去 1 个月。查询返回的结果和上面的示例相同。

SQLite 可以利用 STRFTIME 函数实现两个日期的相减，或者为日期增加一个时间间隔。例如：

-- SQLite
SELECT STRFTIME('%J', '2021-03-01') - STRFTIME('%J', '2021-02-01'),
       STRFTIME('%Y-%m-%d', '2021-02-01', '-1 months');

前两个 STRFTIME 函数中的参数%J 表示将日期转换为儒略日（Julian Day）。第 3 个STRFTIME 函数格式化日期的同时增加了一个时间间隔。查询返回的结果和上面的示例相同。

相关文章