MySQL DATETIME question
I have a DATETIME field. I would like to select all the records that have been updated in the last week. I am currently using this query:
SELECT SUM(mins_spent) as time_sum FROM todos WHERE
lastUpdate >= '2008-12-22' AND lastUpdate <='2008-12-28'
But the results I get seem to be different depending on the time of day, for example at 7pm I can get 17 hours, and at 11pm I get 14 hours, although it should increase, not decrease. I was thinking to change the query:
SELECT SUM(mins_spent) as time_sum FROM todos WHERE
lastUpdate >= '2008-12-22 00:00:00' AND lastUpdate <='2008-12-28 00:00:00'
Will this help at all? Suggestions please.
source to share
'2008-12-22'
should equal '2008-12-22 00:00:00'
.
Do you want "before the end of the day?" If so, add 23:59:59
to the second date.
Alternatively, you can use lastUpdate < "2008-12-29"
.
How do you track changes to an existing ToDo? INSERT
and / or DELETE
? Or UPDATE
?
If you did DELETE
"entries" after completion, "you will have fewer entries.
If you're UPDATE
'ing, can you change the dates to the current week? If so, they will be removed from your results.
To see what's going on, you can try grabbing multiple aggregates in the table, mins_spent
and lastUpdate
(mark the values and watch them change from time to time).
SELECT count(*) AS Total
FROM todos
WHERE lastUpdate >= '2008-12-22' AND lastUpdate <= '2008-12-28 23:59:59'
SELECT min(mins_spent) AS Min, max(mins_spent) AS Max, avg(mins_spent) AS Avg
FROM todos
WHERE lastUpdate >= '2008-12-22' AND lastUpdate <= '2008-12-28 23:59:59'
SELECT min(lastUpdate) AS Min, max(lastUpdate) AS Max
FROM todos
WHERE lastUpdate >= '2008-12-22' AND lastUpdate <= '2008-12-28 23:59:59'
source to share
Judging by the MySQL 6.0 Reference for "Date and Time Types" , you should be more or less OK as written. However, if that doesn't really work, you may have encountered a reported bug.
The DATE value is enforced on the DATETIME type by adding the time portion as '00: 00: 00 '. To perform a comparison ignoring the time portion of the DATETIME value, use the CAST () function to perform the comparison as follows:
date_col = CAST(NOW() as DATE);
Your queries say '<=' 2008-12-28 '; it must be strictly less than not less than or equal.
One very subtle point would be whether you write the expressions you write as DATETIME (because LHS is a DATETIME column) or DATE (because RHS is best thought of as DATE), or, indeed, DATETIME is converted to a string according to RHS ? It will make a difference if the LHS converts to DATE, because any time on the 28th would be in the range, whereas you seem to want everything from the 21st to the 27th. What happens if you explicitly capture the types with butts?
Also, is the MySQL server running in the same time zone as you? Seemed like the difference between what you saw and the nominal end of the day was "out of time"? I'm still not sure how this might (incorrectly) work, but it might otherwise be an overlooked factor.
source to share