CODEWITHSUNDEEP
databasepostgresql
defekas17
defekas17

Reputation: 171

compare only hours in timestamp column postgresql

I have a table with date like that:

OBJECT    TIMESTAMP_START        TIMESTAMP_END
House     2020-02-20 09:33:24    2020-02-20 09:33:33
Dog       2020-02-20 18:00:03    2020-02-21 18:33:22
Cat       2020-02-11 19:00:00    2020-02-11 19:15:23

I need to extract all objects,start timestamp and end timestamp whose timestamp start is between (18:00 hours and 09:00)

In that case was Dog and Cat

How could I make that in postgreSql ? Do you think is possible easily?

Thanks!

Upvotes: 0

Views: 940

Answers (3)

Belayer
Belayer

Reputation: 14861

You cannot do this with TIME alone because in hours 09:00 is always less than 18:00, and from 09:00 to 18:00 is the time to be excluded. You can get this by truncating to the start and adding the appropriate interval.

with the_table (object, timestamp_start,timestamp_end ) as 
   ( values ('House', '2020-02-20 09:33:24'::timestamp, '2020-02-20 09:33:33'::timestamp)  
          , ('Dog',   '2020-02-20 18:00:03'::timestamp, '2020-02-21 18:33:22'::timestamp)  
          , ('Cat',   '2020-02-11 19:00:00'::timestamp, '2020-02-11 19:15:23'::timestamp)
          , ('Mouse', '2020-02-11 20:00:00'::timestamp, '2020-02-12 08:00:00'::timestamp)    
   )
select *
from the_table
where timestamp_start between  
      date_trunc('day', timestamp_start) + interval '18 hours' and 
      date_trunc('day', timestamp_start) + interval '1 day 9 hours' ;

Of course this get all such rows matching the times even if they are years old. You might want to consider that as well. Just a suggestion.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

Since you exclude both bounds, a rare case where BETWEEN is correct:

select *
from   tbl
where  timestamp_start::time NOT BETWEEN time '09:00' AND time '18:00';

Upvotes: 1

user330315
user330315

Reputation:

You can cast the timestamp to time 

select *
from the_table
where timestamp_start::time > time '18:00'
  and timestamp_start::time < time '09:00'

Upvotes: 1

Related Questions