Hi, I read onto documentation:

http://www.postgresql.org/docs/8.3/s...TETIME-EXTRACT

"epoch

For date and timestamp values, the number of seconds since
1970-01-01 00:00:00-00 (can be negative); for interval values, the total
number of seconds in the interval

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16
20:38:40-08');
Result: 982384720

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800

Here is how you can convert an epoch value back to a time stamp:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1
second';"

now if I do a couple of tests I get this:

db_atm=# select extract(epoch from timestamp '2009-06-23 10:45:07.967524');
date_part
------------------
1245746707.96752
(1 row)

db_atm=# select extract(epoch from timestamp '2009-06-23
10:45:07.967524+02');
date_part
------------------
1245746707.96752
(1 row)

db_atm=# select extract(epoch from timestamp with time zone '2009-06-23
10:45:07.967524');
date_part
------------------
1245746707.96752
(1 row)

db_atm=# select extract(epoch from timestamp with time zone '2009-06-23
10:45:07.967524+02');
date_part
------------------
1245746707.96752
(1 row)

so, how behaves the function extract with epoch parameter related with
timezones? I have doubts about it.

Thank you in advance for the help you might give.

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) it> writes:

I suspect what you're seeing is most likely due to the time zone
parsing and interpretation of input strings rather than the epoch
extraction. By any chance is your server running with a system
timezone setting that has an offset of +2?

When parsing an input timestamp string, PostgreSQL is going to ignore
any time zone specified unless the input is specifically a "with time
zone" data type. Instead, it assumes the timestamp is in its local
(as set with the "timezone" parameter) time zone. And it always
translates the input into UTC internally based on that input (or
implicit) time zone.

See 8.5.1.3 in http://www.postgresql.org/docs/8.3/s...DATETIME-INPUT

In particular, the bit where PostgreSQL deviates from the SQL standard
in that it doesn't look at the content of timestamp strings (to look
for + or -) to identify a time zone containing string.

For example, here's some queries against an 8.3.7 system where my
server is in the US/Eastern time zone (current offset -04):

db3l=> show timezone;
TimeZone
------------
US/Eastern

db3l=> select timestamp '2009-06-23 10:45:07';
timestamp
---------------------
2009-06-23 10:45:07

db3l=> select cast(timestamp '2009-06-23 10:45:07'
db3l(> as timestamp with time zone);

timestamptz
------------------------
2009-06-23 10:45:07-04

db3l=> select cast(timestamp '2009-06-23 10:45:07-07'
db3l(> as timestamp with time zone);
timestamptz
------------------------
2009-06-23 10:45:07-04

db3l=> select cast(timestamp with time zone '2009-06-23 10:45:07-07'
db3l(> as timestamp with time zone);
timestamptz
------------------------
2009-06-23 13:45:07-04

See how the input time zone is ignored in the third case. Note that I
include the explicit "timestamp" type on the input string since
otherwise PostgreSQL is smart enough in this particular query to apply
the output cast type when parsing the input which might obscure what
is going on with the input parsing.

In each of the queries requesting an output with time zone, the server
time zone is used for the output.

And of course, how the input is interpreted is crucial, since the
final internal UTC value is what epoch is going to be breaking down.
For example, the epoch query with time zone-less input matches input
with a timezone that is the same as the server:

db3l=> show timezone;
Timezone
------------
US/Eastern

db3l=> select extract(epoch from timestamp '2009-06-23 10:45:07');
date_part
------------
1245768307

db3l=> select extract(epoch from timestamp with time zone
db3l(> '2009-06-23 10:45:07-04');
date_part
------------
1245768307

As with the basic queries above, even if a time zone is included, it
is ignored if the input type does not include the time zone:

db3l=> select extract(epoch from timestamp '2009-06-23 10:45:07-07');
date_part
------------
1245768307

and changes to the server's time zone are tracked (in this case as a
changed session setting):

db3l=> set timezone="US/Pacific";
SET
db3l=> select extract(epoch from timestamp '2009-06-23 10:45:07');
date_part
------------
1245779107

Qualifying the input type and including the time zone in the input
string does divorce the result from the default server time zone
setting:

An explicit US/Pacific query (with my server restored to US/Eastern):

db3l=> select extract(epoch from timestamp with time zone
db3l(> '2009-06-23 10:45:07-07');
date_part
------------
1245779107

or perhaps UTC:

db3l=> select extract(epoch from timestamp with time zone
db3l(> '2009-06-23 10:45:07-00');
date_part
------------
1245753907

which all seems reasonable. So I would expect that your final example
is working properly, but since it gives the same result as your
timezone-less tests, perhaps your server's default time zone is at
+02?

-- David

Hi, I am definitely on TZ +02, the doubt it is about the way in which PG
parses strings as timestamps, it seems that it parses timestamps always
in UTC, not caring about the actual time zone:

db_atm=# select current_timestamp;
now
------------------------------
2009-06-26 10:05:57.46624+02
(1 row)

db_atm=# select extract(timezone from current_timestamp);
date_part
-----------
7200
(1 row)

db_atm=# select extract (epoch from current_timestamp);
date_part
------------------
1246003597.63392
(1 row)

db_atm=# select extract (epoch from timestamp '2009-06-26
10:05:57.46624+02');
date_part
------------------
1246003557.46624
(1 row)

db_atm=# select extract (epoch from timestamp with time zone '2009-06-26
10:05:57.46624+02');
date_part
------------------
1246003557.46624
(1 row)

db_atm=# select extract (epoch from timestamp with time zone '2009-06-26
10:05:57.46624');
date_part
------------------
1246003557.46624
(1 row)

db_atm=# select extract (epoch from timestamp '2009-06-26 10:05:57.46624');
date_part
------------------
1246003557.46624
(1 row)

as you can see the output from epoch is alway the same, in order to
export the epoch to JSON I had to write this SP:

CREATE OR REPLACE FUNCTION utcepoch(ts timestamp with time zone)
RETURNS bigint AS
$BODY$
declare
ltime bigint;
tz int;
utctime bigint;
begin
ltime:=extract(epoch from date_trunc('milliseconds', ts) )*1000;
tz:=extract(timezone from date_trunc('milliseconds', ts) )*1000;
utctime:=ltime + tz;
return utctime;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE

I created this too:

CREATE OR REPLACE FUNCTION epoch(ts timestamp with time zone)
RETURNS bigint AS
$BODY$
declare
ltime bigint;
begin
ltime:=extract(epoch from date_trunc('milliseconds', ts) )*1000;
return ltime;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION epoch(timestamp with time zone) OWNER TO postgres;

see this:

db_atm=# select extract(epoch from current_timestamp);
date_part
------------------
1246004613.92817
(1 row)

db_atm=# select utcepoch(current_timestamp);
utcepoch
---------------
1246011815928
(1 row)

db_atm=# select epoch(current_timestamp);
epoch
---------------
1246004617439
(1 row)

it seems that epoch it is always computed without regard to TZ.

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) it> writes:

It only ignores the time zone in an input string if it believes it is
parsing a string for a data type without a time zone (see again
section 8.5.1.3 in the docs). As long as you include a type
specification that includes a time zone it will parse what you supply
(as I believe my examples in the prior post showed).

This is shown to you in the server's timezone, but I believe
internally is held in UTC.

This is identical to the prior case because you supply a "timestamp"
(no time zone) input data type. The time zone happens to match your
server's zone too, but it's ignored anyway.

Here, PostgreSQL did parse the time zone, but since you specified the same
one as the server (and thus the value it was using when not parsing
the time zone) the result remains the same.

Here, PostgreSQL was willing to parse a time zone, but none was
supplied, so again it will assume the string is in the server's time
zone, and thus still +02.

This is the same as an earlier example

Yes, but I would expect all of the above to be the same, since in all
cases, whether explicitly or implicitly, PostgreSQL is assuming the
supplied time string is in the +02 time zone. Why did you expect any
of those to be different, especially given my prior response?

In other words, all of the various time strings you supplied in the
examples above, essentially get parsed (or already represent in the
case of current_timestamp) the exact same instant in time.

Now, if you had tried commands like:

select extract (epoch from timestamp with time zone '2009-06-26 10:05:57.46624+04');
or
select extract (epoch from timestamp with time zone '2009-06-26 10:05:57.46624+00');

for example, I would have expected you to get some different results
since at least in those cases the time zone would be different than +02.

It seems to me that it's always computed to the value given by the
date provided, which may or may not represent a specific time in a
given TZ depending on how it was parsed (and at least internally is
always the UTC value converted from the date originally parsed).

Sure, if you manually adjust that date by some value, it will then
represent a different instance in time, and the epoch value will
change. That may or may not be appropriate for your specific
application, so can't say if it's right or wrong.

But I do believe that the epoch query itself is doing the right thing,
and accurately represents the time delta from the epoch to whatever
instant in time you are supplying to the query.

-- David

David Bolen wrote:
I guess that Coniglio (BTW, is this your real first name??) means
that PostgreSQL internally converts a timestamp with time zone to
UTC and on output always converts it to the local time zone,
so that the time zone information is not preserved.

Compare the following surprising results (this is 8.4):

test=> SHOW timezone;
TimeZone
---------------
Europe/Vienna
(1 row)

This is UTC+02 in summer.

test=> SELECT EXTRACT(TIMEZONE FROM (timestamp with time zone
'2009-06-26 10:05:57.46624+11'));
date_part
-----------
7200
(1 row)

test=> SELECT EXTRACT(HOUR FROM (timestamp with time zone
'2009-06-26 10:05:57.46624+11'));
date_part
-----------
1
(1 row)

7200 = 2*3600 is my local time tone.
The gut reaction is "what the **** is going on here", but an
EXPLAIN will show it:

test=> EXPLAIN VERBOSE
SELECT EXTRACT(TIMEZONE FROM (timestamp with time zone
'2009-06-26 10:05:57.46624+11'));
QUERY PLAN
------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
Output: date_part('timezone'::text,
'2009-06-26 01:05:57.46624+02'::timestamp with time zone)
(2 rows)

So the timestamp is immediately converted to my local time zone.

[...]

Epoch will be the seconds between 1970-01-01 00:00:00 UTC and the timestamp.

What does Coniglio's function "utcepoch" do?
As we have seen above, the "extract(timezone from ....)" will always
return 7200 for him.
So the function always returns for the timestamp + 7200 seconds, no matter
what the time zone of the supplied timestamp is.
In other words, "utcepoch" will return the number of milliseconds
between midnight of 1.1.1970 in the timezone of Coniglio's server and
the supplied timestamp.

What this has to do with UTC eludes me :^)

Yours,
Laurenz Albe

Laurenz Albe ha scritto:
no, it is some sort of funny/moron nickname, my real name is the one you
read now

means
this was exactly my point of concern

my intent was to produce an "epoch time" in UTC time, as the name
suggests, actually the sp works as expected, the javascript client
library I am using accepts time data in UTC TZ, and currently it is
working fine for me (automated computed ticks on graphs are right).
(see "time series data" on http://people.iola.dk/olau/flot/API.txt)
see below:

db_atm=# select current_timestamp;
now
-------------------------------
2009-07-06 11:05:52.618695+02
(1 row)

db_atm=# select extract (epoch from current_timestamp);
date_part
------------------
1246871157.86648
(1 row)

db_atm=# select extract (timezone from current_timestamp);
date_part
-----------
7200
(1 row)
^
db_atm=# select extract (timezone from '2009-07-06
11:05:52.618695+02'::timestamp with time zone);
date_part
-----------
7200
(1 row)

db_atm=# select extract (timezone from '2009-07-06
11:05:52.618695+00'::timestamp with time zone);
date_part
-----------
7200
(1 row)

db_atm=# select extract (epoch from '2009-07-06
11:05:52.618695+00'::timestamp with time zone);
date_part
-----------------
1246878352.6187
(1 row)

db_atm=# select extract (epoch from '2009-07-06
11:05:52.618695+02'::timestamp with time zone);
date_part
-----------------
1246871152.6187
(1 row)

so, it seems that:

db_atm=# select current_timestamp;
now
-------------------------------
2009-07-06 11:05:52.618695+02
(1 row)

retrieves the current UTC time add up it the 2 hours of current TZ and
write out this time with the indication "+02" to make you understand
that the local TZ shift was added. BUT, when I call the "epoch"
extraction it seems that EXTRACT uses the internal UTC representation,
so I am forced to manually add the number of seconds of TZ shift in
order to have the actual epoch. May it be so?

so I set something like this:

prompt=# \d <sometable>
Table "sometable"
Column | Type | Modifiers
-------------+--------------------------+-------------------------
........... | ........................ |
........... | ........................ |
........... | ........................ |
........... | ........................ |
........... | ........................ |
........... | ........................ |
epoch | bigint | default utcepoch(now())

and in epoch field I have the epoch * 1000 in UTC TZ

Anselmo Canfora

Anselmo Canfora wrote:
Actually, I liked "Coniglio".

Depends on your definition of "the actual epoch".
Usually that is: seconds since 1970-01-01 00:00:00 UTC.

Your definition is different:
Seconds since 1970-01-01 00:00:00 at my local time zone.

That's fine, and if that is what you need, who can argue with it.
It's just that the name "utcepoch" would not have been my choice for
that function.

Yours,
Laurenz Albe

Laurenz Albe ha scritto:
it is "rabbit" in English

I am still a little bit confused on this point, given a moment in time,
say PT, it seems to me that:

extract(epoch from date_trunc('milliseconds', current_timestamp))*1000

_should_ give me the epoch of PT expressed in my local time

so if I add up my timezone shift:

extract(timezone from date_trunc('milliseconds', current_timestamp) )*1000;

it _should_ give me the UTC epoch of PT, is it right?

actually it worked on my application, then the name of my sp, but I wish
to be sure that it is working not for some kind of error compensation

Anselmo Canfora wrote:
That's why I asked.

"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 you add your timezone shift, you will get the time passed
since 1970-01-01 00:00:00 in your local time zone.
There is no connection to UTC here.

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 query should and will do the same whatever your local time zone is,
because the above statement is always correct.

Yours,
Laurenz Albe

Laurenz Albe ha scritto:
[CUT]
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)

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?