Member Avatar for glycerine

I hope the title made sense.

ok ive got a table in a PostgreSQL database that has a few coloumns in it. What im tryting to do is the following:

<?php 
$totaltimetodayresult = pg_query($link, "SELECT SUM(acctsessiontime) AS totaltime FROM radacct WHERE acctstarttime LIKE '%".$today."%'");
while($totaltimetodayarray = pgfetch_array($totaltimemonthresult)){
$totaltimetoday = $totaltimetodayarray['acctsessiontime'];
}
if($totaltimetoday == ""){
$totaltimetoday = 0;
}else{
echo "<li>Today - $".$totaltimetodayarray['totaltime']."</li>";
}
?>

Now this keeps coming back with it not liking the "LIKE" if I put single quotes around 'acctstarttime' it comes back with null.

acctsessiontime = timestamp with timezone (2010-07-27 09:08:27+12)
acctstarttime = bigint
$today = 2010-07-27

If anyone could shed some light or needs more info please ask

Cheers

  • 2 Contributors
  • 2 Replies
  • 591 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by glycerine

Recommended Answers

All 2 Replies

Member Avatar for ddymacek

it's because it is a bigint type.
some things to try.
remove your first %

...WHERE acctstarttime LIKE '".$today."%'");
// that should hopefully do the trick.
// since acctstarttime is a big int you can also 'CONVERT' when pulling the data.
// try:
$totaltimetodayresult = pg_query($link, "SELECT SUM(acctsessiontime) AS totaltime FROM radacct WHERE CONVERT(acctstarttime,char(25)) LIKE %".$today."%'");
Member Avatar for glycerine

Thanks for your post, I have however fixed the problem.

I used 'SELECT SUM(acctsessiontime) AS totaltime WHERE acctstarttime BETWEEN TIMESTAMP 'INSERT_DATE_HERE' AND TIMESTAMP 'NSERT_DATE_HERE' + INTERVAL '1 day''

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.