I have a column starts_at with a type of TIMESTAMP WITHOUT TIME ZONE because it's representing the time of an appointment and should not change during a DST shift.
However, our library that handles recurring appointments needs this time in UTC. I am attempting to convert starts_at to UTC, but am seeing that I'm getting times representing MDT (daylight savings time) rather than MST (standard time).
For example, take the following:
SELECT starts_at, timezone('America/Denver', starts_at) AS new_starts_at
I would expect to get the following result:
--------------------------------------------------
| starts_at | new_starts_at
--------------------------------------------------
| 2018-09-04 13:05:00 | 2018-09-04 20:05:00+00
Instead, I'm getting the following:
--------------------------------------------------
| starts_at | new_starts_at
--------------------------------------------------
| 2018-09-04 13:05:00 | 2018-09-04 19:05:00+00
new_starts_at should be returning in MST, which would be 2018-09-04 20:05:00+00. My impression was that using the Olsen timezone (America/Denver) would inform Postgres of whether or not there was a DST shift in place. If I replace America/Denver with MST, I see the correct result.
I'm sure this is just a misunderstanding of Postgres timezone types on my part. That said, thanks in advance for the education!
The expression timezone('America/Denver', starts_at) interprets starts_at as being in Denver local time, the result is a timestamp with time zone.
Now when you output that value, it is transformed to your session time zone, which is UTC.
13:05 in Denver is 19:05 in UTC, which happens to be your session time zone.
During daylight savings time, Denver is offset 6 hours from UTC.