Re: timestamp <-> ctime conversion question...

On Tue, Dec 13, 2005 at 08:21:20PM +0100, Karsten Hilbert wrote:
> > Presumably this would allow timestamps to be displayed with a
> > timezone other than the current setting.
> *Display* of timestamptz values at arbitrary time zones is
> already possible using the "at time zone" syntax. Retrieving
> the "original" (as in "as inserted"), however, isn't
> possible to date. It would certainly be very useful. In

Actually, a while ago I wrote a module that would allow you to store
arbitrary tags with normal values and it would remember them. One of
the applications I thought of was such a type:

test=# select t, t + interval '2 hours' from timestamp_test offset 2
limit 1;
t | ?column?
---------------------------------------+---------------------------------------
2005-08-14 02:00:00+02 Asia/Hong_Kong | 2005-08-14 04:00:00+02 Asia/Hong_Kong
(1 row)

So it's displaying the timezone as my current time (UTC+1) but
remembered the timezone I supplied. To make it truly useful you'd have
to go though and create all the operators and probably come up with a
default output format. Something like:

select value(t) at time zone tag(t) from timestamp_test;

Would display the time in the timezone given. To answer the question
about why not use "timestamp" as the base type, it's to distinguish
daylight savings time. Another method would be to store an integer
(seconds since epoch) and the timezone as a pair.

http://svana.org/kleptog/pgsql/taggedtypes.html

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.