From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: working with unix timestamp
Date: Tue, 16 Mar 2004 12:52:26 -0500
Message-ID: <26054.1079459546@sss.pgh.pa.us> (raw)
In-Reply-To: <200403161654.18049.gary.stainburn@ringways.co.uk>
References: <200403161654.18049.gary.stainburn@ringways.co.uk>
Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> How can I insert the integer timestamp in $timestamp into my table?
The "clean" way is
select 'epoch'::timestamptz + <integer> * '1 second'::interval;
for instance
regression=# select 'epoch'::timestamptz + 1079459165 * '1 second'::interval;
?column?
------------------------
2004-03-16 12:46:05-05
(1 row)
The "dirty" way is to rely on abstime being binary-compatible with int4:
regression=# select 1079459165::abstime::timestamptz;
timestamptz
------------------------
2004-03-16 12:46:05-05
(1 row)
This is probably a tad faster, but abstime is deprecated and will
disappear sometime before Y2038 becomes an issue. Also, this *only*
works for integers, whereas the other way handles fractional seconds
just fine.
BTW, the reverse transformation is extract(epoch):
regression=# select extract(epoch from '2004-03-16 12:46:05-05'::timestamptz);
date_part
------------
1079459165
(1 row)
Note that I have been careful to work with timestamp with time zone
(timestamptz) here. If you work with timestamp without time zone,
your results will be off by your GMT offset.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, gary.stainburn@ringways.co.uk
Subject: Re: working with unix timestamp
In-Reply-To: <26054.1079459546@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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