十、日期和时间的存储与格式转换

上一篇我们介绍了 SQL 中常见的字符函数，学习了如何对文本数据进行连接、大小写转换、子串的查找和替换等处理。
本篇我们继续讨论常见的日期和时间函数，以及不同数据类型之间的转换函数。

日期和时间的存储

在数据库中，日期时间类型存在 3 种形式：

• DATE，日期类型，包含年、月、日。可以用于存储出生日期、入职日期等。
• TIME，时间类型，包含时、分、秒，以及小数秒。一般使用较少。
• TIMESTAMP，时间戳类型，包含年、月、日、时、分、秒，以及小数秒。用于对时间精度要求比较高的场景，比如存储订单时间。

TIMESTAMP 和 TIME 还可以添加 WITH TIME ZONE 选项，用于指定一个时区偏移量。例如，UTC 标准时间的 0 点等于北京时间的早上 8 点。时区选项通常用于支持全球化的应用系统中。
以下是 4 种数据库对于日期时间类型的支持情况。

其中，Oracle 的 DATE 类型包含了日期和时间两部分，但不支持 TIME 类型。MySQL 还提供了 DATETIME 日期时间类型。
我们将在第 25 篇讨论如何为表中的字段选择合适的数据类型。现在让我们先来看看有哪些用于日期时间处理的函数。

日期时间函数

日期时间函数用于操作日期和时间数据，例如获取当前日期、为指定日期增加天数，计算两个日期之间的差或者获取日期的部分信息。下表列出了 SQL 中常见的日期时间函数：

接下来我们详细讨论一下这些函数和运算符的作用。

返回当前日期时间

CURRENT_DATE、CURRENT_TIME 以及 CURRENT_TIMESTAMP 函数分别返回数据库系统当前的日期、时间以及时间戳。以下是不同数据库中的实现示例：

-- MySQL 和 PostgreSQL 实现
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP   FROM employee  WHERE emp_id = 1;
CURRENT_DATE|CURRENT_TIME|CURRENT_TIMESTAMP  |
------------|------------|-------------------|
  2019-10-23|    11:29:55|2019-10-23 11:29:55|
  -- Oracle 实现 
  SELECT CURRENT_DATE, CURRENT_TIMESTAMP   FROM employee  WHERE emp_id = 1; 
  CURRENT_DATE       |CURRENT_TIMESTAMP  |
  -------------------|-------------------| 
  2019-10-23 11:31:58|2019-10-23 11:31:58| 
  -- SQL Server 实现 
  SELECT CAST(GETDATE() AS DATE), CAST(GETDATE() AS TIME), CURRENT_TIMESTAMP   FROM employee  WHERE emp_id = 1; 
  |        |                   |
  ----------|--------|-------------------|
  2019-10-23|11:35:47|2019-10-23 11:35:47|

Oracle 中没有 TIME 类型和相应的函数，CURRENT_DATE 函数结果中包含了时间信息；SQL Server 使用 GETDATE() 函数返回当前日期时间，再进行类型转换为日期或者时间。CAST 函数用于转换数据的类型，参见下文。
除此之外，各种数据库还提供了一些扩展的函数：

• Oracle 支持 SYSDATE、SYSTIMESTAMP 获取当前日期和时间戳；
• MySQL 支持 CURDATE()、CURRENT_DATE() 获取当前日期，CURTIME()、CURRENT_TIME() 获取当前时间，NOW()、CURRENT_TIMESTAMP() 获取当前时间戳；
• SQL Server 支持 SYSDATETIME()、SYSDATETIMEOFFSET() 获取系统当前时间戳；
• PostgreSQL 支持 NOW() 获取系统当前时间戳。

对日期时间的另一种常见的操作就是提取某一部分的信息，例如按年统计销量时需要提取销售日期中的年份信息。

提取日期时间信息

EXTRACT(part FROM dt) 函数可以返回日期时间中的某个部分，例如年、月、小时等。以下示例查找 2018 年入职的员工：

-- Oracle、MySQL 以及 PostgreSQL 实现 
SELECT emp_name, hire_date   FROM employee  WHERE EXTRACT(YEAR FROM hire_date) = 2018;

EXTRACT 函数中的 YEAR 表示提取年份信息。该查询的结果如下：

SQL Server 使用 DATEPART(part, dt) 函数实现相同的功能：

-- SQL Server 实现 
SELECT emp_name, hire_date   FROM employee  WHERE DATEPART(YEAR, hire_date) = 2018;

DATEPART 函数中的 YEAR 表示提取年份信息。该查询的结果与上面的示例相同。
EXTRACT 和 DATEPART 函数可以支持更多的选项，例如 MONTH、DAY、WEEK、HOUR、MINUTE、SECOND 等。每个数据库支持的选项可能不同，使用时可以参考数据库文档。

日期和时间的数学运算

日期和时间的运算主要包括两个日期相减以及一个日期加/减一个时间间隔。以下示例计算员工从入职到当前日期之间的天数：

-- Oracle 以及 PostgreSQL 实现 
SELECT emp_name,hire_date, CURRENT_DATE, CURRENT_DATE - hire_date AS days   FROM employee;

Oracle 以及 PostgreSQL 中两个日期相减就可以得到它们之间相差的天数。该查询的结果如下（显示部分内容）：

CURRENT_DATE 获取的是当前日期，所以你的结果和上面的结果应该不同。
MySQL 和 SQL Server 使用 DATEDIFF 函数计算两个日期之间的间隔：

-- MySQL 实现 
SELECT emp_name,hire_date, CURRENT_DATE, DATEDIFF(CURRENT_DATE, hire_date) AS days   FROM employee; 
-- SQL Server 实现 
SELECT emp_name,hire_date, GETDATE(), DATEDIFF(DAY, hire_date, GETDATE()) AS days   FROM employee;

MySQL 中的 DATEDIFF 函数返回第一个日期减去第二个日期的天数。SQL Server 中的 DATEDIFF 函数接受三个参数，可以返回第二个日期减去第一个日期的天数（DAY）、月数（MONTH）或者年数（YEAR）等。
另外，日期时间加上/减去一个时间间隔，可以得到一个新的日期时间。以下示例用于计算员工入职一周年的纪念日：

-- Oracle、MySQL 以及 PostgreSQL 实现 
SELECT hire_date, hire_date + INTERVAL '1' YEAR AS anniversary   FROM employee; 
-- SQL Server 实现 
SELECT hire_date, DATEADD(YEAR, 1, hire_date) AS anniversary   FROM employee;

INTERVAL 表示一段时间，例如 5 分钟（MINUTE）、1 个月（MONTH）等。SQL Server 使用 DATEADD 函数为日期增加一段时间。以上查询的结果如下（显示部分内容）：

当不同类型的数据在一起进行处理时，就会涉及到类型之间的转换。我们可以使用函数执行明确的类型转换，数据库也可能执行隐式的类型转换。

类型转换函数

CAST(expr AS type) 函数用于将数据转换为不同的类型。以下是一个类型转换的示例：

-- Oracle 实现 -- 修改日期显示格式 
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd'; 
SELECT CAST('666' AS INTEGER), CAST(hire_date AS CHAR(10))   FROM employee  WHERE emp_id = 1; 
-- SQL Server 和 PostgreSQL 实现 
SELECT CAST('666' AS INTEGER), CAST(hire_date AS CHAR(10))   FROM employee  WHERE emp_id = 1; 
-- MySQL 实现 
SELECT CAST('666' AS SIGNED INTEGER), CAST(hire_date AS CHAR(10))   FROM employee  WHERE emp_id = 1;

Oracle 中日期的显示格式由参数 nls_date_format 进行控制，ALTER SESSION 语句将日期的显示格式修改成和其他数据库一致的格式；MySQL 中的整型分为有符号的 SIGNED INTEGER 和无符号的 UNSIGNED INTEGER。以上查询的结果如下：

CAST('666'ASINTEGER)|CAST(HIRE_DATEASCHAR(10))
| --------------------|-------------------------|
666|2000-01-01               |

类型转换可能导致精度的丢失，并且 CAST 函数在各种数据库中支持的转换类型取决于数据库的实现。
除了明确指定的类型转换之外，数据库可能在执行某些操作时尝试隐式的类型转换。例如以下语句：

SELECT '666' + 123, CONCAT('Hire Date: ', hire_date)   FROM employee  WHERE emp_id = 1; 
'666' + 123|CONCAT('Date: ', hire_date)|
-----------|---------------------------|
        789|Hire Date: 2000-01-01      |

该查询中存在 2 个隐式类型转换。第一个转换将字符串“666”转换为数字 666，第二个转换将日期类型的 hire_date 转换为字符串。

小结

本篇我们介绍了日期和时间数据类型以及相关的函数，同时了解了数据类型之间的显式转换和隐式转换。到此为止，我们已经学习了 SQL 中各种常见的标量函数。
练习题：如何知道今天或者某一天是星期几？可以查找数据库文档或者在网上搜索相关的函数。