6 ms·

Working with Postgres Types

65 points by alainchabat 5y ago

CRConrad 5y ago
Interesting read, not just this post but the whole series. I used to follow Udell's writings (though I can't recall now where they were published; must have been some magazine, I think, not just his own blog), but it seems he drifted out of my sight sometime in the last decade. Thanks for reminding me of him!
- Stratoscope 5y ago
  Agreed, I used to read Jon a lot back in the magazine days. Glad to see he is still at it! This page will answer some of your questions: https://blog.jonudell.net/about/ https://blog.jonudell.net/about/
- mhw 5y ago
  He used to write for Byte magazine.
doctor_eval 5y ago
I didn’t read the whole article (probably not aimed at me) but I just want to say that you should never, ever, ever use “timestamp without time zone” in PG because it is actually “clock face time” rather than “moment in time” and therefore the moment you try to do anything at all clever with time, you will start to question your own sanity. Well I did anyway. There is not enough room here to explain fully but “Timestamp *with* time zone” effectively means UTC time which is the closest equivalent in PG to Unix epoch time and almost certainly what most people want. It doesn’t actually store a time zone. You’ll thank me later :)
- agent327 5y ago
  Seconded. I now store timestamps as the number of microseconds since the epoch. It may not be as fancy as an actual timestamp type, but at least it works without surprises.
  - mekster 5y ago
    Except you have no idea when if you look at the database manually.
    - agent327 5y ago
      Luckily we provide a GUI so they can look at it in context.
  - doctor_eval 5y ago
    I went down this path for a time and I recommended it for MySQL some time ago. But actually epoch time and timestamptz are equivalent. You can specify the number of decimals in a timestamptz to get to microsecond precision if you like.
- kristiandupont 5y ago
  I've made a "linter" tool to enforce this: https://github.com/kristiandupont/schemalint https://github.com/kristiandupont/schemalint
  - rad_gruchalski 5y ago
    TIL: one can write a Postgres hook to enforce this in the database at 'create table' time. This would fit nicely in the ProcessUtility hook.
- quietbritishjim 5y ago
  > “Timestamp *with* time zone” ... doesn’t actually store a time zone. Thanks, I would never have guessed that!
- Fire-Dragon-DoL 5y ago
  Can you expand? I don't understand clock face time vs moment in time. If you have a link, I'm happy to go there. By reading the doc, it seems tome that timestamp without timezone should be used whenever the entirety of time operations are handled by the application. I'd argue that using timestamptz might actually cause bugs due to the conversion, often overlooked because everything is configured to use UTC.
  - cdaringe 5y ago
    - clock face time, lossy. non geo-aware/geo-normalized value. generally a scalar, or partial date time. value may not be robust if consumed outside of reporter’s locale - moment in time, geo-aware. scalar + geo-value, such as TZ or UTC offset I (mediocrely) wrote about this years ago: https://cdaringe.com/managing-application-dates https://cdaringe.com/managing-application-dates
  - patrickthebold 5y ago
    Let's say your application,for whatever reason, sends 3 different timestamps to pg: 2021-08-08T05:00:00+01 2021-08-08T06:00:00+02 2021-08-08T07:00:00+03 Do you want PG to store the same value or different values? If you answer 'same', you want with time zone. Now your answer might be: "make sure your application doesn't do that" in which case your probably fine either way but then you need to make sure you do that correctly.
    - Fire-Dragon-DoL 5y ago
      That is pretty clear when I choose the type. Timestamp WITHOUT timezone would imply that information is truncated. Now, my application works only in UTC so I don't want any transformation to happen and it will be explicit (and timezone stored in a separate field) in the application when this has to happen. If this is the case, is there any problem with using timestamp without timezone?
      - doctor_eval 5y ago
        If your application only works in UTC then you should use timestamptz. The problem is that a timestamp of “00:00 1 jan” may have a different UTC time depending on the timezone your client is in. So if your laptop is set to Melbourne time then the UTC time would be “14:00 31 Dec”. If your server is set to UTC then the code you deploy would treat it as 0000 UTC. The behaviour changes depending on environment. This happens because PG silently converts timestamp to timestamptz for many time operations, so the final result will depend on the environment TZ, which you don’t necessarily control or set. From the perspective of the SQL expression, the behaviour becomes nondeterministic. If you want to guarantee deterministic behaviour you should use timestamptz which does not require conversion. Timestamp is really only useful IMO when you have a feature such as “enable this setting from 2pm local time wherever the user is” Side note: As much as I love PG, the implicit casting between these two incompatible types is pretty baffling.
- quambene 5y ago
  Or alternatively, "timestamp without time zone default (now() at time zone 'utc')" is fine as well and does make the time zone explicit in the type definition.
  - doctor_eval 5y ago
    No, this just makes the default value the UTC time at the time a row is created, but it doesn’t help. Let’s say I create a row at “12:34 5 April UTC”. If I now change client time zones and run a query, the epoch time will change to “12:34 5 April EST”. That’s probably not what you want. The underlying issue is that most PG functions use timestamptz, and PG silently converts timestamp to timestamptz. This conversion uses the local TZ and can lead to nondeterministic behaviour from the perspective of the SQL expression. Put another way, timestamptz is perfectly stable, timestamp can be nondeterministic, use timestamptz unless you are absolutely sure you know what you are doing.
- shanipribadi 5y ago
  Both postgres timestamptz and timestamp are the same, they both store timestamp as UTC epoch time. Also timestamptz does not store the timezone given by the client, the only thing it stores is the UTC epoch time, so you'll lose the original timezone information. The only difference between the two is that with timestamptz postgres does a conversion between the text representation and the stored value based on a configured timezone (session, or database configured). The decision on which type to use IMO depends a lot more on the behaviour of the database driver of your programming language, and which one works better with the type. If you're not writing an application that uses a db driver, but interacting with pg over psql manually, timestamptz is preferrable because a lot of pg functions works better with timestamptz. If you use timestamp, you would need to be using explicit timezone conversion and be consistent to avoid weird double timezone conversion issues, which makes it far more verbose.

65 points by alainchabat 5y ago

Interesting read, not just this post but the whole series. I used to follow Udell's writings (though I can't recall now where they were published; must have been some magazine, I think, not just his own blog), but it seems he drifted out of my sight sometime in the last decade. Thanks for reminding me of him!

Stratoscope 5y ago
Agreed, I used to read Jon a lot back in the magazine days. Glad to see he is still at it! This page will answer some of your questions: https://blog.jonudell.net/about/ https://blog.jonudell.net/about/
mhw 5y ago
He used to write for Byte magazine.

doctor_eval 5y ago

I didn’t read the whole article (probably not aimed at me) but I just want to say that you should never, ever, ever use “timestamp without time zone” in PG because it is actually “clock face time” rather than “moment in time” and therefore the moment you try to do anything at all clever with time, you will start to question your own sanity. Well I did anyway. There is not enough room here to explain fully but “Timestamp *with* time zone” effectively means UTC time which is the closest equivalent in PG to Unix epoch time and almost certainly what most people want. It doesn’t actually store a time zone. You’ll thank me later :)

agent327 5y ago
Seconded. I now store timestamps as the number of microseconds since the epoch. It may not be as fancy as an actual timestamp type, but at least it works without surprises.
- mekster 5y ago
  Except you have no idea when if you look at the database manually.
  - agent327 5y ago
    Luckily we provide a GUI so they can look at it in context.
- doctor_eval 5y ago
  I went down this path for a time and I recommended it for MySQL some time ago. But actually epoch time and timestamptz are equivalent. You can specify the number of decimals in a timestamptz to get to microsecond precision if you like.
kristiandupont 5y ago
I've made a "linter" tool to enforce this: https://github.com/kristiandupont/schemalint https://github.com/kristiandupont/schemalint
- rad_gruchalski 5y ago
  TIL: one can write a Postgres hook to enforce this in the database at 'create table' time. This would fit nicely in the ProcessUtility hook.
quietbritishjim 5y ago
> “Timestamp *with* time zone” ... doesn’t actually store a time zone. Thanks, I would never have guessed that!
Fire-Dragon-DoL 5y ago
Can you expand? I don't understand clock face time vs moment in time. If you have a link, I'm happy to go there. By reading the doc, it seems tome that timestamp without timezone should be used whenever the entirety of time operations are handled by the application. I'd argue that using timestamptz might actually cause bugs due to the conversion, often overlooked because everything is configured to use UTC.
- cdaringe 5y ago
  - clock face time, lossy. non geo-aware/geo-normalized value. generally a scalar, or partial date time. value may not be robust if consumed outside of reporter’s locale - moment in time, geo-aware. scalar + geo-value, such as TZ or UTC offset I (mediocrely) wrote about this years ago: https://cdaringe.com/managing-application-dates https://cdaringe.com/managing-application-dates
- patrickthebold 5y ago
  Let's say your application,for whatever reason, sends 3 different timestamps to pg: 2021-08-08T05:00:00+01 2021-08-08T06:00:00+02 2021-08-08T07:00:00+03 Do you want PG to store the same value or different values? If you answer 'same', you want with time zone. Now your answer might be: "make sure your application doesn't do that" in which case your probably fine either way but then you need to make sure you do that correctly.
  - Fire-Dragon-DoL 5y ago
    That is pretty clear when I choose the type. Timestamp WITHOUT timezone would imply that information is truncated. Now, my application works only in UTC so I don't want any transformation to happen and it will be explicit (and timezone stored in a separate field) in the application when this has to happen. If this is the case, is there any problem with using timestamp without timezone?
    - doctor_eval 5y ago
      If your application only works in UTC then you should use timestamptz. The problem is that a timestamp of “00:00 1 jan” may have a different UTC time depending on the timezone your client is in. So if your laptop is set to Melbourne time then the UTC time would be “14:00 31 Dec”. If your server is set to UTC then the code you deploy would treat it as 0000 UTC. The behaviour changes depending on environment. This happens because PG silently converts timestamp to timestamptz for many time operations, so the final result will depend on the environment TZ, which you don’t necessarily control or set. From the perspective of the SQL expression, the behaviour becomes nondeterministic. If you want to guarantee deterministic behaviour you should use timestamptz which does not require conversion. Timestamp is really only useful IMO when you have a feature such as “enable this setting from 2pm local time wherever the user is” Side note: As much as I love PG, the implicit casting between these two incompatible types is pretty baffling.
quambene 5y ago
Or alternatively, "timestamp without time zone default (now() at time zone 'utc')" is fine as well and does make the time zone explicit in the type definition.
- doctor_eval 5y ago
  No, this just makes the default value the UTC time at the time a row is created, but it doesn’t help. Let’s say I create a row at “12:34 5 April UTC”. If I now change client time zones and run a query, the epoch time will change to “12:34 5 April EST”. That’s probably not what you want. The underlying issue is that most PG functions use timestamptz, and PG silently converts timestamp to timestamptz. This conversion uses the local TZ and can lead to nondeterministic behaviour from the perspective of the SQL expression. Put another way, timestamptz is perfectly stable, timestamp can be nondeterministic, use timestamptz unless you are absolutely sure you know what you are doing.
shanipribadi 5y ago
Both postgres timestamptz and timestamp are the same, they both store timestamp as UTC epoch time. Also timestamptz does not store the timezone given by the client, the only thing it stores is the UTC epoch time, so you'll lose the original timezone information. The only difference between the two is that with timestamptz postgres does a conversion between the text representation and the stored value based on a configured timezone (session, or database configured). The decision on which type to use IMO depends a lot more on the behaviour of the database driver of your programming language, and which one works better with the type. If you're not writing an application that uses a db driver, but interacting with pg over psql manually, timestamptz is preferrable because a lot of pg functions works better with timestamptz. If you use timestamp, you would need to be using explicit timezone conversion and be consistent to avoid weird double timezone conversion issues, which makes it far more verbose.