Postgres prevent timestamp with timezone conversion

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?

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é
– Daniel Vérité, Commented Mar 6, 2016 at 18:16
Sounds as if you are using timestamp with time zone. If you don't want it converted use timestamp instead — user330315
– user330315, Commented Mar 6, 2016 at 19:58
@DanielVérité Yes, but that would prevent me from using any date functions. — TrippRitter
– TrippRitter, Commented Mar 6, 2016 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
– TrippRitter, Commented Mar 6, 2016 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. — user330315
– user330315, Commented Mar 6, 2016 at 21:37

Matt Johnson-Pint · Accepted Answer · 2016-03-07 00:52:14Z

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.

edited Mar 7, 2016 at 0:52

answered Mar 6, 2016 at 19:55

Matt Johnson-Pint

244k75 gold badges469 silver badges621 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TrippRitter Over a year ago

Thats what I was afraid of. Thanks :)

Collectives™ on Stack Overflow

Postgres prevent timestamp with timezone conversion

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related