New

The executive guide to generative AI

Read more
About usPartnersSupport|Login
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 »
Elastic Docs ›Elasticsearch Guide [6.5] ›SQL Access ›Functions and Operators

Date and Time Functions

edit
IMPORTANT: This documentation is no longer updated. Refer to Elastic's version policy and the latest documentation.

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 »
Was this helpful?
Feedback