6 ms·

I don't get the rules on "timestamptz". Normally I would want to enforce the use of UTC for points in time (as opposed to user entry) across backend code and A

by dagss 7y ago

I don't get the rules on "timestamptz". Normally I would want to enforce the use of UTC for points in time (as opposed to user entry) across backend code and APIs. If the column name is "eventtime"... then it is (of course) UTC... I guess one could use timestamptz with a constraint saying that timezone is UTC, but is there a point? Timestamps in DBs should never be non-UTC...

eurg 7y ago
about "never not UTC" (but otherwise unrelated to the OP): There was an article recently, describing a very important, and also very common, "exception": If you want to schedule/synchronize real-world appointments/events, you need both the _local time_ and the political zone, like 2018-12-11 9am at Europe/London. The reason is that between time of entry and time of event, the defined timezone for that area may change. As a result, your previously translated appointment would not match up the local time. Which is bad, because humans, when meeting, or events, when being announced, announce the local time. Not UTC. And they stick to the local time.
- pmontra 7y ago
  I remember that post. It's not a remote chance. Every EU country will have to choose whether to stay forever in DST or forever in standard time, probably in 2021. Some of them will likely change time zone as a result.
- dagss 7y ago
  I know and that is why I said "as opposed to user entry"
- mixmastamyk 7y ago
  Interesting. Having trouble with one minor point. When a local datetime (in a known timezone) is converted to UTC, won't it know the proper DST setting for that datetime? Seems like it should still get the right answer, with the even smaller exception that a law might be passed to change DST in the meantime.
Bogdanp 7y ago
timestamptz does store the timestamps in UTC. The difference between timestamptz and timestamp is that the former understands that time value represents a particular instant in time, whereas timestamp does not, the value of a timestamp field is essentially opaque because those values can be in any timezone and the system has no way of knowing. [postgres] # create table test(ts timestamp); CREATE TABLE Time: 29.807 ms [postgres] # insert into test values('2019-05-03 17:51:00'); INSERT 0 1 Time: 6.637 ms [postgres] # select * from test; ts --------------------- 2019-05-03 17:51:00 (1 row) [postgres] # select ts at time zone 'utc' from test; timezone ------------------------ 2019-05-03 20:51:00+03 (1 row) Time: 17.506 ms [postgres] # select cast(ts as timestamptz) from test; ts ------------------------ 2019-05-03 17:51:00+03 (1 row) Time: 0.428 ms [postgres] # select cast(ts as timestamptz) at time zone 'utc' from test; timezone --------------------- 2019-05-03 14:51:00 (1 row) Time: 0.329 ms [postgres] # alter table test alter column ts type timestamptz; ALTER TABLE Time: 14.477 ms [postgres] # select * from test; ts ------------------------ 2019-05-03 17:51:00+03 (1 row) Time: 0.399 ms [postgres] # set timezone = 'America/Los_Angeles'; SET Time: 9.281 ms [postgres] # select * from test; ts ------------------------ 2019-05-03 07:51:00-07 (1 row) Time: 0.364 ms All that said, I think it's unfortunate that timestamptz stores values as UTC and I think it would be better for it to store the actual timezone for reasons others have already mentioned in this thread.
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.
kbenson 7y ago
My understanding is that timestamptz basically does what you want but better. It takes a timestamp in the timezone set in the client, and automatically converts it to UTC for storage, and any other client gets it in their local time based on the value their timezone is set to on their connection or session. It basically lets you fold all the logic of converting datetimes into UTC and back out of UTC to local time for display into the DB itself. Worst case, if you want UTC and to do it in the application logic, just set your connection timezone to UTC, and you'll be expected to provide and get back UTC times.
masklinn 7y ago
> I guess one could use timestamptz with a constraint saying that timezone is UTC, but is there a point? There's no need for constraints, that's exactly what timestamptz does. If it's provided a zoned datetime it'll convert that to UTC then store it, if provided a non-zoned datetime it'll assume that's in the local tz ("timezone" system setting) and performs the same conversion. timestamp, by comparison, will just discard any timezone information (if it's given a zoned timestamp it'll simply discard the timezone information and store the timestamp's value as-is). > Timestamps in DBs should never be non-UTC… That only works for past events. Storing future events in UTC is usually broken, because future events are normally relative to a specific timezone (e.g. a meeting at 6 in Tusla, or a party at 8 in Melbourne). If you store them in UTC and the timezone's offsets change your occurrence is now incorrect.

by dagss 7y ago

eurg 7y ago

about "never not UTC" (but otherwise unrelated to the OP): There was an article recently, describing a very important, and also very common, "exception": If you want to schedule/synchronize real-world appointments/events, you need both the _local time_ and the political zone, like 2018-12-11 9am at Europe/London. The reason is that between time of entry and time of event, the defined timezone for that area may change. As a result, your previously translated appointment would not match up the local time. Which is bad, because humans, when meeting, or events, when being announced, announce the local time. Not UTC. And they stick to the local time.

pmontra 7y ago
I remember that post. It's not a remote chance. Every EU country will have to choose whether to stay forever in DST or forever in standard time, probably in 2021. Some of them will likely change time zone as a result.
dagss 7y ago
I know and that is why I said "as opposed to user entry"
mixmastamyk 7y ago
Interesting. Having trouble with one minor point. When a local datetime (in a known timezone) is converted to UTC, won't it know the proper DST setting for that datetime? Seems like it should still get the right answer, with the even smaller exception that a law might be passed to change DST in the meantime.

Bogdanp 7y ago

timestamptz does store the timestamps in UTC. The difference between timestamptz and timestamp is that the former understands that time value represents a particular instant in time, whereas timestamp does not, the value of a timestamp field is essentially opaque because those values can be in any timezone and the system has no way of knowing. [postgres] # create table test(ts timestamp); CREATE TABLE Time: 29.807 ms [postgres] # insert into test values('2019-05-03 17:51:00'); INSERT 0 1 Time: 6.637 ms [postgres] # select * from test; ts --------------------- 2019-05-03 17:51:00 (1 row) [postgres] # select ts at time zone 'utc' from test; timezone ------------------------ 2019-05-03 20:51:00+03 (1 row) Time: 17.506 ms [postgres] # select cast(ts as timestamptz) from test; ts ------------------------ 2019-05-03 17:51:00+03 (1 row) Time: 0.428 ms [postgres] # select cast(ts as timestamptz) at time zone 'utc' from test; timezone --------------------- 2019-05-03 14:51:00 (1 row) Time: 0.329 ms [postgres] # alter table test alter column ts type timestamptz; ALTER TABLE Time: 14.477 ms [postgres] # select * from test; ts ------------------------ 2019-05-03 17:51:00+03 (1 row) Time: 0.399 ms [postgres] # set timezone = 'America/Los_Angeles'; SET Time: 9.281 ms [postgres] # select * from test; ts ------------------------ 2019-05-03 07:51:00-07 (1 row) Time: 0.364 ms All that said, I think it's unfortunate that timestamptz stores values as UTC and I think it would be better for it to store the actual timezone for reasons others have already mentioned in this thread.

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.

kbenson 7y ago

My understanding is that timestamptz basically does what you want but better. It takes a timestamp in the timezone set in the client, and automatically converts it to UTC for storage, and any other client gets it in their local time based on the value their timezone is set to on their connection or session. It basically lets you fold all the logic of converting datetimes into UTC and back out of UTC to local time for display into the DB itself. Worst case, if you want UTC and to do it in the application logic, just set your connection timezone to UTC, and you'll be expected to provide and get back UTC times.

masklinn 7y ago

> I guess one could use timestamptz with a constraint saying that timezone is UTC, but is there a point? There's no need for constraints, that's exactly what timestamptz does. If it's provided a zoned datetime it'll convert that to UTC then store it, if provided a non-zoned datetime it'll assume that's in the local tz ("timezone" system setting) and performs the same conversion. timestamp, by comparison, will just discard any timezone information (if it's given a zoned timestamp it'll simply discard the timezone information and store the timestamp's value as-is). > Timestamps in DBs should never be non-UTC… That only works for past events. Storing future events in UTC is usually broken, because future events are normally relative to a specific timezone (e.g. a meeting at 6 in Tusla, or a party at 8 in Melbourne). If you store them in UTC and the timezone's offsets change your occurrence is now incorrect.