4 ms·
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 data
by 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 agoThat'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 agoYep, 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.