I'm trying to figure out why I'm having an inconsistency between a query executed in the mysql command line environment and the same query executed in a PHP 3.0.18 mysql_query() statement. Both mysql and php are running on a debian system.
I have a very simple table that contains a user id, an equipment id, a start time and an end time (it's a scheduling program).
Here's a sample of the data (sorry about the word wrap):
usr eq start end
132 5 2001-6-11 10:00:00 2001-6-11 11:00:00
132 5 2001-6-11 15:00:00 2001-6-11 18:00:00
132 5 2001-6-12 09:00:00 2001-6-12 10:00:00
132 5 2001-6-13 10:00:00 2001-6-13 11:00:00
132 5 2001-6-14 10:00:00 2001-6-14 11:00:00
now, if I submit the following query under the mysql environment (with the dates filtered to receive exactly the above records:
select sum((unix_timestamp(end) - unix_timestamp(start)) / 3600 ) as usedtime where usr = 132 and eq = 5 and start >= '2001-6-11' and end <= '2001-6-15' group by usr;
I get the right result of time used in hours:
seven.
However, if I connect to the database in php (which is working just fine) and execute _exactly_ the same query using mysql_query, my result is 158 hours.
I have been cutting and pasting queries back and forth from the two environments to make sure I'm not typoing anything. And every time it gives me a flawed answer in the php environment while the answer is correct in the mysql environment. A hand calculation confirms this.
Any help will be greatly appreciated.
I have a very simple table that contains a user id, an equipment id, a start time and an end time (it's a scheduling program).
Here's a sample of the data (sorry about the word wrap):
usr eq start end
132 5 2001-6-11 10:00:00 2001-6-11 11:00:00
132 5 2001-6-11 15:00:00 2001-6-11 18:00:00
132 5 2001-6-12 09:00:00 2001-6-12 10:00:00
132 5 2001-6-13 10:00:00 2001-6-13 11:00:00
132 5 2001-6-14 10:00:00 2001-6-14 11:00:00
now, if I submit the following query under the mysql environment (with the dates filtered to receive exactly the above records:
select sum((unix_timestamp(end) - unix_timestamp(start)) / 3600 ) as usedtime where usr = 132 and eq = 5 and start >= '2001-6-11' and end <= '2001-6-15' group by usr;
I get the right result of time used in hours:
seven.
However, if I connect to the database in php (which is working just fine) and execute _exactly_ the same query using mysql_query, my result is 158 hours.
I have been cutting and pasting queries back and forth from the two environments to make sure I'm not typoing anything. And every time it gives me a flawed answer in the php environment while the answer is correct in the mysql environment. A hand calculation confirms this.
Any help will be greatly appreciated.