[GENERAL] day_trunc and day duration in a remote time zone

[Marc Mamin]

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