Re: working with unix timestamp - Mailing list pgsql-sql

From Gary Stainburn
Subject Re: working with unix timestamp
Date
Msg-id 200403171117.16536.gary.stainburn@ringways.co.uk
Whole thread Raw
In response to Re: working with unix timestamp  (Frank Finner <postgresql@finner.de>)
List 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.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     



pgsql-sql by date:

Previous
From: Richard Huxton
Date:
Subject: Re: Line length in pl/pgsql function
Next
From: "David Olbersen"
Date:
Subject: Re: Line length in pl/pgsql function