what is this query outputting?
So, I want to run a quick query to give me some information on times taken to send messages.
my plan is
- count how many messages we are sending
- work out the difference between the max and the min datetime for the messages
- work out how much time it took per message.
So my query ended up being
SELECT
COUNT(id) AS message_sent,
TIMEDIFF(MAX(msg_sent_datetime), MIN(msg_sent_datetime)) AS total_time_taken,
COUNT(id)/TIMEDIFF(MAX(msg_sent_datetime), MIN(msg_sent_datetime)) AS time_per_message
FROM sent_txts_none_action_child
GROUP BY parent_id;
The first two sections work fine.
You run into trouble with number three.Example output.
message_sent|total_time_taken|time_per_message 5 |00:00:01 |5.0000000000 5 |00:00:00 |NULL 5 |00:00:01 |5.0000000000 7647 |00:28:12 |2.7194167852
What is the third column outputting?
What do I need to change to get it to output the correct value?Edit
ok so after making the suggested change in the comment I get
message_sent|total_time_taken|time_per_message 5 |00:00:01 |0.2000000000 5 |00:00:00 |0.0000000000 5 |00:00:01 |0.2000000000 7647 |00:28:12 |0.3677259056
which still is not what we want.
as if you times0.3677259056
by 7647
you get 2812
.
开发者_开发知识库So whats the issue?
MYSQL is not converting the datediff value to anything logical,instead it is treating it as a simple number and ignoring any non numeric characters...
Why does mysql not convert this to seconds/milliseconds?
Is there a function I can use to do this?
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff
TIMEDIFF() returns a time value. However, when you perform any numeric maths on it, it turns into a numeric value that is useless (just mashing the digits from the time in textual form).
work out how much time it took per message.
So you really mean, time / message-count
. The divisor should be the count.
What you got is the inverse, which is messages that could be sent per unit time, which is seconds. In other words, what you calculated is how many messages could be sent per second, on average.
SELECT
COUNT(id) AS message_sent,
TIMEDIFF(MAX(msg_sent_datetime), MIN(msg_sent_datetime)) AS total_time_taken,
TIME_TO_SEC(TIMEDIFF(MAX(msg_sent_datetime), MIN(msg_sent_datetime)))/COUNT(id) AS time_per_message
FROM sent_txts_none_action_child
GROUP BY parent_id;
The resultant time_per_message
column is measured in seconds.
Reference: TIME_TO_SEC()
精彩评论