各数据源的时间/日期的提取能力对比

目录

• 时间/日期的提取能力对比
• Postgres
  • now(), current_timestamp
  • extract
  • date_part/date_trunc
• Oracle
  • sysdate, current_timestamp
  • extract
  • date_part/date_trunc 不支持
  • 其他
• SQLServer
  • getdate(), current_timestamp
  • datepart
• MySQL
  • now(), current_timestamp
  • extract
  • 其他
• SAP HANA
  • current_timestamp
  • extract
• Impala
  • now(), current_timestamp()
  • extract
  • date_part
  • 其他
• Hive
  • current_timestamp
  • year, month, day, hour, minute, second
  • 其他
• 小结
  • 差异之处

对各数据源的时间/日期的提取能力做了调研，结论见文末。

时间/日期的提取能力对比

• Postgres: 使用 extract 支持，Syntax: EXTRACT(field FROM source)
• Oracle: 使用 extract 支持，Syntax: Extract(field FROM expression)，有时区问题
• SQLServer: 使用 DATEPART 支持，Syntax: DATEPART(datepart, date)
• MySQL: 使用 extract 支持，Syntax: EXTRACT(field FROM date)
• SAP HANA: 使用 extract 支持，Syntax: EXTRACT (field FROM date)
• Impala: 使用 extract/date_trunc 支持, Syntax: EXTRACT(TIMESTAMP / DATE ts, STRING unit), DATE_PART(STRING part, TIMESTAMP / DATE date)，存在差异，
• Hive: 使用具体的函数, Syntax: year|month|day|hour|minute|second(date)

Postgres

参考 PostgreSQL EXTRACT Function

now(), current_timestamp

select now(), current_timestamp

now	current_timestamp
2019-12-31 07:02:48.348918	2019-12-31 07:02:48.348918

extract

select now(),
       extract(year from now())        as year,
       extract(month from now())       as month,
       extract(day from now())         as day,
       extract(hour from now())        as hour,
       extract(minute from now())      as minute,
       extract(second from now())      as second,
       extract(millisecond from now()) as millisecond;

now	year	month	day	hour	minute	second	millisecond
2019-12-31 07:03:03.323875	2019	12	31	7	3	3.323875	3323.875

date_part/date_trunc

select extract(year from now()),
       extract('year' from now()),
       date_part('year', now()),
       date_trunc('year', now())

date_part	date_part	date_part	date_trunc
2020	2020	2020	2020-01-01 00:00:00.000000

Oracle

参考 EXTRACT (datetime)

sysdate, current_timestamp

select sysdate, current_timestamp from dual;

SYSDATE	CURRENT_TIMESTAMP
2019-12-31 07:22:06	2019-12-31 07:22:06.819403

extract

select current_timestamp,
       extract(year from current_timestamp)   as year,
       extract(month from current_timestamp)  as month,
       extract(day from current_timestamp)    as day,
       extract(hour from current_timestamp)   as hour,
       extract(minute from current_timestamp) as minute,
       extract(second from current_timestamp) as second
from dual;

CURRENT_TIMESTAMP	YEAR	MONTH	DAY	HOUR	MINUTE	SECOND
2019-12-30 09:12:45.686054	2019	12	30	9	12	45.686054

需要注意的是，oracle 日期类型区分时区，对无时区日期取 hour/minute/second 不被支持，需要添加时区信息

extract(hour from sysdate) -- get error
extract(hour from sysdateat at time zone 'GMT') 
extract(hour from sysdateat at time zone 'Asia/Shanghai')

date_part/date_trunc 不支持

其他

• 单位 millisecond 无法直接获取，参考[How to extract millisecond from date in Oracle?]
• 提取 hour, minute,second 存在日期/日期类型限制

The field you are extracting must be a field of the datetime_value_expr or interval_value_expr. For example, you can extract only YEAR, MONTH, and DAY from a DATE value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP WITH TIME ZONE datatype.

SQLServer

参考 DATEPART (Transact-SQL)

getdate(), current_timestamp

select getdate(), current_timestamp;

2019-12-31 07:19:20.383	2019-12-31 07:19:20.383

datepart

select current_timestamp                        as "current_timestamp",
       datepart(year, current_timestamp)        as year,
       datepart(month, current_timestamp)       as month,
       datepart(day, current_timestamp)         as day,
       datepart(hour, current_timestamp)        as hour,
       datepart(minute, current_timestamp)      as minute,
       datepart(second, current_timestamp)      as second,
       datepart(millisecond, current_timestamp) as millisencond;

current_timestamp	year	month	day	hour	minute	second	millisecond
2019-12-31 07:20:16.423	2019	12	31	7	20	16	423

MySQL

参考 MySQL EXTRACT() 函数

now(), current_timestamp

select now(), now(3), current_timestamp, current_timestamp(), current_timestamp(6)

now()	now(3)	current_timestamp	current_timestamp()	current_timestamp(6)
2019-12-31 06:43:45	2019-12-31 06:43:45.651000000	2019-12-31 06:43:45	2019-12-31 06:43:45	2019-12-31 06:43:45.651273000

extract

select current_timestamp                         as "current_timestamp",
       extract(year from current_timestamp)   as year,
       extract(month from current_timestamp)  as month,
       extract(day from current_timestamp)    as day,
       extract(hour from current_timestamp)   as hour,
       extract(minute from current_timestamp) as minute,
       extract(second from current_timestamp) as second

current_timestamp	year	month	day	hour	minute	second
2019-12-31 06:45:44	2019	12	31	6	45	44

其他

• 单位 millisecond 无法直接获取

SAP HANA

参考 SAP HANA Reference

current_timestamp

select current_timestamp

CURRENT_TIMESTAMP

2019-12-31 07:27:34.191

extract

select current_timestamp                      as "current_timestamp",
       extract(year from current_timestamp)   as year,
       extract(month from current_timestamp)  as month,
       extract(day from current_timestamp)    as day,
       extract(hour from current_timestamp)   as hour,
       extract(minute from current_timestamp) as minute,
       extract(second from current_timestamp) as second

CURRENT_TIMESTAMP	YEAR	MONTH	DAY	HOUR	MINUTE	SECOND
2019-12-31 03:37:26.538	2019	12	31	3	37	26.538

Impala

参考 impala系列: 时间函数

now(), current_timestamp()

SELECT now(), current_timestamp()

expr_1	expr_2
2019-12-31 16:08:04.443631	2019-12-31 16:08:04.443631

extract

SELECT current_timestamp()                         as "current_timestamp",
       extract(current_timestamp(), 'year')        as year,
       extract(current_timestamp(), 'month')       as month,
       extract(current_timestamp(), 'day')         as day,
       extract(current_timestamp(), 'hour')        as hour,
       extract(current_timestamp(), 'minute')      as minute,
       extract(current_timestamp(), 'second')      as second,
       extract(current_timestamp(), 'millisecond') as millisecond,
       extract(current_timestamp(), 'epoch')       as epoch

current_timestamp	year	month	day	hour	minute	second	millisecond	epoch
2019-12-31 14:27:23.565292	2019	12	31	14	27	23	565	1577802443

date_part

SELECT current_timestamp()                           as "current_timestamp"
       date_part('year', current_timestamp())        as year,
       date_part('month', current_timestamp())       as month,
       date_part('day', current_timestamp())         as day,
       date_part('hour', current_timestamp())        as hour,
       date_part('minute', current_timestamp())      as minute,
       date_part('second', current_timestamp())      as second,
       date_part('millisecond', current_timestamp()) as millisecond,
       date_part('epoch', current_timestamp())       as epoch

current_timestamp	year	month	day	hour	minute	second	millisecond	epoch
2019-12-31 14:22:31.732459	2019	12	31	14	22	31	732	1577802151

其他

• extract()与 date_part() 相比参数顺序不同
• 文档上的单位QUARTER测试失败

Hive

参考 [hive日期函数](https://www.cnblogs.com/linn/p/6028385.html)

current_timestamp

select unix_timestamp(), current_timestamp()

_co	_c1
1577778047	2019-12-31 15:40:47.738

year, month, day, hour, minute, second

select current_timestamp(),
       year(current_timestamp())   as year,
       month(current_timestamp())  as month,
       day(current_timestamp())    as day,
       hour(current_timestamp())   as hour,
       minute(current_timestamp()) as minute,
       second(current_timestamp()) as second

_co	year	month	day	hour	minute	second
2019-12-31 15:45:20.14	2019	12	31	15	45	20

其他

• current_timestamp() 很诡异，追加 as a 或者 a 或者 as current 作为别名没有问题，但完整的 as current_timestamp报错，可能有语法限制

小结

差异之处

目前 Postgres, Oracle, SQLServer, MySQL, SAP HANA, Impala, Hive 在不同程度上实现了时间/日期的提取能力，以常用时间/日期单位为基准，各数据源的差异表如下

	Postgres	Oracle	SQLServer	MySQL	SAP HANA	Impala	Hive
YEAR	y	y	y	y	y	y	y
MONTH	y	y	y	y	y	y	y
DAY	y	y	y	y	y	y	y
HOUR	y	y	y	y	y	y	y
MINUTE	y	y	y	y	y	y	y
SECOND	连带 millisecond 的浮点	连带 millisecond 的浮点	整数	整数	连带 millisecond 的浮点	整数	整数
MILLISECOND	浮点	-	整数	-	-	整数	-