pgsql 日期函数使用

1.date_part 函数日期相减得到相差天数.

select date_part('day', '2021-01-20 17:05'::timestamp - '2021-01-14 16:05'::timestamp);

2. date_part 获取当前日期周数

SELECT date_part('week',TIMESTAMP '2021-03-29');

3. date_trunc 获取当前周的周一日期

SELECT date_trunc('week', '2021-03-18'::timestamp);

4. 接下来 介绍 Postgresql 的trunc函数.

截断数字类型函数trunc为数字截断函数.移步postgresql: trunc函数.
截断日期类型函数date_trunc(text,time/timestamp/timestamptz);

select date_trunc('month',now()) +interval '12 h';
 
//每月1号 12点
select date_trunc('month',now()) + interval '15 d 9 h 30 min';
 
//每月15号9点半
select date_trunc('day',now()) + interval '9 h';
 
//每天9点
select date_trunc('day',now()) + interval '7 d';
 
//每周的今天
select date_trunc('weak',now())  + interval '1d 1minute';
 
//每周的周二第一分钟
select date_trunc('h',now()) + interval '30 minute';
 
//每小时
select date_trunc('minute',now()) + interval '30 second';
 
//每分钟
select date_trunc('minute',now()) + interval '30 minute 30 second';
 
//每30分钟
select date_trunc('quarter',now()) + interval '15 d 15 h 15 minute 30 second';
 
//本季度的第15天,15小时 15分 30秒
select date_trunc('quarter',now() ) - interval '1 h';
 
//每个季度最后一天的晚上11点
select date_trunc('quarter',now() + interval '3 month') - interval '1 h';
 

补充:

me=# select date_trunc('day',date '2018-05-15 09:00:00+08') + interval '9 h';
        ?column?
------------------------
 2018-05-15 09:00:00+08
(1 行记录)
 
me=# select date_trunc('day',date '2018-05-15 14:09:04.127444+08') + interval '9 h';
        ?column?
------------------------
 2018-05-15 09:00:00+08
(1 行记录)
 
me=# select date_trunc('day',date '2018-05-14 14:09:04.127444+08') + interval '9 h';
        ?column?
------------------------
 2018-05-14 09:00:00+08
(1 行记录)
 
me=# select date_trunc('day',time '2018-05-14 14:09:04.127444+08') + interval '9 h';
 ?column?
----------
 09:00:00
(1 行记录)
 
me=# select date_trunc('day',timestamp '2018-05-14 14:09:04.127444+08') + interval '9 h';
      ?column?
---------------------
 2018-05-14 09:00:00
(1 行记录)
 
me=# select date_trunc('day',timestamptz '2018-05-14 14:09:04.127444+08') + interval '9 h';
        ?column?
------------------------
 2018-05-14 09:00:00+08
(1 行记录)
 
me=# select date_trunc('day',timestamptz '2018-05-14 14:09:04.127444+08'+ interval '9 h');
       date_trunc
------------------------
 2018-05-14 00:00:00+08
(1 行记录)