Date and Time Functionsedit

Warning

This functionality is experimental and may be changed or removed completely in a future release. Elastic will take a best effort approach to fix any issues, but experimental features are not subject to the support SLA of official GA features.

  • Extract the year from a date (YEAR)
SELECT YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS year;

     year
---------------
2018
  • Extract the month of the year from a date (MONTH_OF_YEAR or MONTH)
SELECT MONTH_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS month;

     month
---------------
2
  • Extract the week of the year from a date (WEEK_OF_YEAR or WEEK)
SELECT WEEK_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS week;

     week
---------------
8
  • Extract the day of the year from a date (DAY_OF_YEAR or DOY)
SELECT DAY_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50
  • Extract the day of the month from a date (DAY_OF_MONTH, DOM, or DAY)
SELECT DAY_OF_MONTH(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
19
  • Extract the day of the week from a date (DAY_OF_WEEK or DOW). Monday is 1, Tuesday is 2, etc.
SELECT DAY_OF_WEEK(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
1
  • Extract the hour of the day from a date (HOUR_OF_DAY or HOUR). Monday is 1, Tuesday is 2, etc.
SELECT HOUR_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS hour;

     hour
---------------
10
  • Extract the minute of the day from a date (MINUTE_OF_DAY).
SELECT MINUTE_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS minute;

    minute
---------------
623
  • Extract the minute of the hour from a date (MINUTE_OF_HOUR, MINUTE).
SELECT MINUTE_OF_HOUR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS minute;

    minute
---------------
23
  • Extract the second of the minute from a date (SECOND_OF_MINUTE, SECOND).
SELECT SECOND_OF_MINUTE(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS second;

    second
---------------
27
  • Extract

As an alternative, one can support EXTRACT to extract fields from datetimes. You can run any datetime function with EXTRACT(<datetime_function> FROM <expression>). So

SELECT EXTRACT(DAY_OF_YEAR FROM CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50

is the equivalent to

SELECT DAY_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50