2

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?

TrippRitter
  • 305
  • 2
  • 10
  • _Surely there is a way to store my date in one column and get it out the way it was originally created_: yes, the `text` type. – Daniel Vérité Mar 06 '16 at 18:16
  • Sounds as if you are using `timestamp with time zone`. If you don't want it converted use `timestamp` instead –  Mar 06 '16 at 19:58
  • @DanielVérité Yes, but that would prevent me from using any date functions. – TrippRitter Mar 06 '16 at 21:20
  • @a_horse_with_no_name I want my timezone stored. "without time zone" completely discards any timezone. There doesn't seem to be a data type in postgres that will truly store a timestamp and arbitrary timezone. – TrippRitter Mar 06 '16 at 21:25
  • `timestamp with time zone` does **not** store the time zone. If you want that, use a `timestamp` and store the time zone in a separate column. –  Mar 06 '16 at 21:37

1 Answers1

2

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.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575