day_trunc and day duration in a remote time zone - Mailing list 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