From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: Frank Finner <postgresql@finner.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: working with unix timestamp
Date: Wed, 17 Mar 2004 11:17:16 +0000
Message-ID: <200403171117.16536.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <20040316185635.6189cb69.postgresql@finner.de>
References: <200403161654.18049.gary.stainburn@ringways.co.uk>
<20040316185635.6189cb69.postgresql@finner.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk, postgresql@finner.de
Subject: Re: working with unix timestamp
In-Reply-To: <200403171117.16536.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox