Обсуждение: 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