I am looking to query for all records on a table with a created_at time of after 5 PM, EST WITH taking into account daylight savings.
My DB is Postgres and all timestamps are stored in UTC as a normal Rails app.
So, say I have four records
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 have 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 should not return 2.