5 ms·

I completely disagree with this advice: > However, when you come across timestamp, you can’t possibly know in what time zone the timestamp is just by looking a

by zzzeek 8y ago

I completely disagree with this advice: > However, when you come across timestamp, you can’t possibly know in what time zone the timestamp is just by looking at it. yes I do, it's in UTC, because I assume the application is written to correctly handle datetimes. > It is merely a wall time. Maybe it’s UTC, no, it's UTC. > or maybe the developer stored it in local time. right, maybe the application was not programmed correctly, that is always a possibility. But if they are using the timestamp with timezone datatype, it is 100% more likely they have made mistakes in doing so because it is much more confusing to have to fight against PostgreSQL's assumptions and implicit conversions, as the complexity of its behaviors and decisions using this datatype are described in this article. Does the application come up with the wrong answer if the server it runs on, or the webserver container, is mistakenly set to Eastern Timezone rather than UTC? Is the application ensuring that it knows the correct timezone of timestamps as they enter the application, and if so, why isn't it converting to UTC at that moment? Is the application otherwise receiving datetimes of arbitrary timezone, letting them move around business and display logic in their raw form, and waiting until they enter the database to be correctly normalized? that's too late. A correctly coded application does explicit normalization of datetime values the moment they enter the boundaries of the application, just like it would also convert encoded bytes into a unicode-aware strings up front, so that within the application there is no ambiguity, both when persisting and when handling.

grzm 8y ago
> "because I assume the application is written to correctly handle datetimes." > "it is 100% more likely they have made mistakes in doing so because it is much more confusing to have to fight against PostgreSQL's assumptions and implicit conversions" If you're assuming the application is correctly written to handle datetimes, you can safely assume it's also correctly using time zone aware date time data types, and using their database driver correctly, so there's no worry about PostgreSQL's assumptions or implicit conversions. Datetime handling is hard. However, most of the issues you're talking about here are independent of whether you're storing instants as timestamp or timestamptz in PostgreSQL. Persistence is only one layer in an application stack. As an aside, I haven't looked, but if someone hasn't already created a "Zoned Datetime" datatype extension for Postgres, it'd certainly be interesting to try.
- tofflos 8y ago
  You've nailed the problem. Too many layers in the application stack are tampering with time. As the author of the application I now have to understand every layer and all their nuances. All the good intentions of the database authors, driver authors and library authors are making it worse. Just leave my time alone and let me deal with it in the application.
colanderman 8y ago
Your assumptions are wrong. PostgreSQL's time functions provide exactly the semantics described in the article. If you pretend the two types work the other way around, you will get incorrect results when using e.g. `AT TIME ZONE`.
- welder 8y ago
  No, he's right. Before you downvote me, check my bio for my experience. Literally everything in zzzeek's comment I've personally seen causing live bugs in production code. Always use UTC without time zone and always convert to UTC as soon as possible in your application. > Your assumptions are wrong. zzzeek isn't assuming, he's speaking from experience. He wrote the SQLAlchemy library, so he knows what he's talking about.
  - jeltz 8y ago
    No, zzzeek is wrong in this case. The general advice from all PostgreSQL contributors and consultants I have heard talking on the subject is to use timestamptz by default and only use timestamp in a few select cases. And PostgreSQL itself uses timestamptz in its system views (the pg_stat_* views for example). Consider the following queries, both ran on a machine where everything is in UTC (I always configure my production machines in UTC): SELECT date(('2019-01-01T23:00:00Z'::timestamptz) AT TIME ZONE 'Europe/Berlin'); date ------------ 2019-01-02 (1 row) SELECT date(('2019-01-01T23:00:00Z'::timestamp) AT TIME ZONE 'Europe/Berlin'); date ------------ 2019-01-01 (1 row) The first one is likely what you want. When would you write a query like this? For example when you have a non-UTC business day and want to group sales by business day. Doing that kind of aggregation in the application is ugly, error prone and removes many of the benefits of having an SQL database.
    - zzzeek 8y ago
      The blog post and this comment are oriented towards direct commandline use of PostgreSQL, and to the extent you want to use the database as your primary front facing programming API, you need all the conveniences of opinionated types and conversion functions. But when the database is in its more traditional role as persistence for a large layer of business and presentation logic running in a front facing process, having a stricter model with less chance of implicit decisions made by the database itself is better. I think that's part of the disconnect here why some people are downmodding like crazy while others are agreeing. People are coming from two different perspectives, perhaps data scientists vs backend engineers.
welder 8y ago
Edit: I'm being downvoted, but I know more than you about timestamps. Check my bio before downvoting. zzzeek's comment is correct, he should be upvoted not down. > A correctly coded application does explicit normalization of datetime values the moment they enter the boundaries of the application, just like it would also convert encoded bytes into a unicode-aware strings up front, so that within the application there is no ambiguity, both when persisting and when handling. This is 100% true, about handling datetimes and unicode. I built a time tracking application which has proved this true many times. > I completely disagree with this advice Me too, the article op clearly doesn't know what he's talking about.
- mypalmike 8y ago
  > A correctly coded application does explicit normalization of datetime values the moment they enter the boundaries of the application... within the application there is no ambiguity > This is 100% true... I built a time tracking application which has proved this true many times. The potential for timezone rule changes mean that future events specified in local timezones are fundamentally ambiguous with regard to any absolute timeframe. Thus, normalization of such events to UTC can result in errors. Consider TicketMaster. They have a database of future events around the globe, sometimes years in advance. Imagine a band scheduled to play at 24-Jul-2020 at 8pm in Tokyo. Japan's government was considering reintroducing daylight savings time in 2020 so as to reduce heat stress on the 2020 Olympics athletes. In August 2018, they decided against it, but it could have happened. Let's pretend it did happen. If you had stored this band's gig in a database a few months ago (before the decision to make the change), your UTC-normalized timestamp would be 2020-07-24T11:00Z (UTC+9). But the gig is going to happen at 8pm local time regardless of the rule change. The actual event time is 2020-07-24T10:00Z (UTC+10), an hour earlier than originally scheduled. Normalizing to UTC makes sense in so many cases that it can seem to be a silver bullet for all cases. But there are definitely business cases that do not work well with this approach.
  - Izkata 8y ago
    You don't even need rare(ish) occurrences like rule changes to trigger this. Consider recurring events, like "every other Monday at 1pm", and what happens to them at the twice-yearly DST shifts.
djrobstep 8y ago
Why is this correct take being downvoted? Add consistency to your codebase by saving all timestamps in UTC. Edited: Storage size is the same.
- grzm 8y ago
  There's no difference in storage between timestamp and timestamptz in PostgreSQL. Both are 8 bytes.
- jeltz 8y ago
  I downnvoted because it is dangerous advice. If you use timestamp in PostgreSQL rather than timestamptz you will in run into even more time related bugs due to misusing PostgreSQL's data types, for example it will make AT TIME ZONE counterinutitve.
hathawsh 8y ago
Thank you for chiming in. In my SQLAlchemy+Postgres app I've chosen to store datetimes as timestamp without timezone columns where the time zone is always UTC. I'm quite happy with that choice. The last thing I want is for Postgres to start implicitly adjusting timestamps. By making the timestamps non-timezone-aware, I'm telling Postgres that if I ever want it to do any time zone conversion, I'll ask for the conversion explicitly, never implicitly. Explicit time zone conversion works quite nicely in Postgres. I want no magic.
alboy 8y ago
> yes I do, it's in UTC, because I assume the application is written to correctly handle datetimes. This seems to assume that only past events may be stored in a database. How would one schedule events at some clock-on-the-wall point in the future when either the exact applicable time zone is unknown or the mapping of a particular time zone to UTC isn't guaranteed to remain unchanged?
- zzzeek 8y ago
  You would still not be using "TIMESTAMP WITH TIME ZONE", and you would not use a TIMESTAMP of any kind - for a schedule, it's clearest if you have three columns, for date, scheduled time, and the desired timezone in which the event is scheduled. That removes any ambiguity that when one needs to deal with these times for date arithmetic, as in the extremely common case of sorting them, the developer is forced to coerce the three values into ad-hoc UTC or TZ-aware timestamp objects in the application in order to sort them. More realistically, for an application that is storing only near future dates within a limited geographic range, you can probably get away with using UTC for storage, as that's what we did at MLB anyway, but today I would likely not give this advice.
fphilipe 8y ago
> > However, when you come across timestamp, you can’t possibly know in what time zone the timestamp is just by looking at it. > yes I do, it's in UTC, because I assume the application is written to correctly handle datetimes. > > It is merely a wall time. Maybe it’s UTC, > no, it's UTC. No, you don't know that if you're looking at the database without knowledge of the app's conventions. I don't understand your argument about ambiguity and implicit conversions. Yes, if your session or DB is configured to something other than UTC, it will convert the timestamptz values in your output to that time zone's offset, but it'll still include the offset. Thus, a value "2019-04-01 00:00:00+00" that is output when using timestamptz with UTC (compared to "2019-04-01 00:00:00" when using timestamp), is output as "2019-03-31 19:00:00-05" when using EST. Both values represent the same moment in time, obviously, and are equivalent. Assuming your app and ORM convert everything to UTC, that shouldn't matter at all.

by zzzeek 8y ago

grzm 8y ago

> "because I assume the application is written to correctly handle datetimes." > "it is 100% more likely they have made mistakes in doing so because it is much more confusing to have to fight against PostgreSQL's assumptions and implicit conversions" If you're assuming the application is correctly written to handle datetimes, you can safely assume it's also correctly using time zone aware date time data types, and using their database driver correctly, so there's no worry about PostgreSQL's assumptions or implicit conversions. Datetime handling is hard. However, most of the issues you're talking about here are independent of whether you're storing instants as timestamp or timestamptz in PostgreSQL. Persistence is only one layer in an application stack. As an aside, I haven't looked, but if someone hasn't already created a "Zoned Datetime" datatype extension for Postgres, it'd certainly be interesting to try.

tofflos 8y ago
You've nailed the problem. Too many layers in the application stack are tampering with time. As the author of the application I now have to understand every layer and all their nuances. All the good intentions of the database authors, driver authors and library authors are making it worse. Just leave my time alone and let me deal with it in the application.

colanderman 8y ago

Your assumptions are wrong. PostgreSQL's time functions provide exactly the semantics described in the article. If you pretend the two types work the other way around, you will get incorrect results when using e.g. `AT TIME ZONE`.

welder 8y ago
No, he's right. Before you downvote me, check my bio for my experience. Literally everything in zzzeek's comment I've personally seen causing live bugs in production code. Always use UTC without time zone and always convert to UTC as soon as possible in your application. > Your assumptions are wrong. zzzeek isn't assuming, he's speaking from experience. He wrote the SQLAlchemy library, so he knows what he's talking about.
- jeltz 8y ago
  No, zzzeek is wrong in this case. The general advice from all PostgreSQL contributors and consultants I have heard talking on the subject is to use timestamptz by default and only use timestamp in a few select cases. And PostgreSQL itself uses timestamptz in its system views (the pg_stat_* views for example). Consider the following queries, both ran on a machine where everything is in UTC (I always configure my production machines in UTC): SELECT date(('2019-01-01T23:00:00Z'::timestamptz) AT TIME ZONE 'Europe/Berlin'); date ------------ 2019-01-02 (1 row) SELECT date(('2019-01-01T23:00:00Z'::timestamp) AT TIME ZONE 'Europe/Berlin'); date ------------ 2019-01-01 (1 row) The first one is likely what you want. When would you write a query like this? For example when you have a non-UTC business day and want to group sales by business day. Doing that kind of aggregation in the application is ugly, error prone and removes many of the benefits of having an SQL database.
  - zzzeek 8y ago
    The blog post and this comment are oriented towards direct commandline use of PostgreSQL, and to the extent you want to use the database as your primary front facing programming API, you need all the conveniences of opinionated types and conversion functions. But when the database is in its more traditional role as persistence for a large layer of business and presentation logic running in a front facing process, having a stricter model with less chance of implicit decisions made by the database itself is better. I think that's part of the disconnect here why some people are downmodding like crazy while others are agreeing. People are coming from two different perspectives, perhaps data scientists vs backend engineers.

welder 8y ago

Edit: I'm being downvoted, but I know more than you about timestamps. Check my bio before downvoting. zzzeek's comment is correct, he should be upvoted not down. > A correctly coded application does explicit normalization of datetime values the moment they enter the boundaries of the application, just like it would also convert encoded bytes into a unicode-aware strings up front, so that within the application there is no ambiguity, both when persisting and when handling. This is 100% true, about handling datetimes and unicode. I built a time tracking application which has proved this true many times. > I completely disagree with this advice Me too, the article op clearly doesn't know what he's talking about.

mypalmike 8y ago
> A correctly coded application does explicit normalization of datetime values the moment they enter the boundaries of the application... within the application there is no ambiguity > This is 100% true... I built a time tracking application which has proved this true many times. The potential for timezone rule changes mean that future events specified in local timezones are fundamentally ambiguous with regard to any absolute timeframe. Thus, normalization of such events to UTC can result in errors. Consider TicketMaster. They have a database of future events around the globe, sometimes years in advance. Imagine a band scheduled to play at 24-Jul-2020 at 8pm in Tokyo. Japan's government was considering reintroducing daylight savings time in 2020 so as to reduce heat stress on the 2020 Olympics athletes. In August 2018, they decided against it, but it could have happened. Let's pretend it did happen. If you had stored this band's gig in a database a few months ago (before the decision to make the change), your UTC-normalized timestamp would be 2020-07-24T11:00Z (UTC+9). But the gig is going to happen at 8pm local time regardless of the rule change. The actual event time is 2020-07-24T10:00Z (UTC+10), an hour earlier than originally scheduled. Normalizing to UTC makes sense in so many cases that it can seem to be a silver bullet for all cases. But there are definitely business cases that do not work well with this approach.
- Izkata 8y ago
  You don't even need rare(ish) occurrences like rule changes to trigger this. Consider recurring events, like "every other Monday at 1pm", and what happens to them at the twice-yearly DST shifts.

djrobstep 8y ago

Why is this correct take being downvoted? Add consistency to your codebase by saving all timestamps in UTC. Edited: Storage size is the same.

grzm 8y ago
There's no difference in storage between timestamp and timestamptz in PostgreSQL. Both are 8 bytes.
jeltz 8y ago
I downnvoted because it is dangerous advice. If you use timestamp in PostgreSQL rather than timestamptz you will in run into even more time related bugs due to misusing PostgreSQL's data types, for example it will make AT TIME ZONE counterinutitve.

hathawsh 8y ago

Thank you for chiming in. In my SQLAlchemy+Postgres app I've chosen to store datetimes as timestamp without timezone columns where the time zone is always UTC. I'm quite happy with that choice. The last thing I want is for Postgres to start implicitly adjusting timestamps. By making the timestamps non-timezone-aware, I'm telling Postgres that if I ever want it to do any time zone conversion, I'll ask for the conversion explicitly, never implicitly. Explicit time zone conversion works quite nicely in Postgres. I want no magic.

alboy 8y ago

> yes I do, it's in UTC, because I assume the application is written to correctly handle datetimes. This seems to assume that only past events may be stored in a database. How would one schedule events at some clock-on-the-wall point in the future when either the exact applicable time zone is unknown or the mapping of a particular time zone to UTC isn't guaranteed to remain unchanged?

zzzeek 8y ago
You would still not be using "TIMESTAMP WITH TIME ZONE", and you would not use a TIMESTAMP of any kind - for a schedule, it's clearest if you have three columns, for date, scheduled time, and the desired timezone in which the event is scheduled. That removes any ambiguity that when one needs to deal with these times for date arithmetic, as in the extremely common case of sorting them, the developer is forced to coerce the three values into ad-hoc UTC or TZ-aware timestamp objects in the application in order to sort them. More realistically, for an application that is storing only near future dates within a limited geographic range, you can probably get away with using UTC for storage, as that's what we did at MLB anyway, but today I would likely not give this advice.

fphilipe 8y ago

> > However, when you come across timestamp, you can’t possibly know in what time zone the timestamp is just by looking at it. > yes I do, it's in UTC, because I assume the application is written to correctly handle datetimes. > > It is merely a wall time. Maybe it’s UTC, > no, it's UTC. No, you don't know that if you're looking at the database without knowledge of the app's conventions. I don't understand your argument about ambiguity and implicit conversions. Yes, if your session or DB is configured to something other than UTC, it will convert the timestamptz values in your output to that time zone's offset, but it'll still include the offset. Thus, a value "2019-04-01 00:00:00+00" that is output when using timestamptz with UTC (compared to "2019-04-01 00:00:00" when using timestamp), is output as "2019-03-31 19:00:00-05" when using EST. Both values represent the same moment in time, obviously, and are equivalent. Assuming your app and ORM convert everything to UTC, that shouldn't matter at all.