A MySQL horror
-
In a MySQL table, the start and en time of using a service are recorded. I tried to get the sum of usage per user and day:
SELECT OriginationName, Date(StartTime), count(*), sum(timediff(endtime,starttime)) as duration
FROM ippbxcdr
Group by OriginationName, Date(StartTime)
order by OriginationName, Date(StartTime)With a start time of
'2012-08-21 15:02:29'and an end time of'2012-08-21 15:03:59', that duration of 1 minute 30 seconds was shown as130.0000. No, no, no: thou shalt not usesumwithtimediff! The Lord of MySQL giveth no error message, he punisheth with a wrong result.X| :sigh: :doh: :wtf: :~ :omg: :(( :(
I think computer viruses should count as life. I think it says something about human nature that the only form of life we have created so far is purely destructive. We've created life in our own image. Stephen Hawking
-
In a MySQL table, the start and en time of using a service are recorded. I tried to get the sum of usage per user and day:
SELECT OriginationName, Date(StartTime), count(*), sum(timediff(endtime,starttime)) as duration
FROM ippbxcdr
Group by OriginationName, Date(StartTime)
order by OriginationName, Date(StartTime)With a start time of
'2012-08-21 15:02:29'and an end time of'2012-08-21 15:03:59', that duration of 1 minute 30 seconds was shown as130.0000. No, no, no: thou shalt not usesumwithtimediff! The Lord of MySQL giveth no error message, he punisheth with a wrong result.Ooouch!!! :omg:
-
In a MySQL table, the start and en time of using a service are recorded. I tried to get the sum of usage per user and day:
SELECT OriginationName, Date(StartTime), count(*), sum(timediff(endtime,starttime)) as duration
FROM ippbxcdr
Group by OriginationName, Date(StartTime)
order by OriginationName, Date(StartTime)With a start time of
'2012-08-21 15:02:29'and an end time of'2012-08-21 15:03:59', that duration of 1 minute 30 seconds was shown as130.0000. No, no, no: thou shalt not usesumwithtimediff! The Lord of MySQL giveth no error message, he punisheth with a wrong result.Horrifying
-
In a MySQL table, the start and en time of using a service are recorded. I tried to get the sum of usage per user and day:
SELECT OriginationName, Date(StartTime), count(*), sum(timediff(endtime,starttime)) as duration
FROM ippbxcdr
Group by OriginationName, Date(StartTime)
order by OriginationName, Date(StartTime)With a start time of
'2012-08-21 15:02:29'and an end time of'2012-08-21 15:03:59', that duration of 1 minute 30 seconds was shown as130.0000. No, no, no: thou shalt not usesumwithtimediff! The Lord of MySQL giveth no error message, he punisheth with a wrong result.¿ Are you sure you haven't multiple records with the same values ? When you have 100 records with these values......you'll have this result.
-
In a MySQL table, the start and en time of using a service are recorded. I tried to get the sum of usage per user and day:
SELECT OriginationName, Date(StartTime), count(*), sum(timediff(endtime,starttime)) as duration
FROM ippbxcdr
Group by OriginationName, Date(StartTime)
order by OriginationName, Date(StartTime)With a start time of
'2012-08-21 15:02:29'and an end time of'2012-08-21 15:03:59', that duration of 1 minute 30 seconds was shown as130.0000. No, no, no: thou shalt not usesumwithtimediff! The Lord of MySQL giveth no error message, he punisheth with a wrong result.Your punishment is accord to your sins, long live to our migthy and fair MySQL Lord! :laugh:
CEO at: - Rafaga Systems - Para Facturas - Modern Components for the moment...
-
In a MySQL table, the start and en time of using a service are recorded. I tried to get the sum of usage per user and day:
SELECT OriginationName, Date(StartTime), count(*), sum(timediff(endtime,starttime)) as duration
FROM ippbxcdr
Group by OriginationName, Date(StartTime)
order by OriginationName, Date(StartTime)With a start time of
'2012-08-21 15:02:29'and an end time of'2012-08-21 15:03:59', that duration of 1 minute 30 seconds was shown as130.0000. No, no, no: thou shalt not usesumwithtimediff! The Lord of MySQL giveth no error message, he punisheth with a wrong result.I have renamed your thread appropriately!
-
In a MySQL table, the start and en time of using a service are recorded. I tried to get the sum of usage per user and day:
SELECT OriginationName, Date(StartTime), count(*), sum(timediff(endtime,starttime)) as duration
FROM ippbxcdr
Group by OriginationName, Date(StartTime)
order by OriginationName, Date(StartTime)With a start time of
'2012-08-21 15:02:29'and an end time of'2012-08-21 15:03:59', that duration of 1 minute 30 seconds was shown as130.0000. No, no, no: thou shalt not usesumwithtimediff! The Lord of MySQL giveth no error message, he punisheth with a wrong result.You figured out to use timestampdiff(SECOND, time1, time2)?
-
In a MySQL table, the start and en time of using a service are recorded. I tried to get the sum of usage per user and day:
SELECT OriginationName, Date(StartTime), count(*), sum(timediff(endtime,starttime)) as duration
FROM ippbxcdr
Group by OriginationName, Date(StartTime)
order by OriginationName, Date(StartTime)With a start time of
'2012-08-21 15:02:29'and an end time of'2012-08-21 15:03:59', that duration of 1 minute 30 seconds was shown as130.0000. No, no, no: thou shalt not usesumwithtimediff! The Lord of MySQL giveth no error message, he punisheth with a wrong result.Another good reason to buy SQL Server, or ORACLE, etc. Anyone who relies on MySql to run their vital business infrastructure deserves no less than burning at the stake.
-
You figured out to use timestampdiff(SECOND, time1, time2)?
Yes, I found a solution:
SEC_TO_TIME( sum( time_to_sec(endtime)-time_to_sec(starttime))) as Duration
Looks complicated, but it works.
-
Another good reason to buy SQL Server, or ORACLE, etc. Anyone who relies on MySql to run their vital business infrastructure deserves no less than burning at the stake.
Michael K Gray wrote:
Another good reason to buy SQL Server, or ORACLE, etc.
That's plain wrong. It all depends on the business model and on the app using the database. Besides, MySQL does have its strengths - it beats any commercial offering from MS or Oracle when 90% of the workload is reads performed by a server with limited hardware (depending on the table type being used).
-
Yes, I found a solution:
SEC_TO_TIME( sum( time_to_sec(endtime)-time_to_sec(starttime))) as Duration
Looks complicated, but it works.
Maybe time diff is using some sort of fixed place BCD storage scheme. If you span a year does it produce a value out in the thousands? Something like: yyyymmddhhmmss or dddddddhhmmss I worked on a database once where dates were stored like: yyyymmdd. Everything collated correctly and it was easy to extract the field you wanted.
-
Another good reason to buy SQL Server, or ORACLE, etc. Anyone who relies on MySql to run their vital business infrastructure deserves no less than burning at the stake.