UTC is different on 2 servers for same database

Question

I have one server which is on UTC time and one which is on EST. Now I dumped the database from the UTC one and imported it to the EST one. As timestamps are always saved as UTC I cannot find a reason the reason I get two different results for the same query:

select reported_on at time zone 'UTC', temperature from data order by reported_on desc;

UTC Server:

 temperature |                device                |          timezone          
-------------+--------------------------------------+----------------------------
        17.2 | ------------------------------------ | 2020-05-05 12:13:16.256+00

EST Server:

 temperature |                device                |          timezone          
-------------+--------------------------------------+----------------------------
        17.2 | ------------------------------------ | 2020-05-05 14:13:16.256+02

What am I missing here? How can I query the data without the timezones, I need the UTC time, not the +02 time? How can I achieve this?

Edit: I added the lines through nodejs:

INSERT INTO data(device, reported_on, temperature, humidity) VALUES($1, to_timestamp(' + Date.now() /1000.0 + '), $2, $3) RETURNING *

The Typ of the column is: reported_on TIMESTAMP,

Update: Altering the timezone fixed the issue!

ALTER DATABASE sensors SET timezone TO 'UTC';
SELECT pg_reload_conf();

Answer 1

This is about your local database configuration. I created a dummy database locally and the result was this:

test=# CREATE TABLE timestamptest (timezone TIMESTAMPTZ);`

Showed my timezone pattern:

test=# SHOW TIMEZONE;
 TimeZone 
----------
 UTC
(1 row)

And inserted some values inside:

test=# SELECT * FROM timestamptest;
           timezone            
-------------------------------
 2020-05-05 15:26:27.377549+00
 2020-05-05 15:28:14.014597+00
(2 rows)

Now, I changed the local timezone variable:

test=# SET TIME ZONE 'America/Montreal';
SET

And selected the info again:

test=# INSERT INTO timestamptest VALUES (now());
INSERT 0 1
test=# SELECT * FROM timestamptest ;
           timezone            
-------------------------------
 2020-05-05 11:26:27.377549-04
 2020-05-05 11:28:14.014597-04
(2 rows)

And my timezone is different because the SET command:

test=# SHOW timezone;
     TimeZone     
------------------
 America/Montreal
(1 row)

You can change your local configuration and, about your selects showed in your question, the import seems to be correct, just the local timestamp configuration needed to be set equals from one to another.

Note that -04 on the end of the timestamp shows that your time has changed 4 hours in relation of -00 originally. Just a formatting ouptut.

More information here: here on postgresql docs

Answer 2

The statement "timestamps are always saved as UTC" is incorrect. Timestamp without time (timestamp) is stored with local time, timestamp with time zone (timestamptz) is always stored in UTC. From the documentation:

timestamp without time zone, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

Additional be very careful using 'EST', or any of the other timezone abbreviations, instead use the full timezone name. The abbreviations indicate a fixed UTC offset (i.e. EST is always UTC-5, and EDT is always UTC-4) and do not recognize Daylight Savings Time (DST). On the other hand the full names (i.e. are US/Central' or 'America/Montreal' or any other full timezone name) are DST aware and adjust UTC offset accordingly.

Unlike the abbreviations shown in pg_timezone_abbrevs, many of these names imply a set of daylight-savings transition date rules.

You can observe this with:

select * from pg_timezone_abbrevs where abbrev in ('EDT', 'EST')
select * from pg_timezone_names  where name in ('US/Eastern','America/New_York','America/Montreal')

Answer 3

The type of the column is TIMESTAMP*"

I guess there's your problem. You'll want to use TIMESTAMP WITH TIME ZONE instead. Alternatively, do not use AT TIME ZONE 'UTC' in your SELECT statement, so that you just get back the same timestamp that was stored, regardless of timezone.

What is happening in SELECT reported_on AT TIME ZONE 'UTC' is that the stored date is converted from the database's local timezone to UTC. In your second database with a different local timezone, that's a different conversion, leading to the offset in the result.