Thread: Epoch to timestamp conversion function patch

Please find attached two patches (one for pg_proc.h and another for
supporting documentation) for two SQL functions:
epoch_to_timestamp(integer) and epoch_to_timestamptz(double precision),
which convert from UNIX epoch to the native PostgreSQL timestamp and
timestamptz data types. The equivalent SQL code is

create function epoch_to_timestamp(integer)
    returns timestamp
    language sql as '
    select (\'epoch\'::timestamptz + $1 * \'1
second\'::interval)::timestamp
    ';

create function epoch_to_timestamptz(double precision)
    returns timestamptz
    language sql as '
    select (\'epoch\'::timestamp + $1 * \'1 second\'::interval) at time
zone \'UTC\'
    ';

Some very simple tests (all should return TRUE):

test=# select epoch_to_timestamp(extract(epoch from
current_timestamp)::integer) = current_timestamp::timestamp(0);
  ?column?
----------
  t
(1 row)

test=# select epoch_to_timestamptz(extract(epoch from
current_timestamp)::integer) = current_timestamp(0);
  ?column?
----------
  t
(1 row)

test=# select epoch_to_timestamptz(extract(epoch from
current_timestamp)) = current_timestamp;
  ?column?
----------
  t
(1 row)

If regression tests are desired, I'll work some up. Any feedback
appreciated.

Michael Glaesemann
grzm myrealbox com

Michael Glaesemann <grzm@myrealbox.com> writes:
> Please find attached two patches (one for pg_proc.h and another for
> supporting documentation) for two SQL functions:
> epoch_to_timestamp(integer) and epoch_to_timestamptz(double precision),
> which convert from UNIX epoch to the native PostgreSQL timestamp and
> timestamptz data types. The equivalent SQL code is

Why wouldn't these both take double precision?

Actually I think epoch_to_timestamp as such is just plain unnecessary.
I'd suggest just one function epoch_to_timestamp that actually yields
timestamptz, and then if casting the result to timestamp is needed it'll
happen automatically.

            regards, tom lane

On Aug 5, 2004, at 2:03 AM, Tom Lane wrote:
> I'd suggest just one function epoch_to_timestamp that actually yields
> timestamptz, and then if casting the result to timestamp is needed
> it'll
> happen automatically.

That makes sense.

Chris mentioned the possibility of using the MySQL FROM_UNIXTIME()
syntax instead of making something new.

<http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html>

I haven't checked the SQL spec, but I suspect they don't specify this
function. Might be nice to make it consistent with another
implementation rather than making new syntax to do the same thing. I
don't know whether Oracle (or DB2?) might have similar functions that
might at some time in the future make their way into the spec. Skimming
through the Oracle documentation and searching for similar
functionality in DB2 and Oracle on the web leads me to think they
*don't* currently have a function to do this directly. Anyone familiar
with DB2 or Oracle know if this is in fact the case?

One drawback would be that people might expect additional
functionality. For example, MySQL FROM_UNIXTIME(integer) works
similarly to epoch_to_timestamp(double). However, there's also a
FROM_UNIXTIME(integer, format) function that I really don't think would
be needed. Using a different syntax would call attention to this
difference.

Another idea would be to overload TO_TIMESTAMP to take a single double
precision float parameter rather than two text parameters.

Michael Glaesemann
grzm myrealbox com

Michael Glaesemann <grzm@myrealbox.com> writes:
> Another idea would be to overload TO_TIMESTAMP to take a single double
> precision float parameter rather than two text parameters.

Offhand I'd favor this one.

I think borrowing the function name from MySQL would be asking for
trouble: there's nothing worse than a function that looks compatible
with someone else's but isn't quite.  I don't even understand what
FROM_UNIXTIME(integer, format) is supposed to do --- there are not
multiple formats of Unix timestamps --- so that leads me to be a little
skeptical that their idea of FROM_UNIXTIME(integer) works quite like
ours would, either.  (Plus the integer vs float distinction is not
trivial.)

            regards, tom lane