doubt on function extract(epoch...)

#11

Anselmo Canfora wrote:

Quote:

"Epoch" is absolute, so if several people all over the world run
SELECT EXTRACT(EPOCH FROM current_timestamp)
at the same time they should and will all get the same result.

It is the number of seconds that passed since the (absolute!!) timestamp
1970-01-01 00:00:00 UTC

if it is so, I don't understand why I get this:

db_atm=# select extract(epoch from current_timestamp) - extract(epoch from current_timestamp at time zone 'UTC') as tzshift;
tzshift
---------
7200
(1 row)

Let's assume that current_timestamp is '2009-07-08 14:58:08.019366+02'.

Then "current_timestamp at time zone 'UTC'" will be
'2009-07-08 12:58:08.019366' - that is a timestamp without time zone.
Explanation: what is '2009-07-08 14:58:08.019366' here is
'2009-07-08 12:58:08.019366' in UTC.

To calculate the epoch from a timestamp without time zone you must first
know which time zone should be assumed.
This is the "timezone" setting.
So we get the epoch of '2009-07-08 12:58:08.019366+02'.

And this is 7200 seconds less than the epoch of
'2009-07-08 14:58:08.019366+02'.

Quote:

Maybe the following query will make it clearer:

test=> SELECT EXTRACT(EPOCH FROM timestamp with time zone '1970-01-01 00:00:00 Europe/Rome');
date_part
-----------
-3600
(1 row)

Midnight in Rome was one hour *before* the Epoch (no daylight savings time).

this seems in contradiction to what you stated above, if epoch is a timezone invariant, why you get different outputs when you
specify different timezones?

Because the timestamps are different.

test=> SELECT timestamp with time zone '2009-07-08 12:00:00+02'
= timestamp with time zone '2009-07-08 12:00:00+00';
?column?
----------
f
(1 row)

Yours,
Laurenz Albe

#12

Laurenz Albe ha scritto:

Quote:

Anselmo Canfora wrote:
"Epoch" is absolute, so if several people all over the world run
SELECT EXTRACT(EPOCH FROM current_timestamp)
at the same time they should and will all get the same result.

It is the number of seconds that passed since the (absolute!!) timestamp
1970-01-01 00:00:00 UTC
if it is so, I don't understand why I get this:

db_atm=# select extract(epoch from current_timestamp) - extract(epoch from current_timestamp at time zone 'UTC') as tzshift;
tzshift
---------
7200
(1 row)

Let's assume that current_timestamp is '2009-07-08 14:58:08.019366+02'.

Then "current_timestamp at time zone 'UTC'" will be
'2009-07-08 12:58:08.019366' - that is a timestamp without time zone.
Explanation: what is '2009-07-08 14:58:08.019366' here is
'2009-07-08 12:58:08.019366' in UTC.

To calculate the epoch from a timestamp without time zone you must first
know which time zone should be assumed.
This is the "timezone" setting.
So we get the epoch of '2009-07-08 12:58:08.019366+02'.

And this is 7200 seconds less than the epoch of
'2009-07-08 14:58:08.019366+02'.

now I understood we were meaning the same thing, I misunderstood your
statement:

"so if several people all over the world run SELECT EXTRACT(EPOCH FROM
current_timestamp) at the same time they should and will all get the
same result"

and got slightly misguided, but given that "same time" can be "different
timestamps" depending of timezones all it is clear now.
Thank you for clarifications and patience

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

doubt on function extract(epoch...)

comp.databases.postgresql comp.databases.postgresql

Re: doubt on function extract(epoch...) - 07-08-2009 , 08:08 AM

Re: doubt on function extract(epoch...) - 07-08-2009 , 08:36 AM