doubt on function extract(epoch...)
Coniglio Sgabbiato
http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-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.
David Bolen
> so, how behaves the function extract with epoch parameter related with
> timezones? I have doubts about it.
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/static/datatype-datetime.html#DATATYPE-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
Coniglio Sgabbiato
> 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.
David Bolen
>> 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:
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).
> db_atm=# select current_timestamp;
> now
> ------------------------------
> 2009-06-26 10:05:57.46624+02
> (1 row)
This is shown to you in the server's timezone, but I believe
internally is held in UTC.
> 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)
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.
> db_atm=# select extract (epoch from timestamp with time zone '2009-06-26
> 10:05:57.46624+02');
> date_part
> ------------------
> 1246003557.46624
> (1 row)
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.
> db_atm=# select extract (epoch from timestamp with time zone '2009-06-26
> 10:05:57.46624');
> date_part
> ------------------
> 1246003557.46624
> (1 row)
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.
> db_atm=# select extract (epoch from timestamp '2009-06-26 10:05:57.46624');
> date_part
> ------------------
> 1246003557.46624
> (1 row)
This is the same as an earlier example
> 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:
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.
> (...)
>
> 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.
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
Laurenz Albe
> Coniglio Sgabbiato <nob...@nowhere.it> writes:
>> 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:
>
> 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).
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.
>> 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:
[...]
> 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.
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
David Bolen
> 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.
True, you can't guarantee a round trip that will retain an original
timezone as a consequence of the internal storage as UTC.
> 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)
Wow, that's definitely confusing. I suppose that requesting the
extraction of a specific time component is triggering the creation of
a new local timestamp string, but it's far from intuitive. It's like
the extract functions, though returning numbers, are actually a pure
string field extraction. I suppose that might make sense given that
the internal timestamp value is probably not stored as multiple
fields.
If you try to adjust things with an "at time zone", it's actually
adjusting the intermediate local time string, which is even more
confusing:
=> select extract(hour from (timestamp with time zone
'2009-06-26 10:05:57+11'
at time zone 'UTC+11'));
date_part
-----------
12
(1 row)
I originally figured this would give me back the original +11 hour
value of "10", but it seems to transition through a +02 local
timestamp before being converted to the target +11. Not obvious.
I guess it might be safest to set the session timezone to UTC before
transitioning between various time zones and doing extractions. At
least that way an intermediate format is time zone-free.
The above confusion doesn't seem to influence the epoch calculation
though (at least not with 8.3.7) which as you point out is distance
from UTC:
=> show timezone;
TimeZone
---------------
Europe/Vienna
(1 row)
=> select extract(epoch from (timestamp '2009-06-26 10:05:57'));
date_part
------------
1246003557
(1 row)
=> select extract(epoch from (timestamp with time zone
'2009-06-26 10:05:57+11'));
date_part
------------
1245971157
(1 row)
The difference is -32400, so the TZ+11 date is 9 hours earlier (from
the epoch) than the TZ+02 date which seems right.
I suspect that's because the epoch function can work with the internal
UTC value, while the per-field extractions use the local timestamp, but
I can't find anything in the documentation that would help know when
which was being used.
Although the docs for epoch are the only ones that use a timezone
including string (distance from 1970-01-01 00:00:00-00) so maybe that's
a hint.
> 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.
I'm intrigued by what steps the above actually represents, since
someone (presumably not the client) has to parse the string to produce
UTC and/or the local time zone, so at what point is it forced back
into a local format? With my 8.3.7 system a normal explain does not
include that Output: line and explain verbose is a very different
lower level output.
-- David
Laurenz Albe
>> 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.
>
> True, you can't guarantee a round trip that will retain an original
> timezone as a consequence of the internal storage as UTC.
I have asked about this on the pgsql-general mailing list
(http://archives.postgresql.org/pgsql-general/2009-06/msg01318.php)
and got a very helpful answer that may help understand the problem:
PostgreSQL's "timestamp with time zone" is not a "timestamp AND
time zone" but should more appropriately be called "absolute timestamp".
That is, there is no time zone information stored along with
the data, internally it is an UTC timestamp.
Tom Lane has mentioned in a reply that this seems in fact to be
somewhat different from what the SQL standard had in mind, yet
"we've discussed this in the past but there's not been a lot
of enthusiasm for changing it ... aside from the work involved, it would
mean doubling the space required for a timestamptz value (because of
alignment considerations)".
>> Compare the following surprising results (this is 8.4):
>>
>> 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)
>
> Wow, that's definitely confusing. I suppose that requesting the
> extraction of a specific time component is triggering the creation of
> a new local timestamp string, but it's far from intuitive. It's like
> the extract functions, though returning numbers, are actually a pure
> string field extraction. I suppose that might make sense given that
> the internal timestamp value is probably not stored as multiple
> fields.
Oj wej, now I've got you confused :^)
What is actually going on is something different:
The original timestamp is converted to its internal UTC
representation.
To answer the question "what is the HOUR value of this absolute timestamp?"
you first have to answer "in which time zone do you want the result?".
In PostgreSQL, the answer to that is "in your current time zone".
> If you try to adjust things with an "at time zone", it's actually
> adjusting the intermediate local time string, which is even more
> confusing:
>
> => select extract(hour from (timestamp with time zone
> '2009-06-26 10:05:57+11'
> at time zone 'UTC+11'));
> date_part
> -----------
> 12
> (1 row)
>
> I originally figured this would give me back the original +11 hour
> value of "10", but it seems to transition through a +02 local
> timestamp before being converted to the target +11. Not obvious.
Beware. You wade deeply in the murky waters of POSIX timeszone
specifications, see
http://www.postgresql.org/docs/8.4/static/datatype-datetime.html#DATATYPE-TIMEZONES
The time zone in which the timestamp above would appear as
'2009-06-26 10:05:57' is actually "UTC-11".
So if you had extracted DAY in your above sample, the answer
would have been "25" because you have crossed the international date line...
> I guess it might be safest to set the session timezone to UTC before
> transitioning between various time zones and doing extractions. At
> least that way an intermediate format is time zone-free.
"timestamp with time zone" is never ignorant of time zone -
all you have to keep in mind is that it is always interpreted
with respect to your current timezone.
The EXTRACT results are the answer to the question:
In my local time zone, what is the day/hour/... value of this
absolute timestamp?
> The above confusion doesn't seem to influence the epoch calculation
> though (at least not with 8.3.7) which as you point out is distance
> from UTC:
Right. Distance in seconds from midnight Jan 1st 1970, UTC,
which is independent from your local time zone.
> I suspect that's because the epoch function can work with the internal
> UTC value, while the per-field extractions use the local timestamp, but
> I can't find anything in the documentation that would help know when
> which was being used.
Yes.
>> 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.
>
> I'm intrigued by what steps the above actually represents, since
> someone (presumably not the client) has to parse the string to produce
> UTC and/or the local time zone, so at what point is it forced back
> into a local format? With my 8.3.7 system a normal explain does not
> include that Output: line and explain verbose is a very different
> lower level output.
Yes, EXPLAIN VERBOSE output has changed in 8.4 and is more helpful here.
I guess I have been sloppy. I should have said: "is presented in" instead
of "is immediately converted to".
I would say the string '2009-06-26 01:05:57.46624+02' is generated as follows:
"I am EXPLAIN VERBOSE, and I should present the values to the user.
Now, what do we have here: a timestamp with time zone (an absolute timestamp).
How should we present this value to the user? He/she would not be happy with
our internal representation. So let's display it in the user's time zone."
Yours,
Laurenz Albe
David Bolen
> David Bolen wrote:
>> True, you can't guarantee a round trip that will retain an original
>> timezone as a consequence of the internal storage as UTC.
>
> I have asked about this on the pgsql-general mailing list
> (http://archives.postgresql.org/pgsql-general/2009-06/msg01318.php)
> and got a very helpful answer that may help understand the problem:
>
> PostgreSQL's "timestamp with time zone" is not a "timestamp AND
> time zone" but should more appropriately be called "absolute timestamp".
>
> That is, there is no time zone information stored along with
> the data, internally it is an UTC timestamp.
Agreed (and the docs do spell this out somewhere if I recall
correctly). That's what I intended to mean by the phrase "internal
storage as UTC", which is the internal absolute fixed time zone (if
you consider an implicit offset of 0 conceptually as a time zone -
there's no separate time zone data stored).
The rest of your follow-up and the psql-general thread was helpful in
further clarifying things. I suspect that I also wasn't precise
enough with some of my earlier response since your explanation matches
at least my mental conception of the conversion that I thought was
being applied on input and output.
> Beware. You wade deeply in the murky waters of POSIX timeszone
> specifications, see
> http://www.postgresql.org/docs/8.4/static/datatype-datetime.html#DATATYPE-TIMEZONES
>
> The time zone in which the timestamp above would appear as
> '2009-06-26 10:05:57' is actually "UTC-11".
> So if you had extracted DAY in your above sample, the answer
> would have been "25" because you have crossed the international date line...
Crud, yeah, that's what I missed - I didn't realize I had switched
between ISO and Posix formats, which then requires inverting the sign
on the time zone offset.
Ok, with that fixed, my "at time zone" example makes sense and obeys
the same rules as the rest of the examples we've been using.
-- David
Anselmo Canfora
> David Bolen wrote:
>> Coniglio Sgabbiato <nob...@nowhere.it> writes:
>>> 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:
>> 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).
>
> I guess that Coniglio (BTW, is this your real first name??)
no, it is some sort of funny/moron nickname, my real name is the one you
read now :)
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.
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
Laurenz Albe
>> I guess that Coniglio (BTW, is this your real first name??)
>
> no, it is some sort of funny/moron nickname, my real name is the one you read now :)
Actually, I liked "Coniglio".
> 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?
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
Anselmo Canfora
> Anselmo Canfora wrote:
>>> I guess that Coniglio (BTW, is this your real first name??)
>> no, it is some sort of funny/moron nickname, my real name is the one you read now :)
>
> Actually, I liked "Coniglio".
it is "rabbit" in English :)
>
>> 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?
>
> 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.
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 :)
Laurenz Albe
>> Actually, I liked "Coniglio".
>
> it is "rabbit" in English :)
That's why I asked.
> 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
"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
> 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?
... 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
Anselmo Canfora
[CUT]
> "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)
>> 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?
>
> .... 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 seems in contradiction to what you stated above, if epoch is a
timezone invariant, why you get different outputs when you specify
different timezones?
Laurenz Albe
>> "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'.
>> 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
Anselmo Canfora
> 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 :)