What is the correct syntax for getting timestamps before a certain time?

user_11357604 0
Viewed 66

I would like to return all the timestamps from a database as long as they are before 6 am on the day they were made. I tried to do this: 

SELECT source_created_at AT TIME ZONE 'US/PACIFIC' FROM time_entry
WHERE source_created_at AT TIME ZONE 'US/PACIFIC' < '06:00:00'::time

but got this error:

ERROR:  operator does not exist: timestamp without time zone < time without time zone
LINE 2: WHERE source_created_at AT TIME ZONE 'US/PACIFIC' < '06:00:0...

I also tried < '06:00:00' and TIME'06:00:00; but got the same error. I'm new to SQL and the documentation has not been helpful here.

postgresql sql
1 Answers

You cannot compare a timestamp to a time. You need to cast the timestamp to time first:

WHERE (source_created_at AT TIME ZONE 'US/PACIFIC')::time < '06:00:00'::time

Edit from the comments

If you want all timestamps before tomorrow 6 AM in the same timezone:

WHERE source_created_at at time zone 'US/PACIFIC'
    < current_date at time zone 'US/PACIFIC' + interval '1 day 6 hour'
user_106767160
Related