Postgres prevent timestamp with timezone conversio

Question

I have a table that I am using to store iso dates with timezones. I realize that dates should "always" be stored as utc but I have an exception to that rule. The timestamps aren't in any way related to the server they are running on. I want to be able to store an iso date like this: 2016-03-06T01:15:52-06:00 And regardless of the time zone of the server or anything else I want the timestamp returned as: 2016-03-06T01:15:52-06:00

Currently if I insert an iso date it automatically converts it to whatever the server timezone is. My above date gets converted to: 2016-03-06 07:15:52+00 (server is utc)

The only thing I can think of is storing the timezone offset in a separate column, storing my date as utc and then converting using the offset column, horribly messy. Surely there is a way to store my date in one column and get it out the way it was originally created?

Answer 1

Your proposed solution is correct. Or more precisely, it is one of several correct implementations. Any of the following would work:

• Store the UTC timestamp in one field, store the offset in another.
• Store the local timestamp in one field, store the offset in another.
• Store the local date in one field, and store a time with time zone in another. (though time with time zone is generally discouraged...)
• Store the UTC timestamps in one field and the local timestamp in another.

The easiest by far is the first one, which you already proposed.

I'd avoid against storing timestamps in text fields, as they tend not to be very efficiently searchable.

Also note - if you're coming from a SQL Server background, you might recall its datetimeoffset type, which stores the local datetime and offset in the field, and uses the UTC equivalent during indexing. It's common to think that Postgres and MySQL's timestamp with time zone would have the same behavior, but they don't. They simply use the session time zone to convert to/from UTC. SQL Server has no concept of a session time zone, and thus the discrepancy.

Be sure to read this part of the Postgres docs.