PostgreSQL数据库中取昨天特定时间段的SQL写法

highgo=# create table test_bb (c2 timestamp);
CREATE TABLE
highgo=# insert into test_bb values (to_timestamp('2018-06-05 19:55:44' ,'yyyy-mm-dd hh24:mi:ss'));
INSERT 0 1
highgo=# insert into test_bb values (to_timestamp('2018-06-05 03:55:44' ,'yyyy-mm-dd hh24:mi:ss'));
INSERT 0 1
highgo=# insert into test_bb values (to_timestamp('2018-06-05 13:55:44' ,'yyyy-mm-dd hh24:mi:ss'));
INSERT 0 1
highgo=# 
highgo=# 
highgo=# select * from test_bb;
         c2          
---------------------
 2018-06-05 19:55:44
 2018-06-05 03:55:44
 2018-06-05 13:55:44
(3 rows)


highgo=# select date_trunc('day',now());
       date_trunc       
------------------------
 2018-06-06 00:00:00+08
(1 row)


highgo=# select date_trunc('day',now()) -interval '1d';
        ?column?        
------------------------
 2018-06-05 00:00:00+08
(1 row)


highgo=# select date_trunc('day',now()) -interval '1d'+interval '6 hours';
        ?column?        
------------------------
 2018-06-05 06:00:00+08
(1 row)


highgo=# select date_trunc('day',now())-interval '6 hours';
        ?column?        
------------------------
 2018-06-05 18:00:00+08
(1 row)


highgo=# select * from test_bb
highgo-#  where (c2 > date_trunc('day', now()) - interval '1 day' and c2 < date_trunc('day', now()) - interval '1d' + interval '6 hours')
highgo-#     or (c2 > date_trunc('day', now()) - interval '6 hours' and c2 < date_trunc('day', now()));
         c2          
---------------------
 2018-06-05 19:55:44
 2018-06-05 03:55:44
(2 rows)


highgo=# select * from test_bb;
         c2          
---------------------
 2018-06-05 19:55:44
 2018-06-05 03:55:44
 2018-06-05 13:55:44
(3 rows)

如上的这个where条件就是取昨天特定时间段的SQL写法。

参考:https://www.postgresql.org/docs/10/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

相关文章
相关标签/搜索
每日一句
    每一个你不满意的现在,都有一个你没有努力的曾经。
公众号推荐
   一个健康类的公众号,欢迎关注
小青桔健康