day_trunc and day duration in a remote time zone

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

Responses

Browse pgsql-general by date

  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