Re: timezone() with timeofday() converts the wrong direction? - Mailing list pgsql-general

On Mon, Apr 25, 2005 at 08:28:47AM +0200, Karsten Hilbert wrote:
> > > gnumed=> create teable test (f timestamp with time zone);
> > > CREATE
> >
> > Does that really work in 7.1.3?  Trying to create a "teable" fails
>
> This results from "script" logging backspaces into the log
> file instead of deleting characters in there and my not
> spotting that when cleaning up the log file. Nothing to do with
> the question at hand.

It doesn't matter in this particular case because we could see what
was intended (I honestly wondered if that was a typo that 7.1.3
accepted), but sometimes subtle differences can matter so it's
better to post code that others can copy and paste verbatim into
their database -- that way the corrections themselves don't introduce
different behavior.

> >   select f, f::timestamp at time zone 'MEZ' from test;
> >                  f               |          timezone
> >   -------------------------------+----------------------------
> >    2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218
> >   (1 row)
>
> Huh ? This IS different time zones from the same timestamp
> within the same query, isn't it ??

No, it isn't.  In the above example from 7.2.7 the second column
has no time zone specification whatsoever; if you cast it to timestamp
with time zone then you get the following:

select f, (f::timestamp at time zone 'MEZ')::timestamptz from test;
               f               |           timezone
-------------------------------+-------------------------------
 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+00
(1 row)

There's only one time zone displayed here: +00.  Those are different
times being displayed in the same time zone, not the same time being
displayed in different time zones.  Other versions of PostgreSQL
give varying output, but they all show both columns in the same
time zone.

The problem I was discussing involves getting *different* time zone
specifictions in the output.  That is, something like this (which
apparently is possible in 7.1.3 but not in later versions):

               f               |           timezone
-------------------------------+-------------------------------
 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+01

> The fact that different versions of PostgreSQL get it right or
> wrong in a variety of ways indicates that the logic may need
> to be fixed but does show that in principle it is quite
> possible.

Certainly it's possible in principle; the question is how to do it
in practice in modern versions of PostgreSQL.  As far as I can tell
it's not possible (short of changing the type to text, as one of
my earlier examples did), but again, I'd be pleased to be corrected
(preferably with an example that works in 8.0.2).

BTW, this is mostly academic to me, but others have asked similar
questions in the past so I've been curious about whether it could
be done.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/