From: Steve Crawford <scrawford@pinpointresearch.com>
To: LaraK <indarija@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: convert in GMT time zone without summer time
Date: Mon, 18 Apr 2011 09:56:45 -0700
Message-ID: <4DAC6D4D.30405@pinpointresearch.com> (raw)
In-Reply-To: <ioc0gr$53p$1@reversiblemaps.ath.cx>
References: <1302848430605-4304830.post@n5.nabble.com>
<ioc0gr$53p$1@reversiblemaps.ath.cx>
On 04/16/2011 05:02 AM, Jasen Betts wrote:
> On 2011-04-15, LaraK<indarija@gmx.net> wrote:
>> Hello,
>>
>> I want write a function that converts a timestamp with time zone to the UTC
>> zone. But it should all be stored in the winter time.
Done! All timestamp with time zone information is stored internally in UTC.
But you need to be sure you really understand date/time manipulation in
PostgreSQL so you don't reinvent the wheel.
[CODE]
>> SELECT
>> to_char(CONVERT_TO_UTC(to_timestamp('2011-03-22 14:17:00', 'YYYY-MM-DD
>> hh24:MI:SS'), 'CET'), 'yyyy-mm-dd hh24:MI:SS') AS winter,
>> to_char(CONVERT_TO_UTC(to_timestamp('2011-04-22 14:17:00', 'YYYY-MM-DD
>> hh24:MI:SS'), 'CET'), 'yyyy-mm-dd hh24:MI:SS') AS summer
>> [/CODE]
>>
>> must come out:
>> [CODE]
>> WINTER | SUMMER
>> --------------------+-------------------------
>> 2011-03-22 13:17:00 | 2011-04-22 12:17:00
>> [/CODE]
> that test case is ambiguous your inputs are timespamptz but
> have an unspecified timezone (and so get the zone appropriate to
> your time locale). I'm assuming your time locale is "Europe/Berlin"
> and you really mean the following:
>
> SELECT to_char(CONVERT_TO_UTC( '2011-03-22 14:17:00+01'::timestamptz
> ,'CET'),'yyyy-mm-dd hh24:MI:SS') AS winter, to_char(CONVERT_TO_UTC(
> '2011-04-22 14:17:00+02'::timestamptz ,'CET'),'yyyy-mm-dd hh24:MI:SS')
> AS summer;
If you can use the correct time zone name, everything is done for you.
Better yet, it will keep working when the timezone rules change (if you
apply your patches regularly) or for other time zones:
steve=> select '2011-03-22 14:17:00 Europe/Berlin' at time zone 'UTC';
timezone
---------------------
2011-03-22 13:17:00
(1 row)
steve=> select '2011-04-22 14:17:00 Europe/Berlin' at time zone 'UTC';
timezone
---------------------
2011-04-22 12:17:00
Cheers,
Steve
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-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, indarija@gmx.net
Subject: Re: convert in GMT time zone without summer time
In-Reply-To: <4DAC6D4D.30405@pinpointresearch.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