Postgresql – Filter column by records matching a timezone in Postgres

Here are a couple of approaches:

I've simplified your query, as you shouldn't need the TRUNC(), nor the CAST().

SELECT to_timestamp(1395036000) AT TIME ZONE 'UTC';

SELECT timestamp '1970-01-01 00:00:00' + interval '1395036000 second';

For reference, more information can be found at the following links:

• First query

• Second query

1. 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.

2. what will be the effect of this on fields of type "timestamp without timezone"?

Nothing.

3. 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.