Thread: now() AT TIME ZONE interval '-5 hours' returns type interval???

I am attempting to work with time zones.  All of my timestamp fields are

timestamp with time zone

The problem I am having is when I attempt to convert a timestamp with a time
zone to another time zone.  For example, the statement

SELECT now() AT TIME ZONE interval '-5 hours'

returns type interval not timestamp with//out time zone.

On my website I have people select the timezone they are in..

That way I can theoritically perform an insert along the lines of

timestamp '2003-12-03 19:34' AT TIME ZONE interval '-5 hours'

replacing -5 with whatever timezone they specify.

I am hoping to be able to do the reverse,

SELECT stamp AT TIME ZONE interval '-5 hours' FROM table;

However it returns type interval!!!  And casting it to type timestamp does not
work.

Help!

Josh.

In addition, if I use

SELECT now() AT TIME ZONE 'EST' It remains as type timezone...

This looks like a bug in Postgres..  since the manual mentions that you can
'EST' and interval '-5 hours' interchangeably.

Anyone agree//disagree before I post a bug report? I'm rather new to postgres
implementation of a timestamp so I'd rather get some feedback (hoping I'm
wrong ) before I submit a bgu report..

Josh.

Joshua Moore-Oliva <josh@chatgris.com> writes:
> The problem I am having is when I attempt to convert a timestamp with a time
> zone to another time zone.  For example, the statement
> SELECT now() AT TIME ZONE interval '-5 hours'
> returns type interval not timestamp with//out time zone.

This appears to be a simple typo in the pg_proc entry for the function.
The underlying C code thinks it is returning a timestamp without time
zone, but that's not what the pg_proc entry has.

You can fix this in an existing database by doing

UPDATE pg_proc SET prorettype = 1114 WHERE prosrc = 'timestamptz_izone';


            regards, tom lane

On Thursday 13 March 2003 8:10 pm, Tom Lane wrote:
> UPDATE pg_proc SET prorettype = 1114 WHERE prosrc = 'timestamptz_izone';

This syntax looked useful to me so I tried the update you suggested
(fortunately on an old test system running 7.2.3) and now it only returns
2000-01-01 00:00:00. Always.

steve=# select now() at time zone interval '6 hours';
      timezone
---------------------
 2000-01-01 00:00:00
(1 row)

steve=# select now() at time zone interval '-5 hours';
      timezone
---------------------
 2000-01-01 00:00:00
(1 row)

steve=# select now() at time zone interval '4.5 hours';
      timezone
---------------------
 2000-01-01 00:00:00
(1 row)

steve=# select now() at time zone interval '0';
      timezone
---------------------
 2000-01-01 00:00:00
(1 row)

Cheers,
Steve

Steve Crawford <scrawford@pinpointresearch.com> writes:
> On Thursday 13 March 2003 8:10 pm, Tom Lane wrote:
>> UPDATE pg_proc SET prorettype = 1114 WHERE prosrc = 'timestamptz_izone';

> This syntax looked useful to me so I tried the update you suggested
> (fortunately on an old test system running 7.2.3) and now it only returns
> 2000-01-01 00:00:00. Always.

Sorry, I should have made it clear that that patch was only for 7.3.*.
7.2 has a much more primitive AT TIME ZONE facility --- IIRC, the
function in question returns TEXT in 7.2.  (The origin of this problem
seems to be that Tom Lockhart left before he'd finished debugging his
latest round of date-and-time improvements.)

If you need to fix your test system, change the value back to 25.

            regards, tom lane