How can we use interval?
delete from access_log where requestTime <= NOW() - interval 2 day
How can we determine the delta between two dates in minutes?
select *,now() as now, round((unix_timestamp(now()) - unix_timestamp(statusTimestamp)) / 60) as diff_minutes
from SubmitterStatus where statusTimestamp < now() - interval 40 minute order by diff_minutes DESC
How can we figure out the timezone offset of the server?
select unix_timestamp(utc_timestamp()) - unix_timestamp(now());
How can we determine the current date?
Use the CURDATE() function
Why is it a good practice to use the Date() function if what you want is just the date, even if you know that the column only contain dates?
SELECT id FROM orders WHERE Date(date) = '2005-09-01';
It is good practice to use Date() if what you want is just the date, even if you know that the column only contain dates. This way, if somehow a date time value ends up in the table in the future, your SQL will continue to work. The same applies to Time().
SELECT id FROM orders WHERE Date(date) BETWEEN '2005-09-01' AND '2005-09-30';
SELECT id FROM orders WHERE Year(date) = 2005 AND Month(date) = 9;
What are some available date / time functions?
Year(), Month(), Day(), Hour(), Minute(), Second(), DayOfWeek(), Date(), Time(), Now(), AddDate(), AddTime(), CurDate(), CurTime(), DateDiff(), Date_Add(), Date_Format().
