MySQL DateTime functions

mysql

How can we use interval?

delete from access_log where requestTime <= NOW() - interval 2 day

How to 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().

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License