Date calculation in query

Dev Shed Forums Sponsor:

December 18th, 2012, 05:08 PM

SeanF

Contributing User

Join Date: May 2004

Location: Boston, MA USA

Posts: 317

Time spent in forums: 3 Days 6 h 11 m 38 sec
Reputation Power: 43

Date calculation in query

Hi

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:

Quote:

SELECT max(date) as last,
min(curdate()-date) AS age
FROM event_data
WHERE event = $event
GROUP BY event"

(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, 05:39 PM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

So this table also stores dates in the future?

December 19th, 2012, 05:26 AM

SeanF

Contributing User

Join Date: May 2004

Location: Boston, MA USA

Posts: 317

Time spent in forums: 3 Days 6 h 11 m 38 sec
Reputation Power: 43

Quote:

Originally Posted by cafelatte

So this table also stores dates in the future?

Umm... no, I don't think so. The latest date is 2012-12-15, accurately shown as three days ago.

Am I misreading something?

December 19th, 2012, 07:41 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

Code:

SELECT event
     , MAX(date) date
     , DATEDIFF(CURDATE(),MAX(date)) age 
  FROM event_data 
 GROUP 
    BY event;

your query subtracts (for instance) the integer 20121101 from 20121208 which (in this case) equals 107.

Viewing: Dev Shed Forums > Databases > MySQL Help > Date calculation in query

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump