Does anyone know what this actually does?
There appears to be two different timezones, a Session timezone and a Database time zone. The former is clear, it causes timezones with timestamp to be converted from a text representation in psql to UTC.
But what does the Database time zone do?
It can be changed with
ALTER DATABASE database_name SET TIMEZONE=’zone’;
Is Database Timezone just some sort of default for the Session Timezone? Or does it affect how timestamps are stored? My understanding is that the psql session timezone defaults to the client computer timezone.
There is also the question of the 99.9% of usages that do not use psql. Say JDBC. When and how are offsets added. But that is not this question.
Timezones are tricky, and never well documented.
3
Answers
This is covered in the documentation. In particular:
Admittedly mentioning UTC is a bit misleading, I’d prefer to say that a
timestamptz
represents an instant, an exact fixed point in time, without regard to calendar or location (timezone). It’s just an offset since an epoch. Very much like aDate
in Java or JavaScript, or better: like anInstant
(Java, JavaScript).So the
timezone
setting affects conversions of timestamps from and to text, both via casting and the viato_char
/to_timestamp
function calls, in SQL execution.It does not affect storage.
Looking at chapter 20, in particular Setting Parameters, shows that there are many more than just two places to set the
timezone
configuration variable.The documentation of the
SET
command details how this may be limited to the current session, the current transaction, or the currently executing function.A client would have to explicitly set this to use the client computer’s system timezone for the session. JDBC does this, for example.
Manual ALTER DATABASE part.
meaning if the new connection not explicitly override the value, then the new value will become session default.
Like
ALTER DATABASE test15 SET TIMEZONE='Singapore';
if you are still in session, you will still have the previous timezone value, if you quit the session, reenter agagin, then the new TIMEZONE is Singapore.return.
The context is user level, which means any connection can use set command change it.
So when query the interval value like
timestamptz column > now().
the return values will become different for connections in differenent timezone.if you not explicitly set it, the default will be database level timezone parameter value.
psql is same as JDBC, both are client. they can change timezone use set command, if the connection is superuser or owner, then they can change database default, which means other connection will follow the new default.
However each connection can still use set command to change the timezone paramter.
If your timestamp from string literal,then no. if your timezone from timestamptz then yes. First query result is the same, second is not.
(another post answering exactly the questions from the OP, taken from the comments of my other answer)
It’s a configuration setting that is applied when starting a fresh session (connection), overrides the server defaults, and constitutes the default for the rest of the session.
Yes.
No.
timestamp with timezone
represents an instant, a fixed point in time, without regard to calendar or location (timezone). It’s just an offset since an epoch.No: only when your client explicitly sets it – like JDBC does.
I’ve not used it myself and don’t know how it does conversion of raw serialisation to java objects. But I would presume that when you query
timestamptz
as aDate
, the timezone setting doesn’t matter. When you querytimestamptz
as aString
, the timezone would apply ("When a timestamp with time zone value is output, it is always converted from UTC to the currenttimezone
zone, and displayed as local time in that zone.").When you query
timestamp
as aString
, it would not apply. And you shouldn’t query atimestamp
as aDate
(JDBC might assume UTC? Postgres does not!)See How do you view the configuration settings of a PostgreSQL database? or Query for all the Postgres configuration parameters‘ current values?