pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconvert in GMT time zone without summer time
6+ messages / 3 participants
[nested] [flat]
* convert in GMT time zone without summer time
@ 2011-04-15 06:20 LaraK <indarija@gmx.net>
2011-04-16 12:02 ` Re: convert in GMT time zone without summer time Jasen Betts <jasen@xnet.co.nz>
2011-04-18 07:10 ` Re: convert in GMT time zone without summer time LaraK <indarija@gmx.net>
0 siblings, 2 replies; 6+ messages in thread
From: LaraK @ 2011-04-15 06:20 UTC (permalink / raw)
To: pgsql-sql
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.
For example, it must now, in the summer, the German time back by 2 hours and
in the winter time only 1 hour. But it expects only back one hour.
Is there a function or a specific time zone?
(I work with PostgreSQL 8.4.7)
[CODE]
CREATE OR REPLACE FUNCTION CONVERT_TO_UTC (TIMESTAMP with time zone,
VARCHAR)
returns TIMESTAMP
as $$
declare
v_zone VARCHAR(20);
p_time ALIAS FOR $1;
p_zone ALIAS FOR $2;
v_time1 TIMESTAMP with time zone;
v_time2 TIMESTAMP with time zone;
v_text1 text;
begin
IF LENGTH(p_zone) IS NULL THEN
v_zone := 'GMT';
else
v_zone := p_zone;
END IF;
-- ++ Timestamp with time zone in Text umwandeln
select to_char(p_time, 'DD Mon YYYY HH24:MI:SS') into v_text1;
if(v_zone in ('BST', 'CET', 'DNT', 'FST', 'MET', 'MEWT', 'MEZ', 'NOR',
'SET', 'SWT', 'WETDST')) then
-- ++ Timestamp with time zone in die Zeitzone '+01' umwandeln. ++
SET TIME ZONE 1;
Select CONVERT_TO_UTC_EXEC(v_text1, 'UTC') into v_time1;
elsif(v_zone in ('JST', 'KST', 'MHT', 'WDT', 'AWSST')) then
SET TIME ZONE 9;
-- ++ Timestamp with time zone in die Zeitzone '+09' umwandeln. ++
Select CONVERT_TO_UTC_EXEC(v_text1, 'UTC') into v_time1;
elsif(v_zone in ('GMT', 'UT', 'UTC', 'Z', 'ZULU', 'WET')) then
-- ++ Zone wird nicht geändert ++
v_time1 := p_time;
else
raise exception 'unbekannte Zone - ist noch eine Baustelle';
end if;
RETURN v_time1 ;
end
$$
LANGUAGE 'plpgsql';
[/CODE]
[CODE]
CREATE OR REPLACE FUNCTION CONVERT_TO_UTC_EXEC (Text, Text)
returns TIMESTAMP
as $$
declare
p_time ALIAS FOR $1;
p_zone ALIAS FOR $2;
v_time1 TIMESTAMP with time zone;
v_time2 TIMESTAMP with time zone;
v_text1 text;
begin
select to_timestamp (p_time, 'DD Mon YYYY HH24:MI:SS') into v_time1 ;
-- ++ Timestamp with time zone in die UTC Zeitzone umwandeln. ++
Select timezone( p_zone, v_time1) INTO v_time2 ;
-- ++ Zeitausgabe formatieren: HH12. ++
v_text1 := to_char(v_time2, 'DD Mon YYYY HH12:MI:SS AM');
-- ++ In Type Timestamp umwandeln. ++
RETURN to_timestamp( v_text1, 'DD Mon YYYY HH12:MI:SS AM') ;
end
$$
LANGUAGE 'plpgsql';
[/CODE]
calling:
[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]
--
View this message in context: http://postgresql.1045698.n5.nabble.com/convert-in-GMT-time-zone-without-summer-time-tp4304830p43048...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: convert in GMT time zone without summer time
2011-04-15 06:20 convert in GMT time zone without summer time LaraK <indarija@gmx.net>
@ 2011-04-16 12:02 ` Jasen Betts <jasen@xnet.co.nz>
2011-04-18 16:56 ` Re: convert in GMT time zone without summer time Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 1 reply; 6+ messages in thread
From: Jasen Betts @ 2011-04-16 12:02 UTC (permalink / raw)
To: pgsql-sql
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.
>
> For example, it must now, in the summer, the German time back by 2 hours and
> in the winter time only 1 hour. But it expects only back one hour.
>
> Is there a function or a specific time zone?
if I undestand your goal correctly you want to subtract the daylight
savings offset from the given timezone if daylight-savings is in use
in the current time locale.
you can detect daylight-savings by setting testing the timezone offset
at 3 month intervals ( timestamp, timestamp+3months timestamp-3months,
timestamp+6months, timestamp-6months)
the one(s) of them with the least (most negative) offset from UTC will
represent non daylight-saving time.
if your given time has a different offset it's daylight saving time,
add the difference.
calling:
> [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;
CREATE OR REPLACE FUNCTION
CONVERT_TO_UTC ( timestamptz, text) returns timestamp as
$$ SELECT $1 at time zone 'UTC'; $$
language sql;
In that this function does not use the second parameter it may not be
what you want, on the other hand it's function matches it's name well.
what are you trying to do?
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: convert in GMT time zone without summer time
2011-04-15 06:20 convert in GMT time zone without summer time LaraK <indarija@gmx.net>
2011-04-16 12:02 ` Re: convert in GMT time zone without summer time Jasen Betts <jasen@xnet.co.nz>
@ 2011-04-18 16:56 ` Steve Crawford <scrawford@pinpointresearch.com>
2011-05-03 07:15 ` Re: convert in GMT time zone without summer time LaraK <indarija@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Steve Crawford @ 2011-04-18 16:56 UTC (permalink / raw)
To: LaraK <indarija@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: convert in GMT time zone without summer time
2011-04-15 06:20 convert in GMT time zone without summer time LaraK <indarija@gmx.net>
2011-04-16 12:02 ` Re: convert in GMT time zone without summer time Jasen Betts <jasen@xnet.co.nz>
2011-04-18 16:56 ` Re: convert in GMT time zone without summer time Steve Crawford <scrawford@pinpointresearch.com>
@ 2011-05-03 07:15 ` LaraK <indarija@gmx.net>
2011-05-03 15:54 ` Re: convert in GMT time zone without summer time Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 6+ messages in thread
From: LaraK @ 2011-05-03 07:15 UTC (permalink / raw)
To: pgsql-sql
Very good!
Another question:
I want to convert the string to the type TIMESTAMP WITH TIME ZONE. What do I
have to format? 'TZ' does not.
select to_timestamp('2011-03-22 14:17:00 Europe/Berlin', 'YYYY-MM-DD
HH:MI:SS TZ')
---
FEHLER: Formatmuster »TZ«/»tz« werden in to_date nicht unterstützt
--
View this message in context: http://postgresql.1045698.n5.nabble.com/convert-in-GMT-time-zone-without-summer-time-tp4304830p43665...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: convert in GMT time zone without summer time
2011-04-15 06:20 convert in GMT time zone without summer time LaraK <indarija@gmx.net>
2011-04-16 12:02 ` Re: convert in GMT time zone without summer time Jasen Betts <jasen@xnet.co.nz>
2011-04-18 16:56 ` Re: convert in GMT time zone without summer time Steve Crawford <scrawford@pinpointresearch.com>
2011-05-03 07:15 ` Re: convert in GMT time zone without summer time LaraK <indarija@gmx.net>
@ 2011-05-03 15:54 ` Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 6+ messages in thread
From: Steve Crawford @ 2011-05-03 15:54 UTC (permalink / raw)
To: LaraK <indarija@gmx.net>; +Cc: pgsql-sql
On 05/03/2011 12:15 AM, LaraK wrote:
> Very good!
>
> Another question:
> I want to convert the string to the type TIMESTAMP WITH TIME ZONE. What do I
> have to format? 'TZ' does not.
>
> select to_timestamp('2011-03-22 14:17:00 Europe/Berlin', 'YYYY-MM-DD
> HH:MI:SS TZ')
>
Just cast it to a timestamp with time zone:
select timestamptz '2011-04-22 19:17:00 Europe/Berlin';
Remember...
The value of a timestamp with time zone is always stored internally as UTC.
When a timestamp with time zone is displayed, the time zone is based on
the client's default, the "set timezone to" statement or the "at time
zone" clause in the query.
In the case of an explicit "at time zone" clause, the result becomes a
timestamp without time zone data type (that is why the previous static
example with the "at time zone" clause was a timestamp without time zone).
A timestamp with time zone is useful to identify a specific point in
time. "Bin Laden's death was announced at...", "shuttle Endeavor
launched at...", "Amazon EC2 crashed at...". Most timestamp data I
encounter is of this type.
A timestamp without time zone might be useful for data like "Breakfast
is served at 7am". Presumably a hotel chain would serve at 7am in each
hotel and not have all hotels serve at 7am corporate headquarters time.
It takes a bit of time to wrap your head around time and time zones but
it would be well worth your time to carefully read
http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html
(IIRC, you are using 8.4) a couple times.
Cheers,
Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: convert in GMT time zone without summer time
2011-04-15 06:20 convert in GMT time zone without summer time LaraK <indarija@gmx.net>
@ 2011-04-18 07:10 ` LaraK <indarija@gmx.net>
1 sibling, 0 replies; 6+ messages in thread
From: LaraK @ 2011-04-18 07:10 UTC (permalink / raw)
To: pgsql-sql
I find it difficult to understand you.
But the input I can not change:
>SELECT
>to_char(CONVERT_TO_UTC( '2011-03-22 14:17:00+01'::timestamptz
,'CET'),'yyyy-mm-dd hh12: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;
The system must know alone when is summertime and when is wintertime. Can it
this?
--
View this message in context: http://postgresql.1045698.n5.nabble.com/convert-in-GMT-time-zone-without-summer-time-tp4304830p43100...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-05-03 15:54 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-15 06:20 convert in GMT time zone without summer time LaraK <indarija@gmx.net>
2011-04-16 12:02 ` Jasen Betts <jasen@xnet.co.nz>
2011-04-18 16:56 ` Steve Crawford <scrawford@pinpointresearch.com>
2011-05-03 07:15 ` LaraK <indarija@gmx.net>
2011-05-03 15:54 ` Steve Crawford <scrawford@pinpointresearch.com>
2011-04-18 07:10 ` LaraK <indarija@gmx.net>
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