Tweeted twitter.com/#!/StackDBAs/status/515434688098627584
2 added 560 characters in body
source | link

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

I only found a way to return TZ names or Abbreviations, or the default +-##.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

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

I only found a way to return TZ names or Abbreviations, or the default +-##.

Thanks

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

1
source | link

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

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

I only found a way to return TZ names or Abbreviations, or the default +-##.

Thanks