Postgresql – Format timezone to +-#### instead of +-##

date formatdatetimepostgresqltimezone

I am curious if there is a way to configure Postgres (9.1+) to format the TZ part of a "timestamp with time zone" as +-####, just like PHP's date.format method does using "O".

PHP Docs:

O | Difference to Greenwich time (GMT) in hours | Example: +0200

As requested an example:

create table example (time1 timestamp with time zone);
insert into example values ('2000-01-01 00:00:00+0200');
select * from example;
         time1          
------------------------
 1999-12-31 23:00:00+01
(1 rows)

-- How can I return the time as it was inserted (with it's timezone).
select time1 at time zone 'CEST' from example;
      timezone       
---------------------
 2000-01-01 00:00:00

-- This is the correct time, 
-- but I would like to attach the timezone to it
-- and return '2000-01-01 00:00:00+0200'

Thanks

Best Answer

The to_char function, as the first choice to format dates, is quite limited with time zones. According to Template Patterns for Date/Time Formatting in the documentation we have TZ and tz and they'll output a time zone name, not a offset against GMT.

You may use extract(timezone_hour from timestamptz_expression) to get the offset as a signed number of hours and extract(timezone_minutes from timestamptz_expression) to get the number of minutes that comes with it. This offset is documented as:

The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC. (Technically, PostgreSQL uses UT1 because leap seconds are not handled.)

Be aware that there are two conflicting conventions for the sign, east of Greenwich or west of Greenwich. You want to check that the sign is what you're expecting, otherwise invert it.

Another word of caution related to your edit:

  • How can I return the time as it was inserted (with it's timezone).

Postgres will never know the offset that the timestamp had when it was inserted, it's lost on insertion. What is returned to a SQL client is always the current offset depending on its current time zone. In order to keep the original time zone, you'd need to store it in an additional column.

Related Question