Thread: Surprise AT TIME ZONE behaviour (buglet?)?

test=# select version();
                            version
-------------------------------------------------------------
  PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
test=# create table tz (t timestamp, tz text);
CREATE
test=# insert into tz values (now(), 'GMT');
INSERT 340574 1
test=# insert into tz values (now(), 'PST');
INSERT 340575 1
test=# select t at time zone tz from tz;
         timezone
------------------------
  2002-02-04 18:43:55+00
  2002-02-04 10:44:00-08
(2 rows)


Can I first say that I am dead-impressed that this select statement works!!
Well done and a million thanks to whoever implemented it such that the argument
to AT TIME ZONE is a SQL statement.

Also thanks to the authors of "Practical PostgreSQL" (O'Reilly 2002) for
pointing this feature out to me in the first place.  It is a fine book, even if
they do get the definition of PST wrong (John and Joshua: it is GMT-8, cf. above).

Two questions:

1. Am I the only one who is surprised by the column name ('timezone' instead of
't')?  A parser buglet (assuming noise after column name is AS)?

2. Is this really supposed to work?  More precisely: can I count on it if future
releases?  The documentation
(http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-select.html) does not
mention it....


Allan.

...
> Well done and a million thanks to whoever implemented it such that the argument
> to AT TIME ZONE is a SQL statement.

Thanks. Positive feedback is always welcome ;)

> 1. Am I the only one who is surprised by the column name ('timezone' instead of
> 't')?  A parser buglet (assuming noise after column name is AS)?

I'm not sure I'd characterize this as a bug(let), though since it led to
unexpected behavior maybe it is by definition?

Anyway, what happens under the covers in the initial phase of the parser
is that this SQL9x construct is converted to a function call, which
happens to be

  timezone(tz, t)

The output of this function is a character string with a time and time
zone. By the time the parser hits the "what should we call this column?"
part of the code the fact that you didn't actually specify the
timezone() function call is already lost. It would be a bit misleading
to simply call the column "t", since it isn't really just "t", but "t"
evaluated at a specific time zone and then converted to a string. So I'm
not sure I have a suggestion as to what to call the column if we didn't
call it "timezone".

> 2. Is this really supposed to work?  More precisely: can I count on it if future
> releases?  The documentation
> (http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-select.html) does not
> mention it....

Sure. Well, very likely anyway. AT TIME ZONE is defined in SQL9x, so we
are likely to continue to support it.

                      - Thomas

Thomas Lockhart <lockhart@fourpalms.org> writes:
>> 2. Is this really supposed to work?  More precisely: can I count on it if future
>> releases?  The documentation
>> (http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-select.html) does not
>> mention it....

> Sure. Well, very likely anyway. AT TIME ZONE is defined in SQL9x, so we
> are likely to continue to support it.

I'd hardly expect the SELECT reference page to mention every possible
expression construct.  I do find it documented in the place I'd expect
to look for it:

http://developer.postgresql.org/docs/postgres/datatype-datetime.html#DATATYPE-TIMEZONES

            regards, tom lane