I have PostgreSQL database table with timestamp with time zone
field called published_date
.
I use psql -U postgres dbname
command for access my database.
When I try to update published_date field value by command:
UPDATE mytable SET published_date='PUBLISHED_DATE' WHERE id=3;
I get following results for corresponding PUBLISHED_DATEs:
SOURCE -> RESULT
2013-01-16 20:00:00 -> 2013-01-16 20:00:00+00 (WELL)
2013-02-16 20:00:00 -> 2013-02-16 20:00:00+00 (WELL)
2013-03-16 20:00:00 -> 2013-03-16 20:00:00+00 (WELL)
2013-04-16 20:00:00 -> 2013-04-16 20:00:00+01 (STRANGE!)
2013-05-16 20:00:00 -> 2013-05-16 20:00:00+01 (STRANGE!)
2013-06-16 20:00:00 -> 2013-06-16 20:00:00+01 (STRANGE!)
2013-07-16 20:00:00 -> 2013-07-16 20:00:00+01 (STRANGE!)
2013-08-16 20:00:00 -> 2013-08-16 20:00:00+01 (STRANGE!)
2013-09-16 20:00:00 -> 2013-09-16 20:00:00+01 (STRANGE!)
2013-10-16 20:00:00 -> 2013-10-16 20:00:00+01 (STRANGE!)
2013-11-16 20:00:00 -> 2013-11-16 20:00:00+00 (WELL)
2013-12-16 20:00:00 -> 2013-12-16 20:00:00+00 (WELL)
There is UTC timezone in database:
=> SHOW timezone;
TimeZone
----------
UTC
(1 row)
and system:
$ date
Tue Dec 17 00:14:12 GMT 2013
PostgreSQL version is 9.2.4
Same version of postgres compiled with same flags installed on another machine working pretty! Very strange.
That's the normal behaviour, PostgreSQL tries to accomodate the user when outputting a timestamp with timezone (which internally is indeed stored as UTC). I assume you're in the UK, what you see is it changing between summer and winter time. From the manual, emphasis added: