December 18th, 2012, 06:08 PM
Date calculation in query
I have a database table full of events and dates and I'm trying to find the last occurrence and age of each event. (Age is obviously the time elapsed since the last occurrence)
So, I have the following query:
(This Query returns one row of data within a WHILE loop for every event. I think the "GROUP BY" is unnecessary)
A partial list of the results are as follows:
Event - Count - Last - Age
56 - 15 - 2012-10-27 - 191
59 - 13 - 2012-11-17 - 101
44 - 13 - 2012-12-15 - 3
29 - 13 - 2012-11-28 - 90
7 - 12 - 2012-12-08 - 10
5 - 12 - 2012-11-28 - 90
(the "Count" field is the number of occurrences coming from another query)
Today's date is 2012-12-18. For some reason, all of the December ages are correct but the older ones are WAY off.
What have I done wrong?
December 18th, 2012, 06:39 PM
So this table also stores dates in the future?
December 19th, 2012, 06:26 AM
Umm... no, I don't think so. The latest date is 2012-12-15, accurately shown as three days ago.
Originally Posted by cafelatte
Am I misreading something?
December 19th, 2012, 08:41 AM
your query subtracts (for instance) the integer 20121101 from 20121208 which (in this case) equals 107.
, MAX(date) date
, DATEDIFF(CURDATE(),MAX(date)) age