SQL 日期/时间处理函数

在数据统计分析中，经常会遇到需要对时间进行格式转化或其他层面的内容。由于每种数据库自带的相关函数存在一定的差异，所以经常会记不得如何使用。今天做下简单的梳理。

在开始学习日期/时间函数先，先来了解下数据库中常见的日期/时间存储格式：

• Unix时间戳（10位，单位为秒，1970年1月1日到现在的秒数）：1605191559
• Unix时间戳（13位，单位为微秒）：1605191559123
• DATE类型的日期：2020-11-12
• 字符串类型日期：2020-11-12、2020/11/12、20201112
• 数值型日期：20201112
• DATETIME类型时间：2020-11-12 22:39:08

Hive SQL

获取当前时间

• 获取当前时间：UNIX_TIMESTAMP()，默认返回的是当前时间的时间戳，单位为秒：1605194959
• 获取当前时间：CURRENT_TIMESTAMP()，默认返回DATETIME类型时间：2020-11-12 23:18:51
• 获取当前日期：CURRENT_DATE，返回格式：2020-11-12 00:00:00
• 获取当前日期：CURRENT_DATE()，返回格式：2020-11-12 00:00:00

时区变换

• 把UTC标准时间切换到时区时间：from_utc_timestamp(current_timestamp(),8)。
• 把时区时间切换到UTC标准时间：to_utc_timestamp(current_timestamp(),8);

其他参考：https://en.wikipedia.org/wiki/Lists_of_time_zones

时间格式转换

• 将Unix时间戳转化为DATETIME类型时间：from_unixtime(unix_timestamp(), ‘yyyy-MM-dd HH:mm:ss’)，注意这里接受的是以秒为单位的unix时间戳，如果是微秒，则需要：CAST(microsecond/1000 AS INT)，将值设置为INT类型。
• 将DATETIME类型时间转化为Unix时间戳：unix_timestamp(‘2020-11-12 22:39:08’, ‘yyyy-MM-dd HH:mm:ss’)
• 将DATETIME类型时间转为DATE类型的日期：TO_DATE(‘2020-11-12 22:39:08’)

时间提取

• 获取日期中的年份：YEAR(‘2020-11-12 22:39:08’)
• 获取日期中的月份：MONTH(‘2020-11-12 22:39:08’)
• 获取月份中的天数：DAY(‘2020-11-12 22:39:08’)，DAYOFMONTH(‘2020-11-12 22:39:08’)
• 获取小时数：HOUR(‘2020-11-12 22:39:08’)
• 获取分钟数：MINUTE(‘2020-11-12 22:39:08’)
• 获取秒数：SECOND(‘2020-11-12 22:39:08’)
• 获取日期属于第几周：WEEKOFYEAR(‘2020-11-12 22:39:08’)

日期计算

• 计算两个日期天数差：DATEDIFF(‘2020-11-12’, ‘2020-01-01’)，DATEDIFF与CURRENT_DATE连用的Bug
• 日期+天数：DATE_ADD(‘2020-11-12’, 5)，这里可以传入负数，效果同DATE_SUB
• 日期-天数：DATE_SUB(‘2020-11-12’, 5)
• 日期+月数：ADD_MONTHS(‘2020-11-12’,2)

根据格式整理日期

DATE_FORMAT(date,format)

下表列出了您可以在模式中用来格式化或解析与日期时间相关的字符串的字符。该表下面是一些注意事项，它们对表中的某些示例做了进一步说明。

MySQL

-- MySQL日期时间处理函数
-- 当前日期：2020-11-13 14:44:13
SELECT NOW() FROM DUAL;-- 当前日期时间：2020-11-13 14:44:13
-- 在MySQL里也存在和Oracle里类似的dual虚拟表：官方声明纯粹是为了满足SELECT ... FROM...这一习惯问题，MySQL会忽略对该表的引用。
SELECT NOW();-- 当前日期时间：2020-11-13 14:44:13
-- 除了 now() 函数能获得当前的日期时间外，MySQL 中还有下面的函数：
SELECT CURRENT_TIMESTAMP();-- 2020-11-13 14:46:04
SELECT CURRENT_TIMESTAMP;-- 2020-11-13 14:46:22
SELECT LOCALTIME();-- 2020-11-13 14:46:34
SELECT LOCALTIME;-- 2020-11-13 14:46:49
SELECT LOCALTIMESTAMP();-- 2020-11-13 14:47:03
SELECT LOCALTIMESTAMP;-- 2020-11-13 14:47:17
-- 这些日期时间函数，都等同于 now()。鉴于 now() 函数简短易记，建议总是使用 now()来替代上面列出的函数。
SELECT SYSDATE();-- 当前日期时间：2020-11-13 14:47:42
-- sysdate() 日期时间函数跟 now() 类似，
-- 不同之处在于：now() 在执行开始时值就得到了;sysdate() 在函数执行时动态得到值。
-- 看下面的例子就明白了：
SELECT NOW(), SLEEP(3), NOW(); -- 2020-11-13 14:48:14	0	2020-11-13 14:48:14
SELECT SYSDATE(), SLEEP(3), SYSDATE(); -- 2020-11-13 14:48:38	0	2020-11-13 14:48:41
SELECT CURDATE();-- 当前日期：2020-11-13
SELECT CURRENT_DATE();-- 当前日期：等同于 CURDATE()
SELECT CURRENT_DATE;-- 当前日期：等同于 CURDATE()
SELECT CURTIME();-- 当前时间：14:49:26
SELECT CURRENT_TIME();-- 当前时间：等同于 CURTIME()
SELECT CURRENT_TIME;-- 当前时间：等同于 CURTIME()
-- 获得当前 UTC 日期时间函数
SELECT UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME() -- 2020-11-13 06:49:41	2020-11-13	06:49:41
-- MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()
SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP();-- 2020-11-13 14:50:05	2020-11-13 14:50:05
-- MySQL 日期时间 Extract（选取） 函数
SET @dt = '2020-11-13 14:50:05.123456';
SELECT DATE(@dt);-- 获取日期：2020-11-13
SELECT TIME(@dt);-- 获取时间：14:50:05.123456
SELECT YEAR(@dt);-- 获取年份
SELECT MONTH(@dt);-- 获取月份
SELECT DAY(@dt);-- 获取日
SELECT HOUR(@dt);-- 获取时
SELECT MINUTE(@dt);-- 获取分
SELECT SECOND(@dt);-- 获取秒
SELECT MICROSECOND(@dt);-- 获取毫秒
SELECT QUARTER(@dt);-- 获取季度
SELECT WEEK(@dt);-- 45 (获取周)
SELECT WEEKOFYEAR(@dt);-- 同week()
SELECT DAYOFYEAR(@dt);-- 318 (日期在年度中第几天)
SELECT DAYOFMONTH(@dt);-- 5 (日期在月度中第几天)
SELECT DAYOFWEEK(@dt);-- 6 (日期在周中第几天；周日为第一天)
SELECT WEEKDAY(@dt);-- 4(与dayofweek()都表示日期在周的第几天，只是参考标准不同，weekday()周一为第0天，周日为第6天)
SELECT YEARWEEK(@dt);-- 202045(年和周)
SELECT EXTRACT(YEAR FROM @dt);
SELECT EXTRACT(MONTH FROM @dt);
SELECT EXTRACT(DAY FROM @dt);
SELECT EXTRACT(HOUR FROM @dt);
SELECT EXTRACT(MINUTE FROM @dt);
SELECT EXTRACT(SECOND FROM @dt);
SELECT EXTRACT(MICROSECOND FROM @dt);
SELECT EXTRACT(QUARTER FROM @dt);
SELECT EXTRACT(WEEK FROM @dt);
SELECT EXTRACT(YEAR_MONTH FROM @dt);
SELECT EXTRACT(DAY_HOUR FROM @dt);
SELECT EXTRACT(DAY_MINUTE FROM @dt);-- 131450(日时分)
SELECT EXTRACT(DAY_SECOND FROM @dt);-- 13145005(日时分秒)
SELECT EXTRACT(DAY_MICROSECOND FROM @dt);-- 13145005123456(日时分秒毫秒)
SELECT EXTRACT(HOUR_MINUTE FROM @dt);-- 1450(时分)
SELECT EXTRACT(HOUR_SECOND FROM @dt);-- 145005(时分秒)
SELECT EXTRACT(HOUR_MICROSECOND FROM @dt);-- 145005123456(日时分秒毫秒)
SELECT EXTRACT(MINUTE_SECOND FROM @dt);-- 5005(分秒)
SELECT EXTRACT(MINUTE_MICROSECOND FROM @dt);-- 5005123456(分秒毫秒)
SELECT EXTRACT(SECOND_MICROSECOND FROM @dt);-- 5123456(秒毫秒)
-- MySQL Extract() 函数除了没有date(),time() 的功能外，其他功能一应具全。
-- 并且还具有选取‘day_microsecond' 等功能。
-- 注意这里不是只选取 day 和 microsecond，而是从日期的 day 部分一直选取到 microsecond 部分。
SELECT DAYNAME(@dt);-- Friday(返回英文星期)
SELECT MONTHNAME(@dt);-- November(返回英文月份)
SELECT LAST_DAY('2020-11-13');-- 2020-11-30 (返回月份中最后一天)
-- DATE_ADD(date,INTERVAL expr type) 从日期加上指定的时间间隔
SELECT DATE_ADD(@dt,INTERVAL 1 YEAR);-- 表示：2021-11-13 14:50:05.123456
SELECT DATE_ADD(@dt,INTERVAL 1 QUARTER);-- 表示：2021-02-13 14:50:05.123456
SELECT DATE_ADD(@dt,INTERVAL 1 MONTH);-- 表示：2020-12-13 14:50:05.123456
SELECT DATE_ADD(@dt,INTERVAL 1 WEEK);-- 表示：2020-11-20 14:50:05.123456
SELECT DATE_ADD(@dt,INTERVAL 1 DAY);-- 表示：2020-11-14 14:50:05.123456
SELECT DATE_ADD(@dt,INTERVAL 1 HOUR);-- 表示：2020-11-13 15:50:05.123456
SELECT DATE_ADD(@dt,INTERVAL 1 MINUTE);-- 表示：2020-11-13 14:51:05.123456
SELECT DATE_ADD(@dt,INTERVAL 1 SECOND);-- 表示：2020-11-13 14:50:06.123456
SELECT DATE_ADD(@dt,INTERVAL 1 MICROSECOND);-- 表示：2020-11-13 14:50:05.123457
-- DATE_SUB(date,INTERVAL expr type) 从日期减去指定的时间间隔
SELECT DATE_SUB(@dt,INTERVAL 1 YEAR);-- 表示：2019-11-13 14:50:05.123456
SELECT DATE_SUB(@dt,INTERVAL 1 QUARTER);-- 表示：2020-08-13 14:50:05.123456
SELECT DATE_SUB(@dt,INTERVAL 1 MONTH);-- 表示：2020-10-13 14:50:05.123456
SELECT DATE_SUB(@dt,INTERVAL 1 WEEK);-- 表示：2020-11-06 14:50:05.123456
SELECT DATE_SUB(@dt,INTERVAL 1 DAY);-- 表示：2020-11-12 14:50:05.123456
SELECT DATE_SUB(@dt,INTERVAL 1 HOUR);-- 表示：2020-11-13 13:50:05.123456
SELECT DATE_SUB(@dt,INTERVAL 1 MINUTE);-- 表示：2020-11-13 14:49:05.123456
SELECT DATE_SUB(@dt,INTERVAL 1 SECOND);-- 表示：2020-11-13 14:50:04.123456
SELECT DATE_SUB(@dt,INTERVAL 1 MICROSECOND);-- 表示：2020-11-13 14:50:05.123455
-- MySQL date_sub() 日期时间函数 和 date_add() 用法一致，并且可以用INTERNAL -1 xxx的形式互换使用；
-- 另外，MySQL 中还有两个函数 subdate(), subtime()，建议，用 date_sub() 来替代。
-- MySQL 另类日期函数：period_add(P,N), period_diff(P1,P2)
-- 函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”，第二个参数“N” 表示增加或减去 N month（月）。
-- MySQL period_add(P,N)：日期加/减去N月。
SELECT PERIOD_ADD(202011,2), PERIOD_ADD(202011,-2);-- 202101  202009
-- period_diff(P1,P2)：日期 P1-P2，返回 N 个月。
SELECT PERIOD_DIFF(202101, 202011); -- 2
-- datediff(date1,date2)：两个日期相减 date1 - date2，返回天数
SELECT DATEDIFF('2020-11-13','2020-11-10');-- 3
-- TIMEDIFF(time1,time2)：两个日期相减 time1 - time2，返回 TIME 差值
SELECT TIMEDIFF('2020-11-13 19:28:37', '2020-11-13 17:00:00');-- 02:28:37
-- MySQL时间转换函数
SELECT TIME_TO_SEC('01:00:05'); -- 3605
SELECT SEC_TO_TIME(3605);-- 01:00:05
-- MySQL （日期、天数）转换函数：to_days(date), from_days(days)
SELECT TO_DAYS('0000-01-01'); -- 1 
SELECT TO_DAYS('2020-11-13'); -- 738107
SELECT FROM_DAYS(1);           -- '0000-00-00' ??有Bug？ 数值1-365均返回0000-00-00
SELECT FROM_DAYS(366);           -- '0001-01-01' 
SELECT FROM_DAYS(738107);      -- '2020-11-13'
-- MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)
SELECT STR_TO_DATE('11.13.2020 19:40:30', '%m.%d.%Y %H:%i:%s');-- 2020-11-13 19:40:30
SELECT STR_TO_DATE('11/13/2020', '%m/%d/%Y');                  -- 2020-11-13
SELECT STR_TO_DATE('2020/11/13','%Y/%m/%d')		       -- 2020-11-13
SELECT STR_TO_DATE('20:09:30', '%h:%i:%s')		       -- NULL(超过12时的小时用小写h，得到的结果为NULL,应该用H)
-- 日期时间格式化
SELECT DATE_FORMAT('2020-11-13 17:03:51', '%Y年%m月%d日 %H时%i分%s秒');-- 2020年11月13日 17时03分51秒 (具体需要什么格式的数据根据实际情况来;小写h为12小时制;)
SELECT TIME_FORMAT('2020-11-13 17:03:51', '%Y年%m月%d日 %H时%i分%s秒');-- NULL (time_format()只能用于时间的格式化)
SELECT TIME_FORMAT('17:03:51', '%H时%i分%s秒'); -- 17时03分51秒
-- STR_TO_DATE()和DATE_FORMATE()为互逆操作
-- MySQL 获得国家地区时间格式函数：get_format()
-- MySQL get_format() 语法：get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'
-- MySQL get_format() 用法的全部示例：
SELECT GET_FORMAT(DATE,'usa');   	-- '%m.%d.%Y' 
SELECT GET_FORMAT(DATE,'jis');   	-- '%Y-%m-%d' 
SELECT GET_FORMAT(DATE,'iso');   	-- '%Y-%m-%d' 
SELECT GET_FORMAT(DATE,'eur');   	-- '%d.%m.%Y' 
SELECT GET_FORMAT(DATE,'internal');   	-- '%Y%m%d' 
SELECT GET_FORMAT(DATETIME,'usa');   	-- '%Y-%m-%d %H.%i.%s' 
SELECT GET_FORMAT(DATETIME,'jis');   	-- '%Y-%m-%d %H:%i:%s' 
SELECT GET_FORMAT(DATETIME,'iso');   	-- '%Y-%m-%d %H:%i:%s' 
SELECT GET_FORMAT(DATETIME,'eur');   	-- '%Y-%m-%d %H.%i.%s' 
SELECT GET_FORMAT(DATETIME,'internal'); -- '%Y%m%d%H%i%s' 
SELECT GET_FORMAT(TIME,'usa');   	-- '%h:%i:%s %p' 
SELECT GET_FORMAT(TIME,'jis');   	-- '%H:%i:%s' 
SELECT GET_FORMAT(TIME,'iso');   	-- '%H:%i:%s' 
SELECT GET_FORMAT(TIME,'eur');   	-- '%H.%i.%s' 
SELECT GET_FORMAT(TIME,'internal');     -- '%H%i%s'
-- MySQL 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)
SELECT MAKEDATE(2020,31);   -- '2020-01-31' 
SELECT MAKEDATE(2020,32);   -- '2020-02-01'
SELECT MAKETIME(19,52,35);  -- '19:52:35'
-- MySQL 时区（timezone）转换函数：convert_tz(dt,from_tz,to_tz)
SELECT CONVERT_TZ('2020-11-13 19:54:12', '+08:00', '+00:00'); -- 2020-11-13 11:54:12
-- MySQL （Unix 时间戳、日期）转换函数
-- unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp,format)
-- 将具体时间时间转为timestamp
SELECT UNIX_TIMESTAMP();-- 当前时间的时间戳：1605264540
SELECT UNIX_TIMESTAMP('2020-11-13');-- 指定日期的时间戳：1605196800
SELECT UNIX_TIMESTAMP('2020-11-13 18:48:14');-- 指定日期时间的时间戳：1605264494
-- 将时间戳转为具体时间
SELECT FROM_UNIXTIME(1605264540);-- 2020-11-13 18:49:00
SELECT FROM_UNIXTIME(1605264540, '%Y年%m月%d日 %h时%i分:%s秒');-- 2020年11月13日 06时49分:00秒 获取时间戳对应的格式化日期时间
-- MySQL 时间戳（timestamp）转换、增、减函数
SELECT TIMESTAMP('2020-11-13');-- 2020-11-13 00:00:00
SELECT TIMESTAMP('2020-11-13 08:12:25', '01:01:01');-- 2020-11-13 09:13:26
SELECT DATE_ADD('2020-11-13 08:12:25', INTERVAL 1 DAY);-- 2020-11-14 08:12:25
SELECT TIMESTAMPADD(DAY, 1, '2020-11-13 08:12:25');-- 2020-11-14 08:12:25; MySQL timestampadd() 函数类似于 date_add()。
SELECT TIMESTAMPDIFF(YEAR, '2020-11-13', '2021-02-11');-- 0(注意这里不是年-年)
SELECT TIMESTAMPDIFF(MONTH, '2020-11-13', '2021-02-11');-- 2
SELECT TIMESTAMPDIFF(DAY, '2020-11-13', '2021-02-11');-- 90
SELECT TIMESTAMPDIFF(HOUR, '2020-11-13 08:12:25', '2021-02-11 20:00:00');-- 2171
SELECT TIMESTAMPDIFF(MINUTE, '2020-11-13 08:12:25', '2021-02-11 20:00:00');-- 130307

MySQL可以使用的格式：

PostgreSQL

PostgreSQL日期和时间类型

PostgreSQL支持SQL中所有的日期和时间类型，如表所示。

time、timestamp和interval接受一个可选的精度值 p，这个精度值声明在秒域中小数点之后保留的位数。缺省情况下，在精度上没有明确的边界。p允许的范围是从 0 到 6。

interval类型有一个附加选项，它可以通过写下面之一的短语来限制存储的fields的集合：

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

注意如果fields和p被指定，fields必须包括SECOND，因为精度只应用于秒。

PostgreSQL中with和without time zone的区别：从名字上可以看出一个是是带时区的，另一个是不带时区的，查出来的时间是一样的，只是一个带时区标志，一个不带而已，时区的基准是格林威治时间UTC。比如中国(PRC),时区是东八区,带时区标志的话就是+08。

SET timezone = "US/Pacific";
SHOW timezone; -- US/Pacific
SELECT
now( ) :: TIMESTAMP WITH TIME ZONE, -- 2020-11-22 18:13:32.034831-08
now( ) :: TIMESTAMP WITHOUT TIME ZONE; -- 2020-11-22 18:13:32.034831
SET timezone = PRC;
SHOW timezone;  -- PRC
SELECT
now( ) :: TIMESTAMP WITH TIME ZONE, --2020-11-23 10:13:32.037091+08
now( ) :: TIMESTAMP WITHOUT TIME ZONE; --2020-11-23 10:13:32.037091
SET timezone = UTC;
SHOW timezone; --UTC
SELECT
now( ) :: TIMESTAMP WITH TIME ZONE, --2020-11-23 02:13:32.040013+00
now( ) :: TIMESTAMP WITHOUT TIME ZONE; -- 2020-11-23 02:13:32.040013
SET timezone = "Asia/Shanghai";
SHOW timezone;  --Asia/Shanghai
SELECT
now( ) :: TIMESTAMP WITH TIME ZONE, -- 2020-11-23 10:13:32.042719+08
now( ) :: TIMESTAMP WITHOUT TIME ZONE; --2020-11-23 10:13:32.042719

备注：在PostgreSQL中，双冒号::是用于类型转换。

想要查看PostgreSQL支持的所有时区，可以通过以下SQL查看。

SELECT * FROM pg_timezone_names;

注意，在SQL中设置时区，只是临时性的修改，当关闭连接时会恢复到原来的时区，想要永久修改时区，需要修改配置文件。

PostgreSQL获取当前日期和时间

SELECT CURRENT_DATE; -- 2020-11-15
SELECT CURRENT_TIME; -- 19:53:10.138282+08
SELECT CURRENT_TIMESTAMP; --19:53:25.618691+08
SELECT CURRENT_TIME(0); -- 19:58:05+08,传入参数precision,可选值0-6,即秒后面的位数。
SELECT CURRENT_TIMESTAMP(1); -- 2020-11-15 19:58:40.7+08
SELECT LOCALTIME; -- 19:54:40.377817
SELECT LOCALTIMESTAMP; -- 2020-11-15 19:54:56.594073
SELECT LOCALTIME(0); -- 19:59:09
SELECT LOCALTIMESTAMP(1); -- 2020-11-15 19:59:19.9
SELECT now( ); -- 2020-11-23 10:25:50.801659+08

PostgreSQL如何获取当前Unix时间戳？

在PG数据库中，extract函数用于从一个日期中获取某个子集，比如获取年，月，日，时，分，秒等。

SELECT now( );-- 2020-11-23 10:40:28.85565+08
SELECT EXTRACT('HOUR' FROM now( )); -- 10 
SELECT EXTRACT('HOUR' FROM '2020-11-23 10:40:28.85565+08'::timestamp); -- 10
SELECT EXTRACT('epoch' FROM now( )); -- 1606099228.856519
SELECT TO_TIMESTAMP(1606099228.856519) -- 2020-11-23 10:40:28.856519+08

新纪元时间 Epoch 是以 1970-01-01 00:00:00 UTC 为标准的时间，将目标时间与 1970-01-01 00:00:00 时间的差值以秒来计算，单位是秒，可以是负值。

EXTRACT，date_part函数支持的field：

PostgreSQL的几个日期和时间

SELECT TIMESTAMP 'yesterday'; -- 2020-11-20 00:00:00 
SELECT DATE 'yesterday'; -- 2020-11-20
SELECT TIMESTAMP 'today'; -- 2020-11-21 00:00:00
SELECT DATE 'today'; -- 2020-11-21
SELECT TIMESTAMP 'tomorrow'; -- 2020-11-22 00:00:00
SELECT DATE 'tomorrow'; -- 2020-11-22
SELECT TIME 'allballs'; -- 00:00:00
SELECT now(); -- 2020-11-21 16:53:41.676354+08
SELECT TIMESTAMP 'now'; --2020-11-21 16:53:41.676898
SELECT DATE 'now'; -- 2020-11-21

PostgreSQL日期/时间格式化

PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数，它们都遵循一个公共的调用习惯：第一个参数是待格式化的值，而第二个是定义输出或输出格式的模板。

示例：

SELECT to_char( CURRENT_TIMESTAMP, 'HH12:MI:SS' ) -- 07:46:34
SELECT to_char( INTERVAL '15h 2m 12s', 'HH24:MI:SS' ) -- 15:02:12
SELECT to_date('05 Dec 2000', 'DD Mon YYYY') -- 2000-12-05
SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY') -- 2000-12-05 00:00:00+08

用于日期/时间格式化的模板模式

用于数值格式化的模板模式：

PostgreSQL时间/日期函数和操作符

下面是PostgreSQL中支持的时间/日期操作符的列表：

日期/时间函数：

SQL Server(T-SQL)

SELECT GETDATE();-- 获取系统当前日期的函数 -- 2020-11-23 11:30:02.080
SELECT GETUTCDATE();-- 返回UTC日期的函数 -- 2020-11-23 03:30:02.080
SELECT CURRENT_TIMESTAMP; --返回包含计算机的日期和时间的 datetime 值 -- 2020-11-23 11:52:18.153
SELECT SYSDATETIME(); -- 返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例在该计算机上运行。返回值不包括时区偏移量。-- 2020-11-23 11:53:40.5237747
SELECT SYSDATETIMEOFFSET(); --返回包含计算机的日期和时间的 datetimeoffset(7) 值，SQL Server 的实例在该计算机上运行 。 返回值包括时区偏移量。--2020-11-23 11:54:24.5511200+8
SELECT SYSUTCDATETIME(); -- 返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行 。 该函数返回日期和时间作为 UTC 时间（协调世界时）。 -- 2020-11-23 03:54:43.5515603
SELECT DAY(GETDATE()); -- 获取天数的函数DAY(d) -- 23
SELECT MONTH(GETDATE()); -- 获取月份的函数MONTH(d) -- 11
SELECT YEAR(GETDATE()); -- 获取年份的函数YEAR(d) -- 2020
--获取日期中指定部分字符串值的函数DATENAME(dp,d)
SELECT DATENAME(year, GETDATE()); -- 2020
SELECT DATENAME(quarter, GETDATE()); -- 4
SELECT DATENAME(month, GETDATE()); -- 11
SELECT DATENAME(dayofyear, GETDATE()) ; -- 328
SELECT DATENAME(day, GETDATE()); -- 23
SELECT DATENAME(week, GETDATE()); -- 48
SELECT DATENAME(weekday, GETDATE()); -- 星期一
SELECT DATENAME(hour, GETDATE()); -- 11
SELECT DATENAME(minute, GETDATE()); -- 42
SELECT DATENAME(second, GETDATE()); -- 45
SELECT DATENAME(millisecond, GETDATE()); -- 220
SELECT DATENAME(microsecond, GETDATE()); -- 220000
SELECT DATENAME(ISO_WEEK, GETDATE()); -- 48
-- 获取日期中指定部分的整数值的函数DATEPART(dp,d) --与DATENAME类似，不同的是返回这里返回的int型，上面返回的是nvarchar类型
SELECT DATEPART(year, GETDATE()); -- 2020
SELECT DATEPART(quarter, GETDATE()); -- 4
SELECT DATEPART(month, GETDATE()); -- 11
SELECT DATEPART(dayofyear, GETDATE()) ; -- 328
SELECT DATEPART(day, GETDATE()); -- 23
SELECT DATEPART(week, GETDATE()); -- 48
SELECT DATEPART(weekday, GETDATE()); -- 2
SELECT DATEPART(hour, GETDATE()); -- 11
SELECT DATEPART(minute, GETDATE()); -- 42
SELECT DATEPART(second, GETDATE()); -- 45
SELECT DATEPART(millisecond, GETDATE()); -- 220
SELECT DATEPART(microsecond, GETDATE()); -- 220000
SELECT DATEPART(ISO_WEEK, GETDATE()); -- 48

SQL Server日期计算函数：

• DATEDIFF(datepart , startdate , enddate)：返回两个指定日期之间所跨的日期或时间 datepart 边界数。
• DATEDIFF_BIG(datepart , startdate , enddate)：同上，返回数据类型为bigint
• DATEADD(datepart , number , date)：通过将一个时间间隔与指定 date 的指定 datepart 相加，返回一个新的 datetime 值。

SQL Server日期格式化：

CAST ( expression AS data_type [ ( length ) ] )  
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )  

支持的格式：

SELECT CONVERT(VARCHAR (12), getdate(), 111); -- 2020/11/23
SELECT CONVERT(VARCHAR (12), getdate(), 112); -- 20201123
SELECT CONVERT(VARCHAR (12), getdate(), 102); -- 2020.11.23
SELECT CONVERT(VARCHAR (12), getdate(), 101); -- 11/23/2020
SELECT CONVERT(VARCHAR (12), getdate(), 103); -- 23/11/2020
SELECT CONVERT(VARCHAR (12), getdate(), 104); -- 23.11.2020
SELECT CONVERT(VARCHAR (12), getdate(), 105); -- 23-11-2020
SELECT CONVERT(VARCHAR (12), getdate(), 106); -- 23 11 2020
SELECT CONVERT(VARCHAR (12), getdate(), 107); -- 11 23, 2020
SELECT CONVERT(VARCHAR (12), getdate(), 108); -- 13:29:34
SELECT CONVERT(VARCHAR (12), getdate(), 109); -- 11 23 2020  
SELECT CONVERT(VARCHAR (12), getdate(), 110); -- 11-23-2020
SELECT CONVERT(VARCHAR (12), getdate(), 113); -- 23 11 2020 1
SELECT CONVERT(VARCHAR (12), getdate(), 114); -- 13:29:34:240

MySQL中有内置函数from_unixtime和unix_timestamp来将时间和unix时间戳进行互转，而SQLServer没有类似的函数，为了方便我们可以自己添加，需要用到SQLServer的标量值函数。

将DateTime转换成时间戳：

CREATE FUNCTION [dbo].[Fn_Unix_Timestamp] (@time datetime)  
RETURNS int  AS  
BEGIN 
return DATEDIFF(s, '19700101', @time)
END

将时间戳转换成DateTime：

CREATE FUNCTION [dbo].[Fn_From_UnixTime] (@timestamp int)  
RETURNS DateTime  AS  
BEGIN 
return DATEADD(s, @timestamp, '19700101')
END

参考链接：

• 用于将日期时间格式化为字符串的字符
• PostgreSQL时间/日期函数和操作符
• PostgreSQL数据类型格式化函数
• 日期和时间数据类型及函数 (Transact-SQL)