date_trunc

date_trunc

description

Syntax

DATETIME DATE_TRUNC(DATETIME datetime, VARCHAR unit)

将datetime按照指定的时间单位截断。

datetime 参数是合法的日期表达式。

unit 参数是您希望截断的时间间隔，可选的值如下：[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('2023-4-05 19:28:30', 'week');
30. +-------------------------------------------+
31. | date_trunc('2023-04-05 19:28:30', 'week') |
32. +-------------------------------------------+
33. | 2023-04-03 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,TRUNC