Content-Type: text/plain;
  charset="iso-8859-1"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: Frank Finner <postgresql@finner.de>
Subject: Re: working with unix timestamp
Date: Wed, 17 Mar 2004 11:17:16 +0000
User-Agent: KMail/1.4.3
Cc: pgsql-sql@postgresql.org
References: <200403161654.18049.gary.stainburn@ringways.co.uk>
	<20040316185635.6189cb69.postgresql@finner.de>
In-Reply-To: <20040316185635.6189cb69.postgresql@finner.de>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200403171117.16536.gary.stainburn@ringways.co.uk>

On Tuesday 16 March 2004 5:56 pm, Frank Finner wrote:
> On Tue, 16 Mar 2004 16:54:18 +0000 Gary Stainburn
> <gary.stainburn@ringways.co.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)=@_;
>     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