PostgreSQL常用时间函数与时间计算提取示例说明

文章目录

常用函数与常量

函数说明current_time现在的时间current_date今天的日期current_timestamp当前日期和时间now()当前的日期和时间和current_timestamp一样localtimetime，当前时间localtimestamp当前日期和时间timeofday()字符串，当前日期和时间age(timestamp1, timestamp2)计算年纪，timestamp1-timestamp2，结果类型是interval，类似于：29 years 9 mons 27 days

selectcurrent_date,current_time,current_timestamp,now(),localtime,localtimestamp,timeofday();

select age('2030-04-10','2000-06-13');

第1个参数减第2个参数，age返回值是interval，很方便算年龄

-- 时间戳转字符串再截取SELECTnow()::timestamp,substring(''||now()::timestampfrom1for19);

to_timestamp(字符串转时间戳、数字转时间戳)

函数说明to_timestamp(text, text)把字串转换成时间戳to_timestamp(double)把数字转时间戳

select to_timestamp(1859351600),to_timestamp('1859351600'::bigint),to_timestamp('2090 01 12','YYYY MM DD');

date与to_date(字符串转日期、时间戳转日期)

函数说明to_date(text, text)字串转日期，第1个参数是时间，第2个参数是格式化字符串date(timestamp)时间戳转日期date(text)字符串转日期

select to_date('2090 01 12','YYYY MM DD'),date(now()),date('2090-09-28');

在一些喜欢用字符串存时间的计算时非常有用，虽然不推荐在数据库上做计算，但是有时候有些特殊的项目，需要存储过程，这些函数能极大的简化计算。

例如，有些每日都要和前一日比(这里简化了只计算了日期)：

select priceday,to_char(to_date(priceday,'YYYYMMDD')-interval'1 day','YYYYmmdd')as lastDay from elt_data_table;

interval(时间计算)

interval还是非常有用，不仅仅可以用来表示时间差，还可以用来做时间计算。

基本操作与格式

操作符说明year年month月day天hour小时min分钟sec秒
加不加s不影响，interval '2 day’和interval '2 days’都可以。

selectnow(),now()-interval'1 day'as subday,now()+interval'1 week'as addweek,now()-interval'1 month'as submonth,now()+interval'1 year'as addyear;--加1年1月1天1时1分1秒selectnow()+interval'1 year 1 month 1 day 1 hour 1 min 1 sec';

混合运算

interval还可以执行乘除运算：

selectinterval'1 day'+interval'1 hour'as addmod,interval'1 day'-interval'1 hour'as submod,interval'1 hour'*doubleprecision'3.5'as mulmod,interval'1 hour'/doubleprecision'1.5'as divmod,current_time-interval'2 hours'as sub2h,current_date-interval'3 days'as sub3d,current_timestamp+interval'2 years'as add2y;

to_char(各种时间转字符串)

这里我们只看to_char转日期时间的部分
函数说明to_char(timestamp, text)把时间戳转换成字串to_char(interval, text)把时间间隔转为字串to_char(date, text)把时间间隔转为字串to_char(int, text)把整数转换成字串to_char(double precision, text)把实数/双精度数转换成字串to_char(numeric, text)把numeric转换成字串

select
to_char(current_timestamp,'YYYY/mm/dd HH24:MI:SS'),
to_char(current_date,'YYYYmmdd'),
to_char(interval'15h 2m 12s','HH24:MI:SS');

extract(提取时间字段，年月日时分秒，周、季度，第几周、第几天)

基本说明

函数说明extract(field from timestamp)获取时间戳字段extract(field from date)获取date字段extract(field from interval)从interval获取时间字段

selectcurrent_date,
extract(yearfromcurrent_date)as dyear,
extract(monthfromcurrent_date)as dmonth,
extract(dayfromcurrent_date)as dday,-- 时分秒这种只能从包含时间的数据取，例如timestamp
extract(hourfromcurrent_timestamp)as dhour,
extract(minutefromcurrent_timestamp)as dminute,
extract(secondfromcurrent_timestamp)as dsecond,
extract(week fromcurrent_date)as dweek,
extract(quarter fromcurrent_date)as dquarter;

selectcurrent_timestampas dtime,extract(epoch fromcurrent_timestamp)as depoch;-- 计算年龄SELECT extract(yearfrom age(TIMESTAMP'2030-07-08',TIMESTAMP'2000-07-07'));-- 计算相差月份select extract(yearfrom age(TIMESTAMP'2030-11-05',TIMESTAMP'2010-02-04'))*12+ extract(MONTHfrom age(TIMESTAMP'2030-11-05',TIMESTAMP'2010-02-04'));

常用时间字段

不区分大小写
时间字段说明YEAR年份MONTH月份，timestamp月份数(1-12)，interval月份数(0-11)DAY几号HOUR小时域(0-23)MINUTE分钟(0-59)QUARTER季度(1-4)SECOND秒(0-59)WEEK该年第几周EPOCH数字时间戳,1731315752.861393

其他时间字段

时间字段说明DOY一年的第几天(1-365/366)DOW星期几(0-6，星期天是0)MILLISECONDS毫秒MICROSECONDS微秒CENTURY世纪DECADE十年，年份除以10MILLENNIUM千年

date_part(类似extract)

基本和extract等价
函数说明date_part(text, timestamp)从时间戳提取时间字段date_part(text, date)从日期提取时间字段date_part(text, interval)从interval提取时间字段

select 
date_part('year',current_date)as dyear,
date_part('month',now())as dmonth,
date_part('day',now())as dday,
date_part('week',now())as dweek,
date_part('quarter',now())as dquarter;

date_trunc(时间截断，计算特殊时间，一年第1天，一个季度第1天，一周最后1天等)

主要用于计算一些特殊时间，
函数说明date_trunc(text, timestamp)截断成指定的精度，第1个参数指定精度

select date_trunc('year',now())as 当年第1天,
date_trunc('year',now()+interval'1 year')-interval'1 day'as 当年最后1天,
date_trunc('month',now())as 当月第1天,
date_trunc('month',now()+interval'1 month')-interval'1 day'as 当月最后1天,
date_trunc('quarter',now())as 当季度第1天,
date_trunc('quarter',now()+interval'3 month')-interval'1 day'as 当季度最后1天,
date_trunc('week',now())as 当周第1天,
date_trunc('week',now()+interval'1 week')-interval'1 day'as 当周最后1天;

综合应用-时间计算

时间差

-- 59只取分钟部分select date_part('minute',cast('2050-01-01 14:00:00'asTIMESTAMP)-cast('2050-01-01 13:00:10'asTIMESTAMP));selectround(date_part('epoch',TIMESTAMP'2050-05-05 14:00:00'-TIMESTAMP'2050-05-05 13:10:10'))as dsec,round(date_part('epoch',TIMESTAMP'2050-05-05 14:00:00'-TIMESTAMP'2050-05-05 13:10:10')/60)as dmin,round(date_part('epoch',TIMESTAMP'2050-05-05 14:00:00'-TIMESTAMP'2050-05-05 13:10:10')/3600)as dhour;selectdate('2050-01-10')-date('2050-01-01')as dday;

时间加减

selectcurrent_timestamp,current_timestamp+interval'1 year',current_timestamp+interval'1 month',current_timestamp+interval'1 day',current_timestamp+interval'1 hour',current_timestamp+interval'1 min',current_timestamp+interval'1 sec';selectnow()+interval'1 year 1 month 1 day 1 hour 1 min 1 sec'as r;selectnow()+(2||' day')::intervalas r;-- date类型：2050-01-17selectdate'2050-01-10'+integer'7'as r;-- date类型：2050-01-10selectdate'2050-01-10'+interval'1 hour'as r;selectdate'2050-01-01'+time'06:00'as r;-- timestamp类型：2050-01-01 06:00:00.000selecttimestamp'2050-01-01'+time'06:00'as r;-- 返回interval类型:1 day -01:00:00selectinterval'1 day'-interval'1 hour'as r1,interval'1 hour'*doubleprecision'3.5'as r2,interval'1 hour'/doubleprecision'1.5'as r3,timestamp'2050-01-01 23:00'-interval'23 hours'as r4,date'2050-10-01'-interval'1 hour'as r5;

日期时间格式化字符串

常用格式化字符

模式描述YYYY4位年MM月份号(01-12)DD一个月里的日子(01-31)HH24一天的小时数(00-23)MI分钟(00-59)SS秒(00-59)MS毫秒(000-999)

其他格式化字符

模式描述HH一天的小时数(01-12)HH12一天的小时数(01-12)US微秒(000000-999999)AM正午标识(大写)Y,YYY带逗号的年(4和更多位)YYY年的后三位YY年的后两位Y年的最后一位MONTH全长大写月份名(空白填充为9字符)Month全长混合大小写月份名(空白填充为9字符)month全长小写月份名(空白填充为9字符)MON大写缩写月份名(3字符)Mon缩写混合大小写月份名(3字符)mon小写缩写月份名(3字符)DAY全长大写日期名(空白填充为9字符)Day全长混合大小写日期名(空白填充为9字符)day全长小写日期名(空白填充为9字符)DY缩写大写日期名(3字符)Dy缩写混合大小写日期名(3字符)dy缩写小写日期名(3字符)DDD一年里的日子(001-366)D一周里的日子(1-7；周日是1)W一个月里的周数(1-5)(第一周从该月第一天开始)WW一年里的周数(1-53)(第一周从该年的第一天开始)