From: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | day_trunc and day duration in a remote time zone |
Date: | 2013-10-17 15:47:24 |
Message-ID: | B6F6FD62F2624C4C9916AC0175D56D880CE2B75C@jenmbs01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I want to retrieve the day start and duration of an epoch within a given time zone
and return the day start as epoch.
the queries below works as expected, but I would appreciate a simpler solution ...
example:
( http://localtimes.info/Europe/Cyprus/Nicosia/ )
select (TIMESTAMP WITH TIME ZONE 'epoch' + interval '1382886336 seconds') AT TIME ZONE 'Europe/Nicosia'
2013-10-27 17:05:36
SELECT
EXTRACT (epoch FROM cast(date( (TIMESTAMP WITH TIME ZONE 'epoch' + interval '1382886336 seconds' )
AT TIME ZONE 'Europe/Nicosia') || ' 00:00 Europe/Nicosia' as timestamptz)) as day_start
=> 1382821200
SELECT
(
EXTRACT (epoch FROM cast(date( (TIMESTAMP WITH TIME ZONE 'epoch' + interval '1382983536 seconds' /* added 27 hours to the previous day_start result */ )
AT TIME ZONE 'Europe/Nicosia') || ' 00:00 Europe/Nicosia' as timestamptz))
-
1382821200 /* = day_start*/
)/3600 as hour_duration
=> 25 hours, which is correct as the daylight saving time ends at this date
regards,
Marc Mamin
From | Date | Subject | |
---|---|---|---|
Next Message | Oscar Calderon | 2013-10-17 15:49:31 | Strange results with pg_restore |
Previous Message | Victor Yegorov | 2013-10-17 14:26:55 | Re: Idle transactions in PostgreSQL 9.2.4 |