Rails / Postgres asks for all posts created after 5:00 pm, taking into account daylight saving time?
I'm looking for a query for all records in a table with times created_at
after 5pm, EST WITH summer savings in mind.
My DB is Postgres and all timestamps are stored in UTC just like a normal Rails application.
So let's say I have four entries
ID, created_at, time in EST (-5 offset from UTC),
1, "2017-01-01 22:46:21.829333", 5:46 PM
2, "2017-01-01 21:23:27.259393", 4:23 PM
-------- DST SWITCH -----
ID, created_at, time in EDT (-4 offset from UTC),
3, "2017-03-20 21:52:46.135713", 5:52 PM
4, "2017-06-21 20:08:53.034377", 4:08 PM
My query should return records 1 and 3, but should ignore 2 and 4.
I tried
SELECT "id",
"created_at"
FROM "orders"
WHERE (created_at::TIME WITHOUT TIME ZONE AT TIME ZONE 'utc' AT TIME ZONE 'US/Eastern' > ("created_at"::DATE + TIME '21:00')::TIME AT TIME ZONE 'utc' AT TIME ZONE 'US/Eastern')
ORDER BY "orders"."created_at" ASC
But this query will return record 1,2,3 when it shouldn't return 2.
source to share
Sorry for my previous wrong answer. I don't really like time in the USA.
all timestamps are stored in UTC format
So ("created_at"::DATE + TIME '21:00')::TIME AT TIME ZONE 'utc' AT TIME ZONE 'US/Eastern'
actually says it is utc time 21:00
, which is not necessarily guaranteed 5 PM
in EST because the timezone offset changes from 5 to 4. So the comparison condition is wrong.
You need to get the local time to compare, not the utc time. Try entering the following code:
SELECT
"id",
"created_at"
FROM "orders"
WHERE (created_at AT TIME ZONE 'utc' AT TIME ZONE 'US/Eastern') :: TIME > TIME '5:00 PM'
source to share