date_trunc

date_trunc

Description

Syntax

DATETIME DATE_TRUNC(DATETIME datetime, VARCHAR unit)

Truncates datetime in the specified time unit.

datetime is a legal date expression.

unit is the time unit you want to truncate. The optional values are as follows: [second,minute,hour,day,week,month,quarter,year].

example

1. mysql> select date_trunc('2010-12-02 19:28:30', 'second');
2. +-------------------------------------------------+
3. | date_trunc('2010-12-02 19:28:30', 'second')     |
4. +-------------------------------------------------+
5. | 2010-12-02 19:28:30                             |
6. +-------------------------------------------------+

8. mysql> select date_trunc('2010-12-02 19:28:30', 'minute');
9. +-------------------------------------------------+
10. | date_trunc('2010-12-02 19:28:30', 'minute')     |
11. +-------------------------------------------------+
12. | 2010-12-02 19:28:00                             |
13. +-------------------------------------------------+

15. mysql> select date_trunc('2010-12-02 19:28:30', 'hour');
16. +-------------------------------------------------+
17. | date_trunc('2010-12-02 19:28:30', 'hour')       |
18. +-------------------------------------------------+
19. | 2010-12-02 19:00:00                             |
20. +-------------------------------------------------+

22. mysql> select date_trunc('2010-12-02 19:28:30', 'day');
23. +-------------------------------------------------+
24. | date_trunc('2010-12-02 19:28:30', 'day')        |
25. +-------------------------------------------------+
26. | 2010-12-02 00:00:00                             |
27. +-------------------------------------------------+

29. mysql> select date_trunc('2010-12-02 19:28:30', 'week');
30. +-------------------------------------------+
31. | date_trunc('2010-12-02 19:28:30', 'week') |
32. +-------------------------------------------+
33. | 2010-11-29 00:00:00                       |
34. +-------------------------------------------+

36. mysql> select date_trunc('2010-12-02 19:28:30', 'month');
37. +-------------------------------------------------+
38. | date_trunc('2010-12-02 19:28:30', 'month')      |
39. +-------------------------------------------------+
40. | 2010-12-01 00:00:00                             |
41. +-------------------------------------------------+

43. mysql> select date_trunc('2010-12-02 19:28:30', 'quarter');
44. +-------------------------------------------------+
45. | date_trunc('2010-12-02 19:28:30', 'quarter')    |
46. +-------------------------------------------------+
47. | 2010-10-01 00:00:00                             |
48. +-------------------------------------------------+

50. mysql> select date_trunc('2010-12-02 19:28:30', 'year');
51. +-------------------------------------------------+
52. | date_trunc('2010-12-02 19:28:30', 'year')       |
53. +-------------------------------------------------+
54. | 2010-01-01 00:00:00                             |
55. +-------------------------------------------------+

keywords

DATE_TRUNC,DATE,DATETIME