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

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

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

• 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

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;

date_part/date_trunc

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

Oracle

参考 EXTRACT (datetime)

sysdate, current_timestamp

select sysdate, current_timestamp from dual;

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;

select sysdate,
       extract(year from sysdate)  as year,
       extract(month from sysdate) as month,
       extract(day from sysdate)   as day
       -- extract(hour from  sysdate) as hour     -- error
       -- extract(minute from  sysdate) as minute -- error
       -- extract(second from  sysdate) as second -- error
from dual;

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;

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;

MySQL

参考 MySQL EXTRACT() 函数

now(), current_timestamp

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

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

其他

• 单位 millisecond 无法直接获取

SAP HANA

参考 SAP HANA Reference

current_timestamp

select current_timestamp

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

Impala

参考 impala系列: 时间函数

now(), current_timestamp()

SELECT now(), current_timestamp()

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

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

其他

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

Hive

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

current_timestamp

select unix_timestamp(), current_timestamp()

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

其他

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

小结

差异之处

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

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

原文：https://www.cnblogs.com/leoninew/p/12157537.html