- what will be the effect of this on fields of type "timestamp with timezone"?
They're converted from their internal UTC representation to UTC-5 at the moment. They'll be converted using UTC+4 after. So applications will see a 9-hour time shift if they're ignoring the time zone. If they respect the time zone reported in the timestamp then there will be no effect.
One notable exception to this is where the timestamp is truncated to a date. The timestamp may fall on a different day in one time zone to in another time zone. Truncation of a timestamp with time zone to a date discards time zone information. So be cautious with date_trunc
, casts from timestamp with time zone
to date
, calls to extract
or date_part
, etc.
- what will be the effect of this on fields of type "timestamp without timezone"?
Nothing.
- what will happen to replication if we change the slave timezone?
Nothing. Replication is block level. It doesn't care about the timezone setting in the slightest.
This is just the same as changing TimeZone
in the master with a SET TimeZone
at the SQL level, or in the config file. So you can just try it with a standalone DB.
e.g.
test=> SHOW TimeZone;
TimeZone
----------------
Australia/West
(1 row)
test=> CREATE TABLE tztz(tstz timestamptz, ts timestamp);
CREATE TABLE
test=> INSERT INTO tztz(tstz, ts) values (current_timestamp, current_timestamp);
INSERT 0 1
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
tstz | tstz | ts | ts
-------------------------------+------------+----------------------------+------------
2015-08-02 20:48:56.664932+08 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)
test=> SET TimeZone = UTC;
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
tstz | tstz | ts | ts
-------------------------------+------------+----------------------------+------------
2015-08-02 12:48:56.664932+00 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)
test=> SET TimeZone = 'UTC-4';
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
tstz | tstz | ts | ts
-------------------------------+------------+----------------------------+------------
2015-08-02 16:48:56.664932+04 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)
Note that in both cases the timestamp is the same, because 16:48 at UTC+04 is the same as 12:48 at UTC or 20:48 at UTC+8 (Australia/West).
However, when the timezone shift causes the timestamp to be in the prior or next day in that time zone...:
test=> SET TimeZone = 'UTC-12';
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
tstz | tstz | ts | ts
-------------------------------+------------+----------------------------+------------
2015-08-03 00:48:56.664932+12 | 2015-08-03 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)
it's all fine with the timestamps still, but the timestamp with time zone
truncated to date
has moved to a different day.
If you're confused by the offsets seeming backwards that's because PostgreSQL respects the POSIX standard for time zone offsets which are, infuriatingly, backwards to the offsets most people use day to day.
Instead of using the time zone 'UTC-7' it's using 'UTC+7', which is 14 hours different.
It depends whether +/- means east or west of Greenwich, and it turns out both conventions exist. PostgreSQL doc warns about that:
http://www.postgresql.org/docs/current/static/datatype-datetime.html
Excerpt from 8.5.3. Time Zones (but you really want to read the whole paragraph):
Another issue to keep in mind is that in POSIX time zone names,
positive offsets are used for locations west of Greenwich. Everywhere
else, PostgreSQL follows the ISO-8601 convention that positive
timezone offsets are east of Greenwich.
Using ISO 8601 time zone notation also yields the opposite from the expected result:
In the same page, the accepted formats for time zone literals are listed, here's a condensed version:
PostgreSQL allows you to specify time zones in three different forms:
A full time zone name, for example America/New_York [...]
A time zone abbreviation, for example PST[...]
In addition to the timezone names and abbreviations, PostgreSQL will accept POSIX-style time zone specifications of the form STDoffset
or STDoffsetDST, where STD is a zone abbreviation, offset is a numeric
offset in hours west from UTC, and DST is an optional daylight-savings
zone abbreviation,[...]
Note that it never pretends to accept an ISO 8601 time zone designator. And as you found out with -0700
, it doesn't. You should just use one of the forms above.
Also beware of (false) conclusions that because the parser doesn't reject a time zone, it's valid:
One should be wary that the POSIX-style time zone feature can lead to
silently accepting bogus input, since there is no check on the
reasonableness of the zone abbreviations
Best Answer
A simple cast from
timestamp
totimestamptz
assumes your current time zone. If you want the cast to assume UTC (or any other time zone) you have to use theAT TIME ZONE
construct again:You may be missing that the
AT TIME ZONE
has two different use cases. It can transposetimestamptz
totimestamp
and vice versa. Related answer with detailed explanation:Be aware that your statement does not make sense:
The text representation of
timestamptz
is always displayed as local time according to your current time zone setting, extended with an offset to UTC. The literal you display has no offset, so it is atimestamp
literal, nottimestamptz
. Basics here: