Re: [SQL] unix time -> timestamp

[boyd]

In article <[EMAIL PROTECTED]>,
 boyd <[EMAIL PROTECTED]> wrote:

> I'm pretty sure in 7.0 postgres, (but I may be wrong) you could insert 
> directly from a perl script something like this:
>$time = time; # this gives epoch seconds
>$sql = "insert into mytable values(..., timestamp($time), ...) ";
> 
> And then the $sql string would run with the DBI call, etc.  
> 
> When I tried the same script on 7.2, it would not take it.  I think I 
> once found another way to do it in 7.2.  Of course, I can create a 
> formatted string in perl that will be accepted by the insert, but it 
> seems inefficient to have to do it through strings. 
> 
> Boyd tbmooreATbealenetDOTcom

Sorry - five minutes later, I found it -

You just use abstime as the function to convert, as in:

   "insert into mytable values(..., abstime($time), ...) ";

 Boyd tbmooreATbealenetDOTcom


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Different query results in psql and Perl DBI with Postgres 7.2.1

[boyd]

In article <[EMAIL PROTECTED]>,
 [EMAIL PROTECTED] (Brendan LeFebvre) wrote:

> I have a script that updates one record in a PostgreSQL 7.2.1 database
> through the Perl DBI.
> 

> Where do I even begin to attempt a diagnosis?

Here is the output from a script I ran with no problems under 7.2.1, 
followed by the script:

OUTPUT
-
DB info : PostgreSQL 7.2.1
 Table "item"
 Column  |  Type   | Modifiers 
-+-+---
 item_id | integer | 
 status  | text| 

doing query < TRUNCATE item > 
-1 rows affected
doing query < INSERT INTO item VALUES(1129832,'BACKORDER') > 
1 rows affected
doing query < INSERT INTO item VALUES(1130081,'OUTOFSTOCK') > 
1 rows affected
doing query < UPDATE item SET status='SOLD' WHERE item_id=1129832 > 
1 rows affected
doing query < UPDATE item SET status='STORE' WHERE item_id=1130081 > 
1 rows affected

SCRIPT
--
#!/usr/bin/perl -w

use strict;
use DBI;

our $DBH;
sub dienicely {
warn "$DBH->errstr";
$DBH->disconnect;
}

sub runquery {
my $sql = shift;
print "doing query < $sql > \n";
my $rows = $DBH->do($sql) or dienicely;
print "$rows rows affected\n";
}

$DBH = DBI->connect("dbi:Pg:dbname=boyd","","");
my $info = $DBH->get_info(17) .' '. $DBH->get_info(18);
$info =~ s/0//g; # the get_info adds extraneous '0' to the version number
print "DB info : $info\n";
print `psql -c '\\d item'`;

runquery("TRUNCATE item");

runquery( "INSERT INTO item VALUES(1129832,'BACKORDER')" );

runquery( "INSERT INTO item VALUES(1130081,'OUTOFSTOCK')" );

runquery( "UPDATE item SET status='SOLD' WHERE item_id=1129832" );

runquery( "UPDATE item SET status='STORE' WHERE item_id=1130081" );

$DBH->disconnect;

Hope that helps.
Boyd


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] unix time -> timestamp

[boyd]

I'm pretty sure in 7.0 postgres, (but I may be wrong) you could insert 
directly from a perl script something like this:
   $time = time; # this gives epoch seconds
   $sql = "insert into mytable values(..., timestamp($time), ...) ";

And then the $sql string would run with the DBI call, etc.  

When I tried the same script on 7.2, it would not take it.  I think I 
once found another way to do it in 7.2.  Of course, I can create a 
formatted string in perl that will be accepted by the insert, but it 
seems inefficient to have to do it through strings. 

Boyd tbmooreATbealenetDOTcom


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] max timestamp

[boyd (remove_) [EMAIL PROTECTED]]

Michael Sterling wrote:
i'm trying to get the max time stamp, from each day, of a range of
dates, not just the max time stamp for the complete range dates but
for each day.
I don't trust the news client I was using.  So will answer again from 
the Netscape version;  This worked for me:

I have a table, sis, with schema:
  Table "sis"
 Column |   Type   | Modifiers
+--+---
 time   | timestamp with time zone |
 stat10 | integer  |
 ipf10  | real |
 ipf30  | integer  |
select date_part ( 'doy',time ) as jday, max ( time ) from sis group by jday
(doy means day of year, which I called jday)
Boyd

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html