3 ms·

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 timesta

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.
    - Fire-Dragon-DoL 5y ago
      I'm not sure if what you are describing is correct. If the client sends utc (as in a web browser), the server would transform it to UTC and then work with UTC in any context. So the database will always and only see UTC values coming in and out. Then, the application will transform the time into whatever timezone is needed. A point could be made in ensuring that postgres timezone should be set to UTC. But if this is the situation, even with the conversion nothing would happen
      - doctor_eval 5y ago
        I'm definitely correct. The client I'm referring to is the PG client, not the browser. The browser connects to some HTTP server which is a client of the PG database. Assuming you send the time in epoch time format, you would use: select to_timestamp(1628506048); to convert to a `timestamp with time zone` (yes that is correct). If you insert this value into a database using `timestamp without time zone` then the database forgets that the time is UTC and will use whatever TZ is set on the client that happens to run the query. If you can 100% guarantee that all your devices - developer machines, test machines, VMs, production, the whole works, if you can absolutely 100% guarantee that they are all set to UTC then timestamp and timestamptz are largely the same. But why take the risk? I can tell you from experience that learning this the hard way is really hard.

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.
  - Fire-Dragon-DoL 5y ago
    I'm not sure if what you are describing is correct. If the client sends utc (as in a web browser), the server would transform it to UTC and then work with UTC in any context. So the database will always and only see UTC values coming in and out. Then, the application will transform the time into whatever timezone is needed. A point could be made in ensuring that postgres timezone should be set to UTC. But if this is the situation, even with the conversion nothing would happen
    - doctor_eval 5y ago
      I'm definitely correct. The client I'm referring to is the PG client, not the browser. The browser connects to some HTTP server which is a client of the PG database. Assuming you send the time in epoch time format, you would use: select to_timestamp(1628506048); to convert to a `timestamp with time zone` (yes that is correct). If you insert this value into a database using `timestamp without time zone` then the database forgets that the time is UTC and will use whatever TZ is set on the client that happens to run the query. If you can 100% guarantee that all your devices - developer machines, test machines, VMs, production, the whole works, if you can absolutely 100% guarantee that they are all set to UTC then timestamp and timestamptz are largely the same. But why take the risk? I can tell you from experience that learning this the hard way is really hard.