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:
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
