Date and datetime fields are incredibly useful in MySQL databases, but can be a little tricky to use. Here are a few tips for common tasks
Selecting distinct dates from datetime
Select distinct dates from a table (NB use backtick ` not single quote ‘ - backtick is Unicode 0060, HTML `):
SELECT DISTINCT CAST(`foo_datetime` AS DATE) AS foo_date_only
FROM foo_table
ORDER BY foo_date_only
Selecting distinct year, month, from datetime
In order to select distinct year, month, or a combination (year-month), use the MySQL functions YEAR() and/or MONTH():
SELECT DISTINCT YEAR(foo_datetime), MONTH(foo_datetime)
FROM foo_table
Selecting and grouping
You can also count the number of records grouped by year/month:
SELECT YEAR(foo_datetime) AS foo_year, MONTH(foo_datetime) AS foo_month, COUNT(DISTINCT id)
FROM foo_table
GROUP BY foo_year, foo_month
Selecting between two datetimes
Selecting between two datetimes is relatively straightforward, using SELECT… BETWEEN:
SELECT * FROM foo_table
WHERE foo_date_col BETWEEN '2014-02-21 00:00:00' AND '2014-02-21 23:59:59'
Selecting all records from a single date
Whilst possible to select using the DATE() function, this isn’t an efficient way to select for a single date in a large table:
DATE(datecolumns) = '2012-12-24'
A more efficient way is to select between two datetimes, using the lower and upper limits of that day:
SELECT * FROM foo_table
WHERE foo_date_col BETWEEN '2014-02-21 00:00:00' AND '2014-02-21 23:59:59'