• Home
  • About
    • Richie's Blog photo

      Richie's Blog

      Science, data, biology, digital health, programming, tech

    • Learn More
    • Twitter
    • LinkedIn
    • Github
  • Posts
    • All Posts
    • All Tags
  • Projects

Using dates in MySQL

06 Dec 2014

Reading time ~1 minute

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 &#x0060):

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'


Like Tweet +1