IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.

« Aggregate Functions Full-Text Search Functions »

› › ›

Date and Time Functions

edit

Date and Time Functions

edit

This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview 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

« Aggregate Functions Full-Text Search Functions »