Thread: day_trunc and day duration in a remote time zone
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
On 10/17/2013 08:47 AM, Marc Mamin wrote:
> 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.
Not quite sure what you are looking for, but something like:
hplc=> select current_date::timestamp, extract(epoch from
current_date::timestamp);
timestamp | date_part
---------------------+------------
2013-10-17 00:00:00 | 1381968000
> regards,
> Marc Mamin
--
Adrian Klaver
adrian.klaver@gmail.com