pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconverting an epoch to a timestamp
2+ messages / 2 participants
[nested] [flat]
* converting an epoch to a timestamp
@ 2002-01-10 01:25 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-01-10 01:59 ` Re: converting an epoch to a timestamp Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Christopher Kings-Lynne @ 2002-01-10 01:25 UTC (permalink / raw)
To: pgsql-sql
How does one go about converting a UNIX epoch (ie. int4) to a timestamp?
These don't work:
("datetime" is the int4 field)
extract(timestamp from epoch datetime)
extract(timestamp from datetime)
I'm using 7.1.3, but am also interested in 7.2 solutions. And if it isn't
part of extract, shouldn't it be?
Chris
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: converting an epoch to a timestamp
2002-01-10 01:25 converting an epoch to a timestamp Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-01-10 01:59 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2002-01-10 01:59 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-sql
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> How does one go about converting a UNIX epoch (ie. int4) to a timestamp?
I tend to rely on the binary equivalence between int4 and abstime:
regression=# select 1010626671::int4::abstime::timestamp;
timestamptz
------------------------
2002-01-09 20:37:51-05
(1 row)
which can be written in a less ugly-looking though equivalent fashion:
regression=# select "timestamptz"(1010626671);
timestamp
---------------------
2002-01-09 20:37:51-05
(1 row)
(In 7.1.3 you'd just write "timestamp" without the "tz". Note that the
quotes are not optional in 7.2.)
However, Thomas keeps threatening to remove type abstime. There are
more socially acceptable ways of doing it, eg
regression=# select 'epoch'::timestamptz + '1010626671 sec'::interval;
?column?
------------------------
2002-01-09 20:37:51-05
(1 row)
but this way does not work well when the integer in question is already
in the database, rather than being plugged into a literal query string
by an application. I think some people have recommended
regression=# select 'epoch'::timestamptz + "interval"(1010626671::int4);
?column?
------------------------
2002-01-17 20:37:51-05
(1 row)
but as far as I can see that's just an extra runtime operation without
any redeeming social value, because when you look under the hood at the
interval coercion, you find out that it's relying on binary equivalence
between int4 and reltime ... so this too will break if we get rid of the
obsolete time types.
Personally I'd like to see an officially supported int4-to-timestamp
(or perhaps better double-to-timestamp, to be Y2038-compliant and
fractional-second-capable) conversion function. Getting from Unix
timestamp to Postgres timestamp easily and reliably is an awfully useful
thing. And if we have EXTRACT(EPOCH ...) why shouldn't we have the
reverse conversion?
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-01-10 01:59 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-10 01:25 converting an epoch to a timestamp Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-01-10 01:59 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox