date_trunc
Description
Syntax:
DATETIME DATE_TRUNC(DATETIME datetime,VARCHAR unit)
This function truncates DATETIME based on the specified time unit.
datetime is a valid date expression.
unit is the time unit you want to truncate by. The optional values are as follows: [second,minute,hour,day,month,quarter,year].
If unit is not one of the above optional values, the function will return NULL.
Example
mysql> select date_trunc('2010-12-02 19:28:30', 'second');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'second') |
+-------------------------------------------------+
| 2010-12-02 19:28:30 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'minute');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'minute') |
+-------------------------------------------------+
| 2010-12-02 19:28:00 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'hour');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'hour') |
+-------------------------------------------------+
| 2010-12-02 19:00:00 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'day');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'day') |
+-------------------------------------------------+
| 2010-12-02 00:00:00 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'week');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'week') |
+-------------------------------------------------+
| 2010-11-28 00:00:00 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'month');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'month') |
+-------------------------------------------------+
| 2010-12-01 00:00:00 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'quarter');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'quarter') |
+-------------------------------------------------+
| 2010-10-01 00:00:00 |
+-------------------------------------------------+
mysql> select date_trunc('2010-12-02 19:28:30', 'year');
+-------------------------------------------------+
| date_trunc('2010-12-02 19:28:30', 'year') |
+-------------------------------------------------+
| 2010-01-01 00:00:00 |
+-------------------------------------------------+Keywords
DATE_TRUNC,DATE,DATETIME