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

目录

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

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

  • 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 不支持

其他

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 浮点 - 整数 - - 整数 -
posted @   leoninew  阅读(451)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· 单线程的Redis速度为什么快？
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 展开说说关于C#中ORM框架的用法！
点击右上角即可分享
微信分享提示