Date: Tue, 16 Mar 2004 18:56:35 +0100
From: Frank Finner <postgresql@finner.de>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: working with unix timestamp
Message-Id: <20040316185635.6189cb69.postgresql@finner.de>
In-Reply-To: <200403161654.18049.gary.stainburn@ringways.co.uk>
References: <200403161654.18049.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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.