Converting epoch to timestamp
Hello all.
In IRC, one of the questions we get from time to time is how to convert
UNIX epoch to PostgreSQL timestamp. Users are often surprised there
isn't a builtin cast or function that does this.
I've put together two simple SQL functions that accomplish this. I
think they would make the epoch-to-timestamp converstion more
convenient. I don't know if they would be more efficient if written in
C.
create or replace function epoch_to_timestamp(
integer
) returns timestamp(0)
language sql as '
SELECT ''epoch''::timestamp + $1 * ''1 second''::interval;
';
create or replace function epoch_to_timestamptz(
double precision
) returns timestamptz
language sql as '
SELECT (''epoch''::timestamp + $1 * ''1 second''::interval) at time
zone ''UTC'';
';
The first function, epoch_to_timestamp, conforms to the normal
definition of epoch, which is integer seconds. It returns timestamp at
UTC. The second function, epoch_to_timestamptz, provides an inverse to
extract(epoch from timestamp), which returns double precision.
I'm wondering if this wouldn't be better as cast rather than explicit
functions. I'm interested in hearing people's thoughts, whether or not
these would be useful additions to PostgreSQL and whether this
particular implementation is appropriate. If it is, I'll write up some
docs for the appropriate section, with any modifications people might
suggest.
Thank you for your time.
Michael Glaesemann
grzm myrealbox com
Michael Glaesemann <grzm@myrealbox.com> writes:
create or replace function epoch_to_timestamp(
integer
) returns timestamp(0)
language sql as '
SELECT ''epoch''::timestamp + $1 * ''1 second''::interval;
';
This is in fact wrong, unless you live in the British Isles: the
result will be off by your timezone displacement from UTC. Correct
is to use timestamptz not timestamp.
As an example: timestamp 1089953023 equates to Fri Jul 16 2004, 00:43:43 EDT
according to strftime() on my machine (I live in US Eastern zone which
is presently GMT-4). I get
regression=# select 'epoch'::timestamp + 1089953023 * '1 second'::interval;
?column?
---------------------
2004-07-16 04:43:43
(1 row)
regression=# select 'epoch'::timestamptz + 1089953023 * '1 second'::interval;
?column?
------------------------
2004-07-16 00:43:43-04
(1 row)
The first is not right, the second is ...
I'm wondering if this wouldn't be better as cast rather than explicit
functions.
A cast from integer is probably a bad idea, seeing that it will break in
2038. You could make an argument for a cast from double though. The
issue to my mind is whether this might be too Unix-centric.
regards, tom lane
This is in fact wrong, unless you live in the British Isles: the
result will be off by your timezone displacement from UTC. Correct
is to use timestamptz not timestamp.As an example: timestamp 1089953023 equates to Fri Jul 16 2004, 00:43:43 EDT
according to strftime() on my machine (I live in US Eastern zone which
is presently GMT-4). I get
I would actually prefer this syntax:
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2003-01-01 11:23:44');
Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
I would actually prefer this syntax:
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2003-01-01 11:23:44');
Yeah, but I think Michael's question had to do with going the other
way (numeric to timestamp).
regards, tom lane
On Jul 16, 2004, at 1:55 PM, Tom Lane wrote:
This is in fact wrong, unless you live in the British Isles: the
result will be off by your timezone displacement from UTC. Correct
is to use timestamptz not timestamp.
Thanks. Timestamps and time zones are a challenge for me.
A cast from integer is probably a bad idea, seeing that it will break
in
2038. You could make an argument for a cast from double though. The
issue to my mind is whether this might be too Unix-centric.
In my mind, epoch is pretty Unix-centric. In IRC we often see people
who want to store timestamps in their db as strings or integers, which
we are sure to point out isn't necessarily the best way to take
advantage of PostgreSQL's strengths in handling timestamps.
Having these (corrected) functions available would be enough in my
mind. The casting idea came about when I was thinking about where I'd
put the functions in the documentation.
Michael Glaesemann
grzm myrealbox com
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2003-01-01 11:23:44');
Yeah, but I think Michael's question had to do with going the other
way (numeric to timestamp).
Sorry,
SELECT EXTRACT(TIMESTAMP FROM EPOCH '123412341234');
Chris
On Jul 16, 2004, at 3:08 PM, Christopher Kings-Lynne wrote:
SELECT EXTRACT(TIMESTAMP FROM EPOCH '123412341234');
That's a really interesting idea! Makes for a much more consistent
syntax for our other functions. ISTM this might require a native EPOCH
datatype. I wouldn't want to encourage people to use EPOCH as a
datatype though.
Michael Glaesemann
grzm myrealbox com