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