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条件就是取昨天特定时间段(昨天凌晨零点到凌晨六点 or 昨天18点到昨天24点)的SQL写法。
参考:https://www.postgresql.org/docs/10/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC