MySQL unix_timestamp woes... 2

[vza]

I have a MySQL query used to compute the total time a device is within a expected location. The loglocations table contains a history of every device's location history within the DB. the starttime/endtime are datetime fields which represent how long a device is in a partcular location for that time duration. The macaddress is the ID of the device and is comes from the loghistories table. Both tables are linked by the historyId and uuid respectively:

SELECT (SUM(UNIX_TIMESTAMP(ll.endtime) - UNIX_TIMESTAMP(ll.starttime)) AS DEVICE_TIME FROM loghistories lh INNER JOIN loglocations ll ON (lh.uuid = ll.historyId) WHERE lh.macaddress = ? and ll.locationName = ? and ll.starttime >= ? and ll.endtime <= ?

all the ? marks represent user-inserted information. This query works fine except for in one instance: Some devices have a 'NULL' endtime because the endtime of the device has yet to be determined (still in same location). This causes the sum section of the query to equate to NULL which is of no use to me...is there any way I could somehow append the current datetime value (using NOW() function for instance) for all devices which contain a 'NULL' endtime??

Any responses would be greatly appreciated.

Thanks
-vza

 

[TonyGroves]

You could replace "ll.endtime" with something like GREATEST(ll.endtime,NOW()) .

 

[vza]

I tried the replacement but still no go.

-vza

 

[Kozusnik]

I would suggest an if(ll.endtime){code} logic, but I don't know what language you're using. I didn't think you were doing this directly in sql.

Mark

 

[r937]

...is there any way I could somehow append the current datetime value (using NOW() function for instance) for all devices which contain a 'NULL' endtime??

there sure is

select sum(unix_timestamp(
                 coalesce(ll.endtime,now())
         - unix_timestamp(ll.starttime)) 
          as DEVICE_TIME 
  from loghistories lh 
inner 
  join loglocations ll 
    on (lh.uuid = ll.historyid)
 where lh.macaddress = ? 
   and ll.locationName = ? 
   and ll.starttime >= ? 
   and coalesce(ll.endtime,now()) <= ?

r937.com | rudy.ca

 

[vza]

r937,

Sorry for the delay....I appreciate the response...

select sum(unix_timestamp(
coalesce(ll.endtime,now())
- unix_timestamp(ll.starttime))
as DEVICE_TIME
from loghistories lh
inner
join loglocations ll
on (lh.uuid = ll.historyid)
where lh.macaddress = '00:02:2D:A5:F1:E6'
and ll.locationName = 'Services*'

I was able to run this protion of the query and get a value:

mysql> source sum.sql
+-------------+
| DEVICE_TIME |
+-------------+
| 3925894 |
+-------------+

When I added Dates however I got a null value returned...

select sum(unix_timestamp(coalesce(ll.endtime, now()))- unix_timestamp(ll.starttime))
as DEVICE_TIME
from loghistories lh
inner
join loglocations ll
on (lh.uuid = ll.historyid)
where lh.macaddress = '00:02:2D:A5:F1:E6'
and ll.locationName = 'Services*'
and ll.starttime >= '2005-06-15 00:00:00'
and ll.endtime <= '2005-06-16 00:00:00'


mysql> source sum.sql
+-------------+
| DEVICE_TIME |
+-------------+
| NULL |
+-------------+

Why would the inserted dates cause the query to fail?

I appreciate all the help with this matter.

-vza

 

[r937]

Why would the inserted dates cause the query to fail?

it didn't fail, it probably just couldn't find any rows to satisfy the WHERE conditions, so the SUM() had nothing to sum


i would look at this --

ll.locationName = 'Services*'

possibly you wanted to use LIKE there?



r937.com | rudy.ca