Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Compare only the times of two timestamps in postgres

In PostgreSQL, I need a way to determine if the current time is within the hours of operation of a certain model with an opens_at and closes_at timestamp. What would be a valid Postgres way of doing this.

For Sqlite3 I had:

                        (opens_at > closes_at AND
                        (TIME(opens_at) > :now OR :now < TIME(closes_at)))
                        OR
                        (closes_at > opens_at AND
                        (TIME(opens_at) < :now AND TIME(closes_at) > :now))

I forgot to mention this is running through ActiveRecord so :now is actually equal to the current UTC time.

like image 389
OneChillDude Avatar asked Sep 04 '25 16:09

OneChillDude


1 Answers

Probably the easiest thing to do is to cast the timestamps to times and use localtime:

opens_at::time > localtime or localtime < closes_at::time
...

Rails will have everything in UTC inside the database (including the database connection) so you don't have to worry about time zone issues.

like image 139
mu is too short Avatar answered Sep 07 '25 17:09

mu is too short