器→工具, 数据, 术→技巧, 编程语言

SQL 日期/时间处理函数

钱魏Way · · 6,619 次浏览

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

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

  • 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)

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

符号 含义 表示 示例
a AM 或 PM 标记 文本 输入 am、AM、pm、PM。输出 AM 或 PM
d 一月中的某一天(1-31) 数字 1、20
dd 一月中的某一天(01-31) 数字 01、31
D 一年中的某一天(1-366) 数字 3、80、100
DD 一年中的某一天(01-366) 数字 03、80、366
DDD 一年中的某一天(001-366) 数字 003
e 一周中的某一天(1-7) 数字 2
EEE 一周中的某一天 文本 Tue
EEEE 一周中的某一天 文本 Tuesday
F 一月中某一周的某一天(1-5) 数字 2
G 时代 文本 BC 或 AD
h 用 AM 或 PM 表示的小时(1-12) 数字 6
hh 用 AM 或 PM 表示的小时(01-12) 数字 06
H 24 小时格式的时间(0-23) 数字 7
HH 24 小时格式的时间(00-23) 数字 07
I ISO8601 日期/时间(最多 yyyy-MM-dd’T’HH:mm:ss. SSSZZZ) 文本 2006-10-07T12:06:56.568+01:00
IU ISO8601 日期/时间(类似于 I,但是如果时区为 +00:00,那么 ZZZ 输出为“Z”) 文本 2006-10-07T12:06:56.568+01:00, 2003-12 -15T15:42:12.000Z
k 24 小时格式的时间(1-24) 数字 8
kk 24 小时格式的时间(01-24) 数字 08
K 用 AM 或 PM 表示的小时(0-11) 数字 9
KK 用 AM 或 PM 表示的小时(00-11) 数字 09
m 数字 4
mm 数字 04
M 月(数值) 数字 5、12
MM 月(数值) 数字 05、12
MMM 月(字母) 文本 Jan、Feb
MMMM 月(字母) 文本 January、February
s 数字 5
ss 数字 05
S 分秒 数字 7
SS 厘秒 数字 70
SSS 毫秒 数字 700
SSSS 0.0001 秒 数字 7000
SSSSS 0.00001 秒 数字 70000
SSSSSS 0.000001 秒 数字 700000
T ISO8601 时间(最多为 HH:mm:ss.SSSZZZ) 文本 12:06:56.568+01:00
TU ISO8601 时间(类似于 T,但是 +00:00 时区用“Z”替换) 文本 12:06:56.568+01:00、15:42:12.000Z
w 一年中的某一周 数字 7、53
ww 一年中的某一周 数字 07、53
W 一月中的某一周 数字 2
yy 数字 06
yyyy 数字 2006
YY 年:仅与年中的周结合使用 数字 06
YYYY 年:仅与年中的周结合使用 数字 2006
zzz 时区(简称) 文本 EST
zzzz 时区(全称) 文本 东部标准时间
Z 时区(+/-n) 文本 +3
ZZ 时区(+/-nn) 文本 +03
ZZZ 时区(+/-nn:nn) 文本 +03:00
ZZZU 时区(如同 ZZZ,“+00:00”由“Z”替换) 文本 +03:00、Z
ZZZZ 时区(GMT+/-nn:nn) 文本 GMT+03:00
ZZZZZ 时区(如同 ZZZ,但是无冒号)(+/-nnnn) 文本 +0300
文本的转义 ‘User text’
(两个单引号)转义文本中的单引号 ‘o”clock’

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可以使用的格式:

格式 描述
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月,数值(00-12)
%p AM 或 PM
%r 时间,12-小时(hh:mm:ss AM 或 PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天,与 %X 使用
%v 周 (01-53) 星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天 (0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位

PostgreSQL

PostgreSQL日期和时间类型

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

名字 存储尺寸 描述 最小值 最大值 解析度
timestamp [ (p) ] [ without time zone ] 8字节 包括日期和时间(无时区) 4713 BC 294276 AD 1微秒
timestamp [ (p) ] with time zone 8字节 包括日期和时间,有时区 4713 BC 294276 AD 1微秒
date 4字节 日期(没有一天中的时间) 4713 BC 5874897 AD 1日
time [ (p) ] [ without time zone ] 8字节 一天中的时间(无日期) 00:00:00 24:00:00 1微秒
time [ (p) ] with time zone 12字节 仅仅是一天中的时间(没有日期),带有时区 00:00:00+1459 24:00:00-1459 1微秒
interval [ fields ] [ (p) ] 16字节 时间间隔 -178000000年 178000000年 1微秒

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:

描述 例子 结果
CENTURY 世纪 EXTRACT(CENTURY FROM TIMESTAMP ‘2000-12-16 12:21:13’); 20
DAY (月分)里的日期域(1-31) EXTRACT(DAY from TIMESTAMP ‘2001-02-16 20:38:40’); 16
DECADE 年份域除以10 EXTRACT(DECADE from TIMESTAMP ‘2001-02-16 20:38:40’); 200
DOW 每周的星期号(0-6;星期天是0) (仅用于timestamp) EXTRACT(DOW FROM TIMESTAMP ‘2001-02-16 20:38:40’); 5
DOY 一年的第几天(1 -365/366) (仅用于 timestamp) EXTRACT(DOY from TIMESTAMP ‘2001-02-16 20:38:40’); 47
HOUR 小时域(0-23) EXTRACT(HOUR from TIMESTAMP ‘2001-02-16 20:38:40’); 20
MICROSECONDS 秒域,包括小数部分,乘以 1,000,000。 EXTRACT(MICROSECONDS from TIME ’17:12:28.5′); 28500000
MILLENNIUM 千年 EXTRACT(MILLENNIUM from TIMESTAMP ‘2001-02-16 20:38:40’); 3
MILLISECONDS 秒域,包括小数部分,乘以 1000。 EXTRACT(MILLISECONDS from TIME ’17:12:28.5′); 28500
MINUTE 分钟域(0-59) EXTRACT(MINUTE from TIMESTAMP ‘2001-02-16 20:38:40’); 38
MONTH 对于timestamp数值,它是一年里的月份数(1-12);对于interval数值,它是月的数目,然后对12取模(0-11) EXTRACT(MONTH from TIMESTAMP ‘2001-02-16 20:38:40’); 2
QUARTER 该天所在的该年的季度(1-4)(仅用于 timestamp) EXTRACT(QUARTER from TIMESTAMP ‘2001-02-16 20:38:40’); 1
SECOND 秒域,包括小数部分(0-59[1]) EXTRACT(SECOND from TIMESTAMP ‘2001-02-16 20:38:40’); 40
WEEK 该天在所在的年份里是第几周。 EXTRACT(WEEK from TIMESTAMP ‘2001-02-16 20:38:40’); 7
YEAR 年份域 EXTRACT(YEAR from TIMESTAMP ‘2001-02-16 20:38:40’); 2001

PostgreSQL的几个日期和时间

输入串 合法类型 描述
epoch date, timestamp 1970-01-01 00:00:00+00(Unix系统时间0)
infinity date, timestamp 比任何其他时间戳都晚
-infinity date, timestamp 比任何其他时间戳都早
now date, time, timestamp 当前事务的开始时间
today date, timestamp 今日午夜 (00:00)
tomorrow date, timestamp 明日午夜 (00:00)
yesterday date, timestamp 昨日午夜 (00:00)
allballs time 00:00:00.00 UTC
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)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数,它们都遵循一个公共的调用习惯:第一个参数是待格式化的值,而第二个是定义输出或输出格式的模板。

函数 返回类型 描述 例子
to_char(timestamp, text) text 把时间戳转成字符串 to_char(current_timestamp, ‘HH12:MI:SS’)
to_char(interval, text) text 把间隔转成字符串 to_char(interval ’15h 2m 12s’, ‘HH24:MI:SS’)
to_char(int, text) text 把整数转成字符串 to_char(125, ‘999’)
to_char(double precision, text) text 把实数或双精度转成字符串 to_char(125.8::real, ‘999D9’)
to_char(numeric, text) text 把数字转成字符串 to_char(-125.8, ‘999D99S’)
to_date(text, text) date 把字符串转成日期 to_date(’05 Dec 2000′, ‘DD Mon YYYY’)
to_number(text, text) numeric 把字符串转成数字 to_number(‘12,454.8-‘, ’99G999D9S’)
to_timestamp(text, text) timestamp with time zone 把字符串转成时间戳 to_timestamp(’05 Dec 2000′, ‘DD Mon YYYY’)

示例:

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

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

模式 描述
HH 一天中的小时 (01-12)
HH12 一天中的小时 (01-12)
HH24 一天中的小时 (00-23)
MI 分钟 (00-59)minute (00-59)
SS 秒(00-59)
MS 毫秒(000-999)
US 微秒(000000-999999)
SSSS 午夜后的秒(0-86399)
AM, am, PM or pm 正午指示器(不带句号)
A.M., a.m., P.M. or p.m. 正午指示器(带句号)
Y,YYY 带逗号的年(4 位或者更多位)
YYYY 年(4 位或者更多位)
YYY 年的后三位
YY 年的后两位
Y 年的最后一位
IYYY ISO 8601 周编号方式的年(4 位或更多位)
IYY ISO 8601 周编号方式的年的最后 3 位
IY ISO 8601 周编号方式的年的最后 2 位
I ISO 8601 周编号方式的年的最后一位
BC, bc, AD或者ad 纪元指示器(不带句号)
B.C., b.c., A.D.或者a.d. 纪元指示器(带句号)
MONTH 全大写形式的月名(空格补齐到 9 字符)
Month 全首字母大写形式的月名(空格补齐到 9 字符)
month 全小写形式的月名(空格补齐到 9 字符)
MON 简写的大写形式的月名(英文 3 字符,本地化长度可变)
Mon 简写的首字母大写形式的月名(英文 3 字符,本地化长度可变)
mon 简写的小写形式的月名(英文 3 字符,本地化长度可变)
MM 月编号(01-12)
DAY 全大写形式的日名(空格补齐到 9 字符)
Day 全首字母大写形式的日名(空格补齐到 9 字符)
day 全小写形式的日名(空格补齐到 9 字符)
DY 简写的大写形式的日名(英语 3 字符,本地化长度可变)
Dy 简写的首字母大写形式的日名(英语 3 字符,本地化长度可变)
dy 简写的小写形式的日名(英语 3 字符,本地化长度可变)
DDD 一年中的日(001-366)
IDDD ISO 8601 周编号方式的年中的日(001-371,年的第 1 日时第一个 ISO 周的周一)
DD 月中的日(01-31)
D 周中的日,周日(1)到周六(7)
ID 周中的 ISO 8601 日,周一(1)到周日(7)
W 月中的周(1-5)(第一周从该月的第一天开始)
WW 年中的周数(1-53)(第一周从该年的第一天开始)
IW ISO 8601 周编号方式的年中的周数(01 – 53;新的一年的第一个周四在第一周)
CC 世纪(2 位数)(21 世纪开始于 2001-01-01)
J 儒略日(从午夜 UTC 的公元前 4714 年 11 月 24 日开始的整数日数)
Q 季度(to_date和to_timestamp会忽略)
RM 大写形式的罗马计数法的月(I-XII;I 是 一月)
rm 小写形式的罗马计数法的月(i-xii;i 是 一月)
TZ 大写形式的时区缩写(仅在to_char中支持)
tz 小写形式的时区缩写(仅在to_char中支持)
TZH 时区的小时
TZM 时区的分钟
OF 从UTC开始的时区偏移(仅在to_char中支持)

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

模式 描述
9 数位(如果无意义可以被删除)
0 数位(即便没有意义也不会被删除)
. (period) 小数点
, (comma) 分组(千)分隔符
PR 尖括号内的负值
S 带符号的数字(使用区域)
L 货币符号(使用区域)
D 小数点(使用区域)
G 分组分隔符(使用区域)
MI 在指定位置的负号(如果数字 < 0)
PL 在指定位置的正号(如果数字 > 0)
SG 在指定位置的正/负号
RN 罗马数字(输入在 1 和 3999 之间)
TH or th 序数后缀
V 移动指定位数(参阅注解)
EEEE 科学记数的指数

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

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

操作符 例子 结果
+ date ‘2001-09-28’ + integer ‘7’ date ‘2001-10-05’
+ date ‘2001-09-28’ + interval ‘1 hour’ timestamp ‘2001-09-28 01:00:00’
+ date ‘2001-09-28′ + time ’03:00’ timestamp ‘2001-09-28 03:00:00’
+ interval ‘1 day’ + interval ‘1 hour’ interval ‘1 day 01:00:00’
+ timestamp ‘2001-09-28 01:00′ + interval ’23 hours’ timestamp ‘2001-09-29 00:00:00’
+ time ’01:00′ + interval ‘3 hours’ time ’04:00:00′
– interval ’23 hours’ interval ‘-23:00:00’
date ‘2001-10-01’ – date ‘2001-09-28’ integer ‘3’ (days)
date ‘2001-10-01’ – integer ‘7’ date ‘2001-09-24’
date ‘2001-09-28’ – interval ‘1 hour’ timestamp ‘2001-09-27 23:00:00’
time ’05:00′ – time ’03:00′ interval ’02:00:00′
time ’05:00′ – interval ‘2 hours’ time ’03:00:00′
timestamp ‘2001-09-28 23:00′ – interval ’23 hours’ timestamp ‘2001-09-28 00:00:00’
interval ‘1 day’ – interval ‘1 hour’ interval ‘1 day -01:00:00’
timestamp ‘2001-09-29 03:00’ – timestamp ‘2001-09-27 12:00’ interval ‘1 day 15:00:00’
* 900 * interval ‘1 second’ interval ’00:15:00′
* 21 * interval ‘1 day’ interval ’21 days’
* double precision ‘3.5’ * interval ‘1 hour’ interval ’03:30:00′
/ interval ‘1 hour’ / double precision ‘1.5’ interval ’00:40:00′

日期/时间函数:

函数 返回类型 描述 例子 结果
age(timestamp, timestamp) interval 减去参数,生成一个使用年、月(而不是只用日)的“符号化”的结果 age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’) 43 年 9 月 27 日
age(timestamp) interval 从current_date(在午夜)减去 age(timestamp ‘1957-06-13’) 43 years 8 mons 3 days
clock_timestamp() timestamp with time zone 当前日期和时间(在语句执行期间变化)
current_date date 当前日期
current_time time with time zone 当前时间(一天中的时间)
current_timestamp timestamp with time zone 当前日期和时间(当前事务开始时)
date_part(text, timestamp) double precision 获得子域(等价于extract) date_part(‘hour’, timestamp ‘2001-02-16 20:38:40’) 20
date_part(text, interval) double precision 获得子域(等价于extract) date_part(‘month’, interval ‘2 years 3 months’) 3
date_trunc(text, timestamp) timestamp 截断到指定精度 date_trunc(‘hour’, timestamp ‘2001-02-16 20:38:40’) 36938.83333
date_trunc(text, timestamp with time zone, text) timestamp with time zone 在指定的时区截断到指定的精度 date_trunc(‘day’, timestamptz ‘2001-02-16 20:38:40+00’, ‘Australia/Sydney’) 2001-02-16 13:00:00+00
date_trunc(text, interval) interval 截断到指定精度 date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’) 2 days 03:00:00
extract(field from timestamp) double precision 获得子域 extract(hour from timestamp ‘2001-02-16 20:38:40’) 20
extract(field from interval) double precision 获得子域 extract(month from interval ‘2 years 3 months’) 3
isfinite(date) boolean 测试有限日期(不是+/-无限) isfinite(date ‘2001-02-16’) TRUE
isfinite(timestamp) boolean 测试有限时间戳(不是+/-无限) isfinite(timestamp ‘2001-02-16 21:28:30’) TRUE
isfinite(interval) boolean 测试有限间隔 isfinite(interval ‘4 hours’) TRUE
justify_days(interval) interval 调整间隔这样30天时间周期可以表示为月 justify_days(interval ’35 days’) 1 mon 5 days
justify_hours(interval) interval 调整间隔这样24小时时间周期可以表示为日 justify_hours(interval ’27 hours’) 1 day 03:00:00
justify_interval(interval) interval 使用justify_days和justify_hours调整间隔,使用额外的符号调整 justify_interval(interval ‘1 mon -1 hour’) 29 days 23:00:00
localtime time 当前时间(一天中的时间)
localtimestamp timestamp 当前日期和时间(当前事务的开始)
make_date(year int, month int, day int) date 从年、月、日域创建日期 make_date(2013, 7, 15) 41470
make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) interval 从年、月、周、日、时、分、秒域创建 interval make_interval(days => 10) 10 days
make_time(hour int, min int, sec double precision) time 从时、分、秒域创建时间 make_time(8, 15, 23.5) 0.344021991
make_timestamp(year int, month int, day int, hour int, min int, sec double precision) timestamp 从年、月、日、时、分、秒域创建时间戳 make_timestamp(2013, 7, 15, 8, 15, 23.5) 41470.34402
make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ]) timestamp with time zone 从年、月、日、时、分、秒域创建带时区的时间戳。如果没有指定timezone, 则使用当前时区。 make_timestamptz(2013, 7, 15, 8, 15, 23.5) 2013-07-15 08:15:23.5+01
now() timestamp with time zone 当前日期和时间(当前事务的开始)
statement_timestamp() timestamp with time zone 当前日期和时间(当前事务的开始)
timeofday() text 当前日期和时间(像clock_timestamp,但是作为一个text字符串)
transaction_timestamp() timestamp with time zone 当前日期和时间(当前事务的开始)
to_timestamp(double precision) timestamp with time zone 把 Unix 时间(从 1970-01-01 00:00:00+00 开始的秒)转换成 timestamp to_timestamp(1284352323) 2010-09-13 04:32:03+00

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 ] )  

支持的格式:

不带世纪数位 (yy) (1) 带世纪数位 (yyyy) Standard 输入/输出 (3)
0 或 100 (1,2) datetime 和 smalldatetime 的默认值 mon dd yyyy hh:miAM(或 PM)
1 101 美国 1 = mm/dd/yy

101 = mm/dd/yyyy

2 102 ANSI 2 = yy.mm.dd

102 = yyyy.mm.dd

3 103 英国/法国 3 = dd/mm/yy

103 = dd/mm/yyyy

4 104 德语 4 = dd.mm.yy

104 = dd.mm.yyyy

5 105 意大利语 5 = dd-mm-yy

105 = dd-mm-yyyy

6 106 (1) 6 = dd mon yy

106 = dd mon yyyy

7 107 (1) 7 = Mon dd, yy

107 = Mon dd, yyyy

8 或 24 108 hh:mi:ss
9 或 109 (1,2) 默认格式 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM(或 PM)
10 110 USA 10 = mm-dd-yy

110 = mm-dd-yyyy

11 111 日本 11 = yy/mm/dd

111 = yyyy/mm/dd

12 112 ISO 12 = yymmdd

112 = yyyymmdd

13 或 113 (1,2) 欧洲默认格式 + 毫秒 dd mon yyyy hh:mi:ss:mmm(24 小时制)
14 114 hh:mi:ss:mmm(24 小时制)
20 或 120 (2) ODBC 规范 yyyy-mm-dd hh:mi:ss(24 小时制)
21、25 或 121 (2) time、date、datetime2 和 datetimeoffset 的 ODBC 规范(带毫秒)默认值 yyyy-mm-dd hh:mi:ss.mmm(24 小时制)
22 美国 mm/dd/yy hh:mi:ss AM(或 PM)
23 ISO8601 yyyy-mm-dd
126 ( 4) ISO8601 yyyy-mm-ddThh:mi:ss.mmm(无空格)

注意: 毫秒 (mmm) 值为 0 时,不会显示毫秒小数部分的值。例如,值“2012-11-07T18:26:20.000”显示为“2012-11-07T18:26:20”。

127 ( 6, 7) 带时区 Z 的 ISO8601。 yyyy-MM-ddThh:mm:ss.fffZ(不带空格)

注意: 毫秒 (mmm) 值为 0 时,不会显示毫秒小数值。例如,值“2012-11-07T18:26:20.000”显示为“2012-11-07T18:26:20”。

130 ( 1,2) 回历 (5) dd mon yyyy hh:mi:ss:mmmAM

在此样式中,mon 表示完整月份名称的多标记回历 unicode 表示形式。该值在 SSMS 的默认 US 安装中不会正确呈现。

131 ( 2) 回历 (5) dd/mm/yyyy hh:mi:ss:mmmAM
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

参考链接:

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注