[postgis-users] OT: Coverting time_t to timestamp

[Michael Fuhr]

On Fri, Apr 04, 2008 at 10:03:10PM -0400, Paul Tomblin wrote:
> Sorry about the off topic posting, but does anybody know a cleaner way to 
> convert a Unix time_t date to timestamp than
> 	select '1970-01-01'::timestamp + interval '1207360519 seconds';

The above is wrong unless you always want UTC.  To get the correct
time in your local time zone use timestamptz instead of timestamp
and use 'epoch' as the base time:

select 'epoch'::timestamptz + '1207360519 seconds'::interval;

If you have the time_t value in an application variable and you're
using a prepared statement then you could do this (example shown
in Perl):

my $time = 1207360519;
my $sth = $dbh->prepare("select 'epoch'::timestamptz + ? * '1 second'::interval as tstamp");
$sth->execute($time);

Offhand I can't think of a simpler way without doing the conversion
in application code.

-- 
Michael Fuhr