pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedworking with unix timestamp
4+ messages / 3 participants
[nested] [flat]
* working with unix timestamp
@ 2004-03-16 16:54 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 2 replies; 4+ messages in thread
From: Gary Stainburn @ 2004-03-16 16:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: working with unix timestamp
@ 2004-03-16 17:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-03-16 17:52 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: working with unix timestamp
@ 2004-03-16 17:56 Frank Finner <postgresql@finner.de>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 4+ messages in thread
From: Frank Finner @ 2004-03-16 17:56 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: working with unix timestamp
@ 2004-03-17 11:17 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Frank Finner <postgresql@finner.de>
0 siblings, 0 replies; 4+ messages in thread
From: Gary Stainburn @ 2004-03-17 11:17 UTC (permalink / raw)
To: Frank Finner <postgresql@finner.de>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-03-17 11:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-16 16:54 working with unix timestamp Gary Stainburn <gary.stainburn@ringways.co.uk>
2004-03-16 17:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-16 17:56 ` Frank Finner <postgresql@finner.de>
2004-03-17 11:17 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
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