3 ms·

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

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.
  - Fire-Dragon-DoL 5y ago
    That's exactly how we handle it: all the clients and servers involved are set to utc. I don't see it as a risk, I'd recommend doing that in any instance to save from confusion eventually, but thanks for clarifying in details. Any chance you have an example query (or instructions to follow) to trigger a bad case? I'd like to verify our setup I see your point, I need to evaluate the consequences of it
  - doctor_eval 5y ago
    Yep, the problem we had was all our tests ran fine on dev machines (local tz) but failed when deployed to production (UTC). We were doing some pretty complicated time calculations so it caused us a lot of trouble. I didn’t want to mandate UTC because the code should work deterministically regardless if the server time zone. But also, technically it’s a bug if you think you’re working in UTC, but you’re actually working in clock face time.

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.
- Fire-Dragon-DoL 5y ago
  That's exactly how we handle it: all the clients and servers involved are set to utc. I don't see it as a risk, I'd recommend doing that in any instance to save from confusion eventually, but thanks for clarifying in details. Any chance you have an example query (or instructions to follow) to trigger a bad case? I'd like to verify our setup I see your point, I need to evaluate the consequences of it
- doctor_eval 5y ago
  Yep, the problem we had was all our tests ran fine on dev machines (local tz) but failed when deployed to production (UTC). We were doing some pretty complicated time calculations so it caused us a lot of trouble. I didn’t want to mandate UTC because the code should work deterministically regardless if the server time zone. But also, technically it’s a bug if you think you’re working in UTC, but you’re actually working in clock face time.