Developer Forum

I have two tables, one listing events, and another listing documents
published about the event. I would like to calculate the average lag
between event and publication for each year, and then present the data
grouped by year. I have got to this point, but the averages are coming
out way too large, so I assume that the average function isn't working:

SELECT AVG(tbldocuments.datDate - tblevents.datDate) AS timelag,
YEAR(tblevents.datDate) AS year
FROM tblevents
INNER JOIN tbldocuments
ON tblevents.eventID = tbldocuments.eventID
GROUP BY year
ORDER BY year ASC

Can anyone help me with this?

>I have two tables, one listing events, and another listing documents

I recommend you try this query:

SELECT tbldocuments.datDate,
tblevents.datDate,
tbldocuments.datDate - tblevents.datDate
FROM tblevents INNER JOIN tbldocuments
ON televents.eventID = tbldocuments.eventID;

and observe that subtracting two dates doesn't do what you think
it does, especially if the dates are in different months or years.
Now look at the datediff() function.

(a and b are date fields below.)
test> select a, b, a-b, datediff(a,b) from dates;
+------------+------------+------+---------------+
| a | b | a-b | datediff(a,b) |
+------------+------------+------+---------------+
| 2008-11-01 | 2008-10-31 | 70 | 1 |
| 2008-01-01 | 2007-12-31 | 8870 | 1 |
| 2008-11-02 | 2008-11-01 | 1 | 1 |
| 2000-01-01 | 1999-12-31 | 8870 | 1 |
+------------+------------+------+---------------+
4 rows in set (0.02 sec)

OK, I feel really stoopid now. I assumed that mysql dates behave like ms
excel dates. More fool me. Thanks very much.