PostgreSQL - 日期函数汇总

2018-12-19 2022-01-09 约 969 字预计阅读 2 分钟次阅读条评论

比较两个日期之间的时间差超过N个小时

在PostgreSQL中，两个时间戳相减会得到一个interval类型的结果，如下：

1
2
select now() - '2021-03-28 15:47:07'; --0 years 0 mons 2 days 0 hours 1 mins 15.081206 secs
select '2021-03-28 15:47:07' - now(); --0 years 0 mons -2 days 0 hours -3 mins -17.692835 secs

通过对比两个interval类型即可得到我们想要的结果，PostgreSQL会自行对interval类型进行处理，如下：

1
2
3
select interval '0 years 100 mons 2 days 0 hours' > interval '4years'; --true

select now() - '2021-03-28 15:47:07' > interval '4days'; --false

比较日期时隐藏的坑

PostgreSQl在比较两个timestamp时，隐藏了一个坑：在用DBeaver查看数据时，这个timestamp的值是看不到毫秒值的，在比较的时候可能不小心就被毫秒值坑了，如下：

1
select * from cnt_item where updated_on > '2019-05-13 15:49:26';

上述的条件是可能查出来更新日期为2019-05-13 15:49:26的数据的，原因是上诉条件等价于updated_on > '2019-05-13 15:49:26 000'，有部分数据的毫秒值虽然看不到，但却大于条件中的毫秒值，因此会造成查询出问题的错觉。

在查询日期时，应该尽量根据实际情况将秒值增大一秒或变小一秒。

EXTRACT函数对日期进行处理

语法如下：

1
EXTRACT(type FROM date)

data是日期，也可以是时间间距类型interval。这里的type需要指定为以下的值之一：

type	value
day	返回日期值
month	返回月份值
year	返回年份值
doy（day of year）	返回年中的第几天
dow（day of week）	返回星期几
quarter	返回季度
epoch	将当前日期转化为秒值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select now(); --2021-03-30 17:21:50

select extract(day from(now())); --30
select extract(month from(now())); --3
select extract(year from(now())); --2021
select extract(doy from(now())); --89
select extract(dow from(now())); --2
select extract(quarter from(now())); --1

select extract(epoch from '01:00:06.678'::time); --3606.678
select extract(epoch from(interval '0 years 0 mons 0 days 0 hours 10 mins 10.00 secs')); --610

如果需要把时间转化成毫秒值，直接把结果乘以1000就行。

去掉日期的毫秒值

向下取整函数floor()

1
select floor(extract(epoch from '03:21:06.678'::time));

向上取整函数ceil()或ceiling()，这两个一样的

1
2
select ceil(extract(epoch from '03:21:06.678'::time));
select ceiling(extract(epoch from '03:21:06.678'::time));

四舍五入函数round()

1
select round(extract(epoch from '03:21:06.678'::time));

补充：epoch新纪元时间

新纪元时间 Epoch 是以1970-01-01 00:00:00 UTC为标准的时间，将目标时间与1970-01-01 00:00:00时间的差值以秒来计算，单位是秒，可以是负值; 有些应用会将时间存储成epoch 时间形式，以提高读取效率。

参考链接

警告

本文最后更新于 January 9, 2022，文中内容可能已过时，请谨慎使用。

赞赏支持

微信打赏

支付宝打赏

目录

PostgreSQL - 日期函数汇总

比较两个日期之间的时间差超过N个小时

比较日期时隐藏的坑

EXTRACT函数对日期进行处理

去掉日期的毫秒值

补充：epoch新纪元时间

参考链接