Re: working with unix timestamp

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