Postgres to_timestamp setting timezone to +1

Question

I am using the to_timestamp function in Postgres to convert an integer value to a timestamp. When I do this, the timezone is set to +1, adding an hour to the stored time.

I am located in the UK so the current timezone offset should be +0, not +1.

Does anyone know how to stop this/set the timezone manually?

Example:

UPDATE my_table SET date_time_updated = to_timestamp(0) where id = 1;
SELECT date_time_updated FROM my_table WHERE id = 1; //Would expect midnight 01/01/1970

   date_time_updated
------------------------
 1970-01-01 01:00:00+01
(1 row)

Answer 1

Internally, the timestamp with time zone is time zone agnostic (weird as it sounds).

When PostgreSQL converts it to a string, it formats it according to the current setting of the timezone parameter.

So change your session's time zone with

SET timezone = 'Europe/London';

To change the default value that is used unless the client overrides it, change the parameter in postgresql.conf and reload PostgreSQL.