Skip site navigation (1) Skip section navigation (2)

Re: working with unix timestamp

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: Frank Finner <postgresql(at)finner(dot)de>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: working with unix timestamp
Date: 2004-03-17 11:17:16
Message-ID: 200403171117.16536.gary.stainburn@ringways.co.uk (view raw or flat)
Thread:
Lists: pgsql-sql
On Tuesday 16 March 2004 5:56 pm, Frank Finner wrote:
> On Tue, 16 Mar 2004 16:54:18 +0000 Gary Stainburn
> <gary(dot)stainburn(at)ringways(dot)co(dot)uk> sat down, thought
>
> long and then wrote:
> > Hi folks.
> >
> > I've got a last_updated field on my stock records of type timestamp.
> >
> > This last_updated field I get using the perl code:
> >
> > my $timestamp=(stat "$localcsv/VehicleStock.$data_suffix")[10];
> >
> > How can I insert the  integer timestamp in $timestamp into my table?
>
> I usually use somethinge like the following little function for getting an
> ISO timestamp. The result is suitable for a PostgreSQL timestamp field
> (without special timezone).
>
> # Subroutine for ISO-Timestamp
> sub mydatetime
>   {
>     my ($time)=(at)_;
>     my ($sec,$min,$hou,$mday,$mon,$yea,$wday,$jday,$sz)=localtime($time);
>     if ($sec < 10) {$sec="0".$sec;}
>     if ($min < 10) {$min="0".$min;}
>     if ($hou < 10) {$hou="0".$hou;}
>     if ($mday < 10) {$mday="0".$mday;}
>     $mon++;
>     if ($mon < 10) {$mon="0".$mon;}
>     $yea=$yea+1900;
>     my $t=$yea."-".$mon."-".$mday." ".$hou.":".$min.":".$sec;
>     return $t;
>   }
>
> Regards, Frank.

Thanks Frank,

My code's not as padantic, but produces a string hat is acceptable to 
Postgrresql.

my $timestamp=(stat "$localcsv/VehicleStock.$data_suffix")[9];
my ($sec,$min,$hour,$mday,$mon,$year) =localtime($timestamp);
$year+=1900;
$mon++;
$timestamp="$year-$mon-$mday $hour:$min:$sec";

However, I think I'll use Tom's suggestion and do the conversion in SQL.

Gary
-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     


In response to

pgsql-sql by date

Next:From: David OlbersenDate: 2004-03-17 19:50:44
Subject: Re: Line length in pl/pgsql function
Previous:From: Richard HuxtonDate: 2004-03-17 09:27:50
Subject: Re: Line length in pl/pgsql function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group