Menu
My web app stores all timestamps in UTC without time zones. I have a shell/psql script that returns a list of recent logins, and I want that script to display login times in the server's local time zone (which may vary depending on where the server is and with daylight savings).
To get an interval representing the difference between my database server's time zone and UTC, I'm currently using this hack:
SELECT age(now() at time zone 'UTC', now()); That works, but is there a more straightforward way?
There's a server configuration parameter called "timezone" that returns a valid timezone string, but I don't think it's possible to access those parameters in a query. (I guess that's a separate question, but an answer to it would resolve the time zone issue.)
487
The zone offset can be Z for UTC or it can be a value "+" or "-" from UTC. For example, the value 08:00-08:00 represents 8:00 AM in a time zone 8 hours behind UTC, which is the equivalent of 16:00Z (8:00 plus eight hours). The value 08:00+08:00 represents the opposite increment, or midnight (08:00 minus eight hours).
If you have a timestamp without time zone column and you're storing timestamps as UTC, you need to tell PostgreSQL that, and then tell it to convert it to your local time zone.
SELECT current_setting('TIMEZONE') This can be used in a query, however, this does not give a numerical difference.
Your solution is fine.
99
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
