[postgis-users] OT: Coverting time_t to timestamp

[Paul Tomblin]

Michael Fuhr wrote:
> 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;
> 

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

That's worked very nicely.  Thanks.

-- 
Paul Tomblin <ptomblin at xcski.com> http://blog.xcski.com/
Unix is great.  The Unix culture is magnificent.  Life in a Unix without
the GNU utilities is the kind of hell I'd not wish on my worst enemy.
               -- Robert Uhl