Thread: 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