Menu
In Postgres, I always store my dates using timestamp with time zone.
For the sake of the discussion, let assume I have to store events at the time (8am, 1pm and 10pm) in Eastern Time Zone (EST -04) on the 2018-01-16. The timestamp in the database would be :
- 8 am: "2018-01-16 12:00:00.000+00"
- 1 pm: "2018-01-16 17:00:00.000+00"
- 10pm: "2018-01-17 02:00:00.000+00"
How would I write a Postgres SQL query to get all events that occurred on the 2018-01-16 EST?
731
If you have a timestamp without time zone column and you're storing timestamps as UTC, you need to tell PostgreSQL that, and then tell it to convert it to your local time zone.
The timestamptz datatype is a time zone-aware date and time data type. PostgreSQL stores the timestamptz in UTC value. When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.
PostgreSQL assumes your local time zone for any type containing only date or time. All timezone-aware dates and times are stored internally in UTC .
The AT TIME ZONE construct is your friend:
WHERE CAST((tscol AT TIME ZONE 'EST') AS date) = DATE '2018-01-16'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
