目录

PostgreSQL - 日期函数汇总

## 比较两个日期之间的时间差超过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

EXTRACT函数对日期进行处理

语法如下:

1
EXTRACT(type FROM date)

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

typevalue
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 时间形式,以提高读取效率。

参考链接