《数据库基础语法》6. 日期和时间的存储与格式转换

楔子

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

日期和时间的存储

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

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

TIMESTAMP 和 TIME 还可以添加 WITH TIME ZONE 选项，用于指定一个时区偏移量。例如，UTC 标准时间的 0 点等于北京时间的早上 8 点。时区选项通常用于支持全球化的应用系统中。

以下是 4 种数据库对于日期时间类型的支持情况。

其中，Oracle 的 DATE 类型包含了日期和时间两部分，但不支持 TIME 类型。MySQL 还提供了 DATETIME 日期时间类型。

日期时间函数

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

字符函数用于字符数据的处理，例如字符串的拼接、大小写转换、子串的查找和替换等。下表列出了 SQL 中常见的字符函数：

我们演示一下这几个函数的用法：

-- 当前日期
SELECT CURRENT_DATE;

-- 当前时间
SELECT CURRENT_TIME;

-- 当前日期加上时间，除了current_timestamp，还可以通过now()来获取
SELECT CURRENT_TIMESTAMP;

-- 截取当然日期的指定部分
SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP), -- 获取年
       EXTRACT(MONTH FROM CURRENT_TIMESTAMP), -- 获取月
       EXTRACT(DAY FROM CURRENT_TIMESTAMP);  -- 获取天

SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP),  -- 获取小时
       EXTRACT(MINUTE FROM CURRENT_TIMESTAMP),  -- 获取分钟
       EXTRACT(SECOND FROM CURRENT_TIMESTAMP);  -- 获取秒

类型转换函数

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

SELECT CAST('123' AS INT) + 234; -- 357
SELECT '123'::INT + 234; -- 357
-- 对于PostgreSQL来说，以上是等价的

所以我们可以很好的处理日期，举个栗子：

-- 将字符串转成日期, 当然还可以转成 INTERVAL
SELECT '2018-1-1'::DATE + '7 day'::INTERVAL;  -- 2018-01-08 00:00:00.000000

-- 再比如我有日期类型的数据 A 和 B, 我要在 B 的月份加在 A 上面
SELECT '2018-1-1'::DATE + (EXTRACT(MONTH FROM '2018-2-2'::DATE) || 'month')::INTERVAL; -- 2018-03-01 00:00:00.000000
-- 解释一下，首先 "n YEAR"::INTERVAL 就表示时间间隔n年, 同理 "n MONTH"::INTERVAL 表示时间间隔n月
-- 当然还有 DAY、HOUR 什么的
-- EXTRACT(MONTH FROM '2018-2-2'::DATE)表示解析出来月份, 然后和 MONTH 进行拼接, 最后转成 INTERVAL 类型, 再相加

-- 另外，我这个是PostgreSQL语法，支持通过::来进行类型转化，但是其它数据库则不一定支持, 因此注意自己所使用的数据库

小结

这一节我们介绍了日期和时间数据类型以及相关的函数，同时了解了数据类型之间的显式转换和隐式转换。到此为止，我们已经学习了 SQL 中各种常见的标量函数。