On 2012-08-03 16:19, Tom Lane wrote:
Laszlo Nagy <gandalf@xxxxxxxxxxxx> writes:
So how do I create a query that results in something like:
a
------------------------------
Sun Oct 30 02:00:00 2011 +0500
Sun Oct 30 02:00:00 2011 +0600
(2 rows)
Set the "timezone" setting to the zone you have in mind, and then just
print the values.
majorforms=> set time zone 'Europe/Budapest';
SET
majorforms=> select * from test;
a
------------------------
2011-10-30 02:00:00+02
2011-10-30 02:00:00+01
(2 rows)
majorforms=>
It works. Thank you!
So is it impossible to construct a query with columns that are different
time zones? I hope I'm not going to need that. :-)
The reason there's no manual way to do rotation
across zones is that there's no need for one because it's done
automatically during printout of a timestamptz value.
I can come up with an example when it would be needed. For example,
consider a company with two sites in different time zones. Let's say
that they want to store time stamps of online meetings. They need to
create a report that shows the starting time of the all meetings *in
both zones*. I see no way to do this in PostgreSQL. Of course, you can
always select the timestamps in UTC, and convert them into other time
zones with a program so it is not a big problem. And if we go that
route, then there is not much point in using the timestamptz type, since
we already have to convert the values with a program...
I suspect that you have not correctly internalized what timestamptz
values actually are. Internally they are just time values specified in
UTC (or UT1 if you want to be picky). On input, the value is rotated
from whatever zone is specified in the string (or implicitly specified
by "timezone") to UTC. On output, the value is rotated from UTC to
whatever the current "timezone" setting is.
Oh I see. So actually they don't store the zone? I have seen that
timestamptz and timestamp both occupy 8 bytes, but I didn't understand
completely.
It also means that if I want to store the actual time zone (in what the
value was originally recorded), then I have to store the zone in a
separate field. Later I can convert back to the original time zone, but
only with an external program.
Fine with me. I'm happy with this, just I did not understand how it works.
Thanks,
Laszlo
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
