3 ms·

> Timestamps in DBs should never be non-UTC I think you might want to consider why some people might actually care to store timezone data, and why it almost al

by iod 7y ago

> Timestamps in DBs should never be non-UTC I think you might want to consider why some people might actually care to store timezone data, and why it almost always is needed. Timezone data is separate information that if not included, is now missing from your data forever unless someone goes and backfills it in. Without the explicit timezone stored, your timestamp could technically be in any timezone as far as the database is concerned. > I guess one could use timestamptz with a constraint saying that timezone is UTC, but is there a point? The point is you now have a data type that can properly use the built-in functions to more easily run calculations against proper timestamps that do have timezone sensitive calculations.

masklinn 7y ago
> Timezone data is separate information that if not included, is now missing from your data forever unless someone goes and backfills it in. Without the explicit timezone stored, your timestamp could technically be in any timezone as far as the database is concerned. As the article points out, confusingly, problematically, and despite its name, "TIMESTAMP WITH TIMEZONE" doesn't, in fact, store timezones. What it does is implicitly convert between UTC (the storage) and zoned timestamps (anything you give it, either explicitly zoned or — if non-zoned – assumed to be in the server's "timezone" setting). Thankfully TIMESTAMP WITHOUT TIMEZONE doesn't store timezones either (how weird would that be), what it does is discard timezone information entirely on input, storing only the timestamp as-is: if you give it '1932-11-02 10:34:05+08' it's going to store '1932-11-02 10:34:05' having just stripped out the timezone information without doing any adjustment whereas timestamptz will store '1932-11-02 02:34:05', without maintaining any offset either but having adjusted to UTC: # select '1932-11-02 10:34:05+08'::timestamp, '1932-11-02 10:34:05+08'::timestamptz; timestamp | timestamptz ---------------------+------------------------ 1932-11-02 10:34:05 | 1932-11-02 02:34:05+00 (1 row) Time: 0.466 ms Postgres has no built-in datatype to store an offset timestamp, let alone a properly zoned one. In fact, it has no datatype to store a timezone at all. Though it does provide pg_timezone_names.

by iod 7y ago

masklinn 7y ago

> Timezone data is separate information that if not included, is now missing from your data forever unless someone goes and backfills it in. Without the explicit timezone stored, your timestamp could technically be in any timezone as far as the database is concerned. As the article points out, confusingly, problematically, and despite its name, "TIMESTAMP WITH TIMEZONE" doesn't, in fact, store timezones. What it does is implicitly convert between UTC (the storage) and zoned timestamps (anything you give it, either explicitly zoned or — if non-zoned – assumed to be in the server's "timezone" setting). Thankfully TIMESTAMP WITHOUT TIMEZONE doesn't store timezones either (how weird would that be), what it does is discard timezone information entirely on input, storing only the timestamp as-is: if you give it '1932-11-02 10:34:05+08' it's going to store '1932-11-02 10:34:05' having just stripped out the timezone information without doing any adjustment whereas timestamptz will store '1932-11-02 02:34:05', without maintaining any offset either but having adjusted to UTC: # select '1932-11-02 10:34:05+08'::timestamp, '1932-11-02 10:34:05+08'::timestamptz; timestamp | timestamptz ---------------------+------------------------ 1932-11-02 10:34:05 | 1932-11-02 02:34:05+00 (1 row) Time: 0.466 ms Postgres has no built-in datatype to store an offset timestamp, let alone a properly zoned one. In fact, it has no datatype to store a timezone at all. Though it does provide pg_timezone_names.