Thread: Convert "epoch" to timestamp

jeff=# select version();
                              version

-------------------------------------------------------------------
 PostgreSQL 7.2 on i386-portbld-freebsd4.5, compiled
by GCC 2.95.3
(1 row)

I need to convert the "epoch" (i.e. number of seconds)
timestamp to a PostgreSQL timestamp.

The extract/date_part functions do it easily the other
way, but I have had some difficulty in doing what I
need.

According to posts in the archives, the following used
to work:

jeff=# select extract (epoch from
current_timestamp(0));
 date_part
------------
 1019571752
(1 row)

jeff=# select timestamp(1019571752);
ERROR:  TIMESTAMP(1019571752) WITH TIME ZONE precision
must be between 0 and 13
jeff=#

I assume this change came with the other date/time
changes in version 7.2.

The following works:

jeff=# select abstime(1019571752);
        abstime
------------------------
 2002-04-23 09:22:32-05
(1 row)

Can I rely on this continuing to work?  Apparently
not, because the docs say (wrt "abstime" and
"reltime"): "Any or all of these internal types might
disappear in a future release."

Is there another/better way?

__________________________________________________
Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more
http://games.yahoo.com/

On Tue, 23 Apr 2002, Jeff Eckermann wrote:

> jeff=# select version();
>                               version
>
> -------------------------------------------------------------------
>  PostgreSQL 7.2 on i386-portbld-freebsd4.5, compiled
> by GCC 2.95.3
> (1 row)
>
> I need to convert the "epoch" (i.e. number of seconds)
> timestamp to a PostgreSQL timestamp.
>
> The extract/date_part functions do it easily the other
> way, but I have had some difficulty in doing what I
> need.
>
> According to posts in the archives, the following used
> to work:
>
> jeff=# select extract (epoch from
> current_timestamp(0));
>  date_part
> ------------
>  1019571752
> (1 row)
>
> jeff=# select timestamp(1019571752);
> ERROR:  TIMESTAMP(1019571752) WITH TIME ZONE precision
> must be between 0 and 13

You'd need to double quote that timestamp to differentiate
the function from the data type: "timestamp"(1019571752)

...
> Can I rely on this continuing to work?  Apparently
> not, because the docs say (wrt "abstime" and
> "reltime"): "Any or all of these internal types might
> disappear in a future release."

Right. But it won't disappear from the version you are running ;)

The following also works:

lockhart=# select timestamp without time zone 'today',
lockhart=> timestamp without time zone '1970-01-01'
lockhart=> + cast('1019520000' as interval);
      timestamp      |      ?column?
---------------------+---------------------
 2002-04-23 00:00:00 | 2002-04-23 00:00:00

I'm not sure what other features for integer->timestamp conversions may
be available in the future, but the above conforms to SQL9x standards so
is likely to be a good choice...

                     - Thomas

Jeff Eckermann <jeff_eckermann@yahoo.com> writes:
> According to posts in the archives, the following used
> to work:

> jeff=# select timestamp(1019571752);
> ERROR:  TIMESTAMP(1019571752) WITH TIME ZONE precision
> must be between 0 and 13
> jeff=#

Still works if you quote it:

regression=# select "timestamp"(1019571752);
      timestamp
---------------------
 2002-04-23 10:22:32
(1 row)

regression=# select "timestamptz"(1019571752);
      timestamptz
------------------------
 2002-04-23 10:22:32-04
(1 row)

However, the cast to abstime may be preferable, since Thomas is unlikely
to keep fiddling with the syntax and semantics of that type ;-) ;-).

Eventually there should be an exact converse of the extract(epoch)
functionality, rather than these various kluges relying on
abstime-to-integer equivalence.

We'll probably start thinking about removing abstime when Unix systems
start to migrate away from 32-bit time_t, which one hopes will happen
well before the year 2038.  So eventually something's got to be done.

            regards, tom lane