【PostgreSQL】------PG常用日期/时间函数实例代码

落日映苍穹つ 2022-11-28 10:38 362阅读 0赞

>     select "LIMIT_TIME", extract(DAY from ("LIMIT_TIME"-NOW() )) as aday from "DCS_ACCIDENT_POTENTIAL" --获取（DCS_ACCIDENT_POTENTIAL）表中（LIMIT_TIME）字段与当前时间NOW()相差几天；
>     
>     select now();--获取当前系统时间 2020-08-17 09:31:26.492431+08
>     
>     select current_timestamp;--获取当前系统时间 2020-08-17 09:31:26.492431+08
>     
>     select current_date;--获取当前日期 2020-08-17
>     
>     select current_time;--获取当前时间 09:34:42.366302+08
>     
>     select now() + interval '2 years' as  last2Year;--获取两年后的时间 2022-08-17 09:36:40.854075+08
>     select now() + interval '2 year' as  last2Year;--获取两年后的时间   2022-08-17 09:37:50.996994+08
>     select now() + interval '2 Y' as last2Year;--获取两年后的时间 2022-08-17 09:38:35.346416+08
>     
>     select now() + interval '1 month' as last1Month; --获取一个月后的时间 2020-09-17 09:39:29.063257+08
>     select now() + interval '1 M' as last1Month; --获取一个月后的时间 2020-09-17 09:39:29.063257+08
>     
>     select now() + interval '1 days' as last1Day; --获取一天后的时间 2020-08-18 09:42:33.355513+08
>     select now() + interval '1 D' as last1Day; --获取一天后的时间 2020-08-18 09:42:33.355513+08
>     
>     select now() +interval '1 hours' as last1Hours;--获取一个小时后的时间 2020-08-17 10:40:23.29305+08
>     select now() +interval '1 H' as last1Hours;--获取一个小时后的时间 2020-08-17 10:40:23.29305+08
>     
>     select now() +interval '10 min' as last10Min;--获取10分钟后的时间 2020-08-17 09:54:17.324703+08
>     select now() +interval '10 m' as last10Min;--获取10分钟后的时间 2020-08-17 09:54:17.324703+08
>     
>     SELECT now() +interval '2 week' as last2Week;--获取2周后的时间 2020-08-31 09:46:03.746295+08
>     SELECT now() -interval '2 week' as before2Week;--获取2周之前的时间 2020-08-03 09:46:26.128928+08
>     SELECT now() + '2 W' as last2Week;--获取2周之后的时间 2020-08-31 10:18:56.915496+08
>     
>     select age(now(), timestamp '2019-02-05');-- 计算两个时间差 1 year 6 mons 12 days 10:28:21.946535；
>     
>     select extract(year from now());-- 获取当前年;
>     
>     select extract(year from "LIMIT_TIME") FROM "DCS_ACCIDENT_POTENTIAL" ;-- 获取 LIMIT_TIME 字段 年 2020;
>     
>     select extract(month from now());--获取当前月 8
>     
>     select extract(day from timestamp '2013-04-13'); --获取日期的日 13
>     
>     SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');-- 天数 40
>     
>     select extract(doy from now()); --当前时间是这个年的第几天
>     
>     SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1597631594 * INTERVAL '1 second'; -- 把时间戳 换成日期
>     
>     select extract(epoch from now());--获取当前时间的时间戳；
> 
>