Search code examples
postgresqltypestimezonetimestamp

Transform timestamp to local time for a given timezone during 'COPY .. TO ..'

I have a log table in a PostgreSQL database with an event column of type timestamp without time zone.

Now I have a bash script, which creates a CSV file from the log database:

...
psql .. -c "COPY (SELECT event, ... FROM logtable order by event desc) TO STDOUT WITH CSV" logdb > log.csv
...

This is executed on the cloud server on which the DB is hosted and therefore, the timestamp strings in log.csv are in local time of the timezone of the server.

However, I like to have the timestamp strings to represent the time of my own time zone. So I shall be able to let psql transform the timestamp -> string to a given timezone. How can I achieve this?

Solution

  • Use timestamptz instead of timestamp whenever working with multiple times zones. Would avoid the problem completely.
    See:

    You can use the AT TIME ZONE construct like @NuLo suggests, it may even work, but not exactly as described.

    AT TIME ZONE converts the type timestamp (timestamp without time zone) to timestamptz (timestamp with time zone) and vice versa. The text representation of a timestamptz value depends on the current setting of the time zone in the session in which you run the command. These two timestamptz values are 100 % identical (denote the same point in time):

    '2015-09-02 15:55:00+02'::timestamptz
'2015-09-02 14:55:00+01'::timestamptz

    But the text representation is not. The display is for different time zones. If you take this string literal and feed it to a timestamp type, the time zone part is just ignored and you end up with different values. Hence, if you run your COPY statement in a session with the same time zone setting as your original timestamp values are for, the suggested operation happens to work.

    The clean way, however, is to produce correct timestamp values to begin with by applying AT TIME ZONE twice:

    SELECT event AT TIME ZONE 'my_target_tz' AT TIME ZONE 'my_source_tz', ...
FROM   logtable
ORDER  BY event desc;

    'my_target_tz' is "your own time zone" and 'my_source_tz' the time zone of the of the cloud server in the example. To make sure that DST is respected use time zone names, not time zone abbreviations. The documentation:

    A time zone abbreviation, for example PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition-date rules as well.

    Related:

    Or, much better yet, use timestamptz everywhere and it works correctly automatically.