Обсуждение: working with unix timestamp

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?
-- 
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     

Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> How can I insert the  integer timestamp in $timestamp into my table?

The "clean" way is

select 'epoch'::timestamptz + <integer> * '1 second'::interval;

for instance

regression=# select 'epoch'::timestamptz + 1079459165 * '1 second'::interval;       ?column?
------------------------2004-03-16 12:46:05-05
(1 row)

The "dirty" way is to rely on abstime being binary-compatible with int4:

regression=# select 1079459165::abstime::timestamptz;     timestamptz
------------------------2004-03-16 12:46:05-05
(1 row)

This is probably a tad faster, but abstime is deprecated and will
disappear sometime before Y2038 becomes an issue.  Also, this *only*
works for integers, whereas the other way handles fractional seconds
just fine.

BTW, the reverse transformation is extract(epoch):

regression=# select extract(epoch from '2004-03-16 12:46:05-05'::timestamptz);date_part
------------1079459165
(1 row)

Note that I have been careful to work with timestamp with time zone
(timestamptz) here.  If you work with timestamp without time zone,
your results will be off by your GMT offset.
        regards, tom lane

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.

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