How to work with time offset in a postgresssql statement-CodePudding

I have a problem with postgressql statment and the time difference.

Due to the time difference I have problems when quering between midnight and 3 am. What is the (code-wise) cheapest way to solve this?

My solution simply takes too long

error-prone between 0 and 3 o'clock

    select p.n, p.e,s."name" , g."time", p."date" 
    from l 
        join g on l.g_id = g.id 
        join p on l.p_id = p.id 
        join s on l.s_id = s.id 
    where p."date" = current_date and p."time" >current_time- '3 hour'::interval

takes 4 times longer than previous

    select p.n, p.e, s."name" , g."time", p."date" 
    from l 
        join g on l.g_id = g.id 
        join p on l.p_id = p.id 
        join s on l.s_id = s.id 
    where p."date"   p."time" >(current_date current_time)- '3 hour'::interval

CodePudding user response：

Since you have a fixed time window and the date and time fields are already different below is more straight forward. (single table example)

drop TABLE if exists data;

create table data
(
    rid integer,
    adate   date,
    atime   time
);

insert into data (rid, adate, atime) values
(1, '2020-07-15', '23:59:59'),
(2, '2020-07-16', '00:00:00'),
(3, '2020-07-16', '01:00:00'),
(4, '2020-07-16', '03:00:00'),
(5, '2020-07-16', '03:00:01'),
(6, '2020-07-16', '04:00:00')
;

select rid, adate, atime from data
 where adate = '2020-07-16'
   and atime <= time '03:00';

CodePudding user response：

The first query can take advantage of optimizations on p.date and p.time, such as an index on p(date, time).

The second cannot take advantage of such an index. Nor does it have accurate statistics for the sum of the two columns.

So, the execution plans are likely to be different, explaining the difference in performance.