Posted on

Queries to select rows based on time

Many applications need to find all rows before or after a specific date, or within a certain range. Of course you need to define a DATE, TIME, DATETIME or TIMESTAMP column in the table.

To find all rows where the date is within the last week:

  SELECT ... WHERE d >= (CURRENT_DATE - INTERVAL 7 DAY)

Easy, isn’t it? There are also keywords for CURRENT_TIME, CURRENT_TIMESTAMP, and also the commonly used NOW() which is a synonym for CURRENT_TIMESTAMP.
The INTERVAL clause can also be used with temporal entities other than DAY. The keyword is always specified in singular (i.e., DAY not DAYS).

For an overview of all temporal functions, see http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

To efficiently find all rows from a specific day if you have a DATETIME column:

  SELECT ... WHERE ts BETWEEN '2005-02-03 00:00:00' AND '2005-02-03 23:59:59'

If instead you did processing on the ts column to get the date separately, MySQL would’t be able to use an index for the lookup. Of course, in this case it would be more appropriate to use separate DATE and TIME columns instead. Always design your tables according to what your later queries need!

For an overview of all temporal column types and their use, see http://dev.mysql.com/doc/mysql/en/date-and-time-types.html

Naturally, you can also use such WHERE clauses in UPDATE and DELETE statements, for instance to delete old articles, or expire certain privileges.

Posted on