# Date and Time Operations
# Date arithmetic
NOW() + INTERVAL 1 DAY -- This time tomorrow CURDATE() - INTERVAL 4 DAY -- Midnight 4 mornings ago
Show the mysql questions stored that were asked 3 to 10 hours ago (180 to 600 minutes ago):
SELECT qId,askDate,minuteDiff FROM ( SELECT qId,askDate, TIMESTAMPDIFF(MINUTE,askDate,now()) as minuteDiff FROM questions_mysql ) xDerived WHERE minuteDiff BETWEEN 180 AND 600 ORDER BY qId DESC LIMIT 50; +----------+---------------------+------------+ | qId | askDate | minuteDiff | +----------+---------------------+------------+ | 38546828 | 2016-07-23 22:06:50 | 182 | | 38546733 | 2016-07-23 21:53:26 | 195 | | 38546707 | 2016-07-23 21:48:46 | 200 | | 38546687 | 2016-07-23 21:45:26 | 203 | | ... | | | +----------+---------------------+------------+
MySQL manual pages for
Beware Do not try to use expressions like
CURDATE() + 1 for date arithmetic in MySQL. They don't return what you expect, especially if you're accustomed to the Oracle database product. Use
CURDATE() + INTERVAL 1 DAY instead.
# SYSDATE(), NOW(), CURDATE()
This function returns the current date and time as a value in
'YYYY-MM-DD HH:MM:SS' or
YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. It returns the date and time in the current time zone.
This function is a synonym for
This function returns the current date, without any time, as a value in
YYYYMMDD format, depending on whether the function is used in a string or numeric context. It returns the date in the current time zone.
Shows the current server date and time.
Update `footable` set mydatefield = Now();
This will update the field
mydatefield with current server date and time in server's configured timezone, e.g.
# Testing against a date range
Although it is very tempting to use
AND ... for a date range, it is problematical. Instead, this pattern avoids most problems:
WHERE x >= '2016-02-25' AND x < '2016-02-25' + INTERVAL 5 DAY
BETWEENis 'inclusive' thereby including the final date or second.
23:59:59is clumsy and wrong if you have microsecond resolution on a
- This pattern avoid dealing with leap years and other data calculations.
- It works whether
# Extract Date from Given Date or DateTime Expression
SELECT DATE('2003-12-31 01:02:03');
The output will be:
# Using an index for a date and time lookup
Many real-world database tables have many rows with
TIMESTAMP column values spanning a lot of time, including years or even decades. Often it's necessary to use a
WHERE clause to retrieve some subset of that timespan. For example, we might want to retrieve rows for the date 1-September-2016 from a table.
An inefficient way to do that is this:
WHERE DATE(x) = '2016-09-01' /* slow! */
It's inefficient because it applies a function --
DATE() -- to the values of a column. That means MySQL must examine each value of
x, and an index cannot be used.
A better way to do the operation is this
WHERE x >= '2016-09-01' AND x < '2016-09-01' + INTERVAL 1 DAY
This selects a range of values of
x lying anywhere on the day in question, up until but not including (hence
<) midnight on the next day.
If the table has an index on the
x column, then the database server can perform a range scan on the index. That means it can quickly find the first relevant value of x, and then scan the index sequentially until it finds the last relevant value. An index range scan is much more efficient than the full table scan required by
DATE(x) = '2016-09-01.
Don't be tempted to use this, even though it looks more efficient.
WHERE x BETWEEN '2016-09-01' AND '2016-09-01' + INTERVAL 1 DAY /* wrong! */
It has the same efficiency as the range scan, but it will select rows with values of
x falling exactly at midnight on 2-Sept-2016, which is not what you want.