您好,登錄后才能下訂單哦!
1.功能說明:
date_trunc: 截取給定時(shí)間(TIMESTAMP,date),獲得指定精度(時(shí),天,月,年)的初始使時(shí)間
2.一般時(shí)間
date_trunc('hour',TIMESTAMP '2018-08-16 20:38:40')
Result: 2018-08-16 20:00:00
date_trunc('day',TIMESTAMP '2018-08-16 20:38:40')
Result: 2018-08-16 00:00:00
date_trunc('month',TIMESTAMP '2018-08-16 20:38:40')
Result: 2018-08-01 00:00:00
date_trunc('year',TIMESTAMP '2018-08-16 20:38:40')
Result: 2018-01-01 00:00:00
3.特殊需求:
給定時(shí)間段的每年的所有月份的第一天,最后一天,下月第一天
-- Result: month_first_day, month_end_day, next_month
select date(zz) as month_first_day, date(zz + interval '1 month' - interval '1 day') as month_end_day, date(zz + interval '1 month') as next_month
from generate_series(date_trunc('year',to_date('20180510','yyyymmdd')),date_trunc('year',to_date('201905','yyyymmdd')),'1 month') as tt(zz);
SQL結(jié)果:
month_first_day | month_end_day | next_month
-----------------+---------------+------------
2018-01-01 | 2018-01-31 | 2018-02-01
2018-02-01 | 2018-02-28 | 2018-03-01
2018-03-01 | 2018-03-31 | 2018-04-01
2018-04-01 | 2018-04-30 | 2018-05-01
2018-05-01 | 2018-05-31 | 2018-06-01
2018-06-01 | 2018-06-30 | 2018-07-01
2018-07-01 | 2018-07-31 | 2018-08-01
2018-08-01 | 2018-08-31 | 2018-09-01
2018-09-01 | 2018-09-30 | 2018-10-01
2018-10-01 | 2018-10-31 | 2018-11-01
2018-11-01 | 2018-11-30 | 2018-12-01
2018-12-01 | 2018-12-31 | 2019-01-01
2019-01-01 | 2019-01-31 | 2019-02-01
(13 rows)
找出指定時(shí)間小時(shí),天,月,年的初始值
-- Result: dtrunc_hour, dtrunc_day, dtrunc_month, dtrunc_year
SELECT date_trunc('hour', TIMESTAMP '2018-08-16 20:38:40') as dtrunc_hour ,date_trunc('day', TIMESTAMP '2018-08-16 20:38:40') as dtrunc_day,date_trunc('month', TIMESTAMP '2018-08-16 20:38:40') as dtrunc_month,date_trunc('year', TIMESTAMP '2018-08-16 20:38:40') as dtrunc_year;
SQL結(jié)果:
dtrunc_hour | dtrunc_day | dtrunc_month | dtrunc_year
---------------------+---------------------+---------------------+---------------------
2018-08-16 20:00:00 | 2018-08-16 00:00:00 | 2018-08-01 00:00:00 | 2018-01-01 00:00:00
(1 row)
postgres=#
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。