From: Laszlo Nagy <gandalf@shopzeus.com>
To: pgsql-admin@postgresql.org
Subject: Re: Messed up time zones
Date: Fri, 03 Aug 2012 17:23:51 +0200
Message-ID: <501BED07.5070603@shopzeus.com> (raw)
In-Reply-To: <16399.1344003584@sss.pgh.pa.us>
References: <501B8C6F.6040100@shopzeus.com>
<501BAA84.9010306@shopzeus.com>
<501BAE33.4090102@shopzeus.com>
<16399.1344003584@sss.pgh.pa.us>
On 2012-08-03 16:19, Tom Lane wrote:
> Laszlo Nagy <gandalf@shopzeus.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gandalf@shopzeus.com, pgsql-admin@postgresql.org
Subject: Re: Messed up time zones
In-Reply-To: <501BED07.5070603@shopzeus.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox