文章目录常用函数与常量to_timestamp(字符串转时间戳、数字转时间戳)date与to_date(字符串转日期、时间戳转日期)interval(时间计算)基本操作与格式混合运算to_char(各种时间转字符串)extract(提取时间字段年月日时分秒周、季度第几周、第几天)基本说明常用时间字段其他时间字段date_part(类似extract)date_trunc(时间截断计算特殊时间一年第1天一个季度第1天一周最后1天等)综合应用-时间计算时间差时间加减日期时间格式化字符串常用格式化字符其他格式化字符常用函数与常量函数说明current_time现在的时间current_date今天的日期current_timestamp当前日期和时间now()当前的日期和时间和current_timestamp一样localtimetime当前时间localtimestamp当前日期和时间timeofday()字符串当前日期和时间age(timestamp1, timestamp2)计算年纪timestamp1-timestamp2结果类型是interval类似于29 years 9 mons 27 daysselect current_date,current_time,current_timestamp,now(),localtime,localtimestamp,timeofday();select age(2030-04-10, 2000-06-13);第1个参数减第2个参数age返回值是interval很方便算年龄-- 时间戳转字符串再截取 SELECT now()::timestamp,substring(||now()::timestamp from 1 for 19);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);想要了解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’都可以。select now(),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秒 select now() interval 1 year 1 month 1 day 1 hour 1 min 1 sec;混合运算interval还可以执行乘除运算select interval 1 day interval 1 hour as addmod, interval 1 day - interval 1 hour as submod, interval 1 hour * double precision 3.5 as mulmod, interval 1 hour / double precision 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转日期时间的部分第2个参数都是格式化字符串关于日期时间部分可以参考后文日期时间格式化字符串函数说明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获取时间字段select current_date, extract(year from current_date) as dyear, extract(month from current_date) as dmonth, extract(day from current_date) as dday, -- 时分秒这种只能从包含时间的数据取例如timestamp extract(hour from current_timestamp) as dhour, extract(minute from current_timestamp) as dminute, extract(second from current_timestamp) as dsecond, extract(week from current_date) as dweek, extract(quarter from current_date) as dquarter;select current_timestamp as dtime,extract(epoch from current_timestamp) as depoch; -- 计算年龄 SELECT extract(year from age(TIMESTAMP 2030-07-08,TIMESTAMP 2000-07-07)); -- 计算相差月份 select extract(year from age(TIMESTAMP 2030-11-05,TIMESTAMP 2010-02-04)) * 12 extract(MONTH from 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 as TIMESTAMP)-cast(2050-01-01 13:00:10 as TIMESTAMP)); select round(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; select date(2050-01-10) - date(2050-01-01) as dday;时间加减select current_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; select now() interval 1 year 1 month 1 day 1 hour 1 min 1 sec as r; select now() (2 || day)::interval as r; -- date类型2050-01-17 select date 2050-01-10 integer 7 as r; -- date类型2050-01-10 select date 2050-01-10 interval 1 hour as r; select date 2050-01-01 time 06:00 as r; -- timestamp类型2050-01-01 06:00:00.000 select timestamp 2050-01-01 time 06:00 as r; -- 返回interval类型:1 day -01:00:00 select interval 1 day - interval 1 hour as r1, interval 1 hour * double precision 3.5 as r2, interval 1 hour / double precision 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)(第一周从该年的第一天开始)