在数据统计分析中,经常会遇到需要对时间进行格式转化或其他层面的内容。由于每种数据库自带的相关函数存在一定的差异,所以经常会记不得如何使用。今天做下简单的梳理。
在开始学习日期/时间函数先,先来了解下数据库中常见的日期/时间存储格式:
- 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
参考链接: