Elasticsearch SQL accepts also the plural for each time unit (e.g. bothYEAR andYEARS are valid).

SELECT hire_date FROM emp WHERE hire_date = '1987-03-01||+4y/y';       hire_date------------------------1991-01-26T00:00:00.000Z1991-10-22T00:00:00.000Z1991-09-01T00:00:00.000Z1991-06-26T00:00:00.000Z1991-08-30T00:00:00.000Z1991-12-01T00:00:00.000Z

SELECT hire_date FROM emp WHERE hire_date IN ('1987-03-01||+2y/M', '1987-03-01||+3y/M');       hire_date------------------------1989-03-31T00:00:00.000Z1990-03-02T00:00:00.000Z

SELECT INTERVAL 1 DAY + INTERVAL 53 MINUTES AS result;    result---------------+1 00:53:00

SELECT CAST('1969-05-13T12:34:56' AS DATETIME) + INTERVAL 49 YEARS AS result;       result--------------------2018-05-13T12:34:56Z

SELECT - INTERVAL '49-1' YEAR TO MONTH result;    result----------------49-1

SELECT INTERVAL '1' DAY - INTERVAL '2' HOURS AS result;    result---------------+0 22:00:00

SELECT CAST('2018-05-13T12:34:56' AS DATETIME) - INTERVAL '2-8' YEAR TO MONTH AS result;       result--------------------2015-09-13T12:34:56Z

SELECT -2 * INTERVAL '3' YEARS AS result;    result----------------6-0

CURRENT_DATECURRENT_DATE()CURDATE()

Unlike CURRENT_DATE,CURDATE() can only be used as a function with no arguments and not as a keyword.

SELECT CURRENT_DATE AS result;         result------------------------2018-12-12

SELECT CURRENT_DATE() AS result;         result------------------------2018-12-12

SELECT CURDATE() AS result;         result------------------------2018-12-12

SELECT first_name FROM emp WHERE hire_date > TODAY() - INTERVAL 35 YEARS ORDER BY first_name ASC LIMIT 5; first_name------------AlejandroAmabileAnooshBasilBrendon

CURRENT_TIMECURRENT_TIME([precision])CURTIME

SELECT CURRENT_TIME AS result;         result------------------------12:31:27.237Z

SELECT CURRENT_TIME() AS result;         result------------------------12:31:27.237Z

SELECT CURTIME() AS result;         result------------------------12:31:27.237Z

SELECT CURRENT_TIME(1) AS result;         result------------------------12:31:27.2Z

SELECT first_name FROM emp WHERE CAST(hire_date AS TIME) > CURRENT_TIME() - INTERVAL 20 MINUTES ORDER BY first_name ASC LIMIT 5;  first_name---------------AlejandroAmabileAnnekeAnooshArumugam

Currently, using aprecision greater than 6 doesn’t make any difference to the output of the function as the maximum number of second fractional digits returned is 6.

CURRENT_TIMESTAMPCURRENT_TIMESTAMP([precision])

SELECT CURRENT_TIMESTAMP AS result;         result------------------------2018-12-12T14:48:52.448Z

SELECT CURRENT_TIMESTAMP() AS result;         result------------------------2018-12-12T14:48:52.448Z

SELECT CURRENT_TIMESTAMP(1) AS result;         result------------------------2018-12-12T14:48:52.4Z

SELECT first_name FROM emp WHERE hire_date > NOW() - INTERVAL 100 YEARS ORDER BY first_name ASC LIMIT 5;  first_name---------------AlejandroAmabileAnnekeAnooshArumugam

Currently, using aprecision greater than 6 doesn’t make any difference to the output of the function as the maximum number of second fractional digits returned is 6.

DATE_ADD(    string_exp,    integer_exp,    datetime_exp)

If the second argument is a long there is possibility of truncation since an integer value will be extracted and used from that long.

SELECT DATE_ADD('years', 10, '2019-09-04T11:22:33.000Z'::datetime) AS "+10 years";      +10 years------------------------2029-09-04T11:22:33.000Z

SELECT DATE_ADD('week', 10, '2019-09-04T11:22:33.000Z'::datetime) AS "+10 weeks";      +10 weeks------------------------2019-11-13T11:22:33.000Z

SELECT DATE_ADD('seconds', -1234, '2019-09-04T11:22:33.000Z'::datetime) AS "-1234 seconds";      -1234 seconds------------------------2019-09-04T11:01:59.000Z

SELECT DATE_ADD('qq', -417, '2019-09-04'::date) AS "-417 quarters";      -417 quarters------------------------1915-06-04T00:00:00.000Z

SELECT DATE_ADD('minutes', 9235, '2019-09-04'::date) AS "+9235 minutes";      +9235 minutes------------------------2019-09-10T09:55:00.000Z

DATE_DIFF(    string_exp,    datetime_exp,    datetime_exp)

SELECT DATE_DIFF('years', '2019-09-04T11:22:33.000Z'::datetime, '2032-09-04T22:33:11.000Z'::datetime) AS "diffInYears";      diffInYears------------------------13

SELECT DATE_DIFF('week', '2019-09-04T11:22:33.000Z'::datetime, '2016-12-08T22:33:11.000Z'::datetime) AS "diffInWeeks";      diffInWeeks-------------------------143

SELECT DATE_DIFF('seconds', '2019-09-04T11:22:33.123Z'::datetime, '2019-07-12T22:33:11.321Z'::datetime) AS "diffInSeconds";      diffInSeconds-------------------------4625362

SELECT DATE_DIFF('qq', '2019-09-04'::date, '2025-04-25'::date) AS "diffInQuarters";      diffInQuarters------------------------23

Forhour andminute,DATEDIFF doesn’t do any rounding, but instead first truncates the more detailed time fields on the 2 dates to zero and then calculates the subtraction.

SELECT DATEDIFF('hours', '2019-11-10T12:10:00.000Z'::datetime, '2019-11-10T23:59:59.999Z'::datetime) AS "diffInHours";      diffInHours------------------------11

SELECT DATEDIFF('minute', '2019-11-10T12:10:00.000Z'::datetime, '2019-11-10T12:15:59.999Z'::datetime) AS "diffInMinutes";      diffInMinutes------------------------5

SELECT DATE_DIFF('minutes', '2019-09-04'::date, '2015-08-17T22:33:11.567Z'::datetime) AS "diffInMinutes";      diffInMinutes-------------------------2128407

DATE_FORMAT(    date_exp/datetime_exp/time_exp,    string_exp)

If the 1st argument is of typetime, then pattern specified by the 2nd argument cannot contain date related units (e.g.dd,MM,yyyy, etc.). If it contains such units an error is returned. Ranges for month and day specifiers (%c, %D, %d, %e, %m) start at one, unlike MySQL, where they start at zero, due to the fact that MySQL permits the storing of incomplete dates such as2014-00-00. Elasticsearch in this case returns an error.

SELECT DATE_FORMAT(CAST('2020-04-05' AS DATE), '%d/%m/%Y') AS "date";      date------------------05/04/2020

SELECT DATE_FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), '%d/%m/%Y %H:%i:%s.%f') AS "datetime";      datetime------------------05/04/2020 11:22:33.987654

SELECT DATE_FORMAT(CAST('23:22:33.987' AS TIME), '%H %i %s.%f') AS "time";      time------------------23 22 33.987000

DATE_PARSE(    string_exp,    string_exp)

If the parsing pattern does not contain all valid date units (e.g.HH:mm:ss,dd-MM HH:mm:ss, etc.) an error is returned as the function needs to return a value ofdate type which will contain date part.

SELECT DATE_PARSE('07/04/2020', 'dd/MM/yyyy') AS "date";   date-----------2020-04-07

The resultingdate will have the time zone specified by the user through thetime_zone/timezone REST/driver parameters with no conversion applied.

{    "query" : "SELECT DATE_PARSE('07/04/2020', 'dd/MM/yyyy') AS \"date\"",    "time_zone" : "Europe/Athens"}   date------------2020-04-07T00:00:00.000+03:00

DATETIME_FORMAT(    date_exp/datetime_exp/time_exp,    string_exp)

If the 1st argument is of typetime, then pattern specified by the 2nd argument cannot contain date related units (e.g.dd,MM,yyyy, etc.). If it contains such units an error is returned.

SELECT DATETIME_FORMAT(CAST('2020-04-05' AS DATE), 'dd/MM/yyyy') AS "date";      date------------------05/04/2020

SELECT DATETIME_FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'dd/MM/yyyy HH:mm:ss.SS') AS "datetime";      datetime------------------05/04/2020 11:22:33.98

SELECT DATETIME_FORMAT(CAST('11:22:33.987' AS TIME), 'HH mm ss.S') AS "time";      time------------------11 22 33.9

DATETIME_PARSE(    string_exp,    string_exp)

If the parsing pattern contains only date or only time units (e.g.dd/MM/yyyy,HH:mm:ss, etc.) an error is returned as the function needs to return a value ofdatetime type which must contain both.

SELECT DATETIME_PARSE('07/04/2020 10:20:30.123', 'dd/MM/yyyy HH:mm:ss.SSS') AS "datetime";      datetime------------------------2020-04-07T10:20:30.123Z

SELECT DATETIME_PARSE('10:20:30 07/04/2020 Europe/Berlin', 'HH:mm:ss dd/MM/yyyy VV') AS "datetime";      datetime------------------------2020-04-07T08:20:30.000Z

If timezone is not specified in the datetime string expression and the parsing pattern, the resultingdatetime will have the time zone specified by the user through thetime_zone/timezone REST/driver parameters with no conversion applied.

{    "query" : "SELECT DATETIME_PARSE('10:20:30 07/04/2020', 'HH:mm:ss dd/MM/yyyy') AS \"datetime\"",    "time_zone" : "Europe/Athens"}      datetime-----------------------------2020-04-07T10:20:30.000+03:00

TIME_PARSE(    string_exp,    string_exp)

If the parsing pattern contains only date units (e.g.dd/MM/yyyy) an error is returned as the function needs to return a value oftime type which will contain only time.

SELECT TIME_PARSE('10:20:30.123', 'HH:mm:ss.SSS') AS "time";     time---------------10:20:30.123Z

SELECT TIME_PARSE('10:20:30-01:00', 'HH:mm:ssXXX') AS "time";     time---------------11:20:30.000Z

If timezone is not specified in the time string expression and the parsing pattern, the resultingtime will have the offset of the time zone specified by the user through thetime_zone/timezone REST/driver parameters at the Unix epoch date (1970-01-01) with no conversion applied.

{    "query" : "SELECT DATETIME_PARSE('10:20:30', 'HH:mm:ss') AS \"time\"",    "time_zone" : "Europe/Athens"}      time------------------------------------10:20:30.000+02:00

DATE_PART(    string_exp,    datetime_exp)

SELECT DATE_PART('year', '2019-09-22T11:22:33.123Z'::datetime) AS "years";   years----------2019

SELECT DATE_PART('mi', '2019-09-04T11:22:33.123Z'::datetime) AS mins;   mins-----------22

SELECT DATE_PART('quarters', CAST('2019-09-24' AS DATE)) AS quarter;   quarter-------------3

SELECT DATE_PART('month', CAST('2019-09-24' AS DATE)) AS month;   month-------------9

Forweek andweekday the unit is extracted using the non-ISO calculation, which means that a given week is considered to start from Sunday, not Monday.

SELECT DATE_PART('week', '2019-09-22T11:22:33.123Z'::datetime) AS week;   week----------39

Thetzoffset returns the total number of minutes (signed) that represent the time zone’s offset.

SELECT DATE_PART('tzoffset', '2019-09-04T11:22:33.123+05:15'::datetime) AS tz_mins;   tz_mins--------------315

SELECT DATE_PART('tzoffset', '2019-09-04T11:22:33.123-03:49'::datetime) AS tz_mins;   tz_mins---------------229

DATE_TRUNC(    string_exp,    datetime_exp/interval_exp)

SELECT DATE_TRUNC('millennium', '2019-09-04T11:22:33.123Z'::datetime) AS millennium;      millennium------------------------2000-01-01T00:00:00.000Z

SELECT DATETRUNC('week', '2019-08-24T11:22:33.123Z'::datetime) AS week;      week------------------------2019-08-19T00:00:00.000Z

SELECT DATE_TRUNC('mi', '2019-09-04T11:22:33.123Z'::datetime) AS mins;      mins------------------------2019-09-04T11:22:00.000Z

SELECT DATE_TRUNC('decade', CAST('2019-09-04' AS DATE)) AS decades;      decades------------------------2010-01-01T00:00:00.000Z

SELECT DATETRUNC('quarters', CAST('2019-09-04' AS DATE)) AS quarter;      quarter------------------------2019-07-01T00:00:00.000Z

SELECT DATE_TRUNC('centuries', INTERVAL '199-5' YEAR TO MONTH) AS centuries;      centuries------------------ +100-0

SELECT DATE_TRUNC('hours', INTERVAL '17 22:13:12' DAY TO SECONDS) AS hour;      hour------------------+17 22:00:00

SELECT DATE_TRUNC('days', INTERVAL '19 15:24:19' DAY TO SECONDS) AS day;      day------------------+19 00:00:00

FORMAT(    date_exp/datetime_exp/time_exp,    string_exp)

If the 1st argument is of typetime, then pattern specified by the 2nd argument cannot contain date related units (e.g.dd,MM,yyyy, etc.). If it contains such units an error is returned.
Format specifierF will be working similar to format specifierf. It will return the fractional part of seconds, and the number of digits will be same as of the number ofFs provided as input (up to 9 digits). Result will contain0 appended in the end to match with number ofF provided. e.g.: for a time part10:20:30.1234 and patternHH:mm:ss.FFFFFF, the output string of the function would be:10:20:30.123400.
Format specifiery will return year-of-era instead of one/two low-order digits. eg.: For year2009,y will be returning2009 instead of9. For year43,y format specifier will return43. - Special characters like" ,\ and% will be returned as it is without any change. eg.: formatting date17-sep-2020 with%M will return%9

SELECT FORMAT(CAST('2020-04-05' AS DATE), 'dd/MM/yyyy') AS "date";      date------------------05/04/2020

SELECT FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'dd/MM/yyyy HH:mm:ss.ff') AS "datetime";      datetime------------------05/04/2020 11:22:33.98

SELECT FORMAT(CAST('11:22:33.987' AS TIME), 'HH mm ss.f') AS "time";      time------------------11 22 33.9

TO_CHAR(    date_exp/datetime_exp/time_exp,    string_exp)

If the 1st argument is of typetime, then the pattern specified by the 2nd argument cannot contain date related units (e.g.dd,MM,YYYY, etc.). If it contains such units an error is returned.
The result of the patternsTZ andtz (time zone abbreviations) in some cases differ from the results returned by theTO_CHAR in PostgreSQL. The reason is that the time zone abbreviations specified by the JDK are different from the ones specified by PostgreSQL. This function might show an actual time zone abbreviation instead of the genericLMT or empty string or offset returned by the PostgreSQL implementation. The summer/daylight markers might also differ between the two implementations (e.g. will showHT instead ofHST for Hawaii).
TheFX,TM,SP pattern modifiers are not supported and will show up asFX,TM,SP literals in the output.

SELECT TO_CHAR(CAST('2020-04-05' AS DATE), 'DD/MM/YYYY') AS "date";      date------------------05/04/2020

SELECT TO_CHAR(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'DD/MM/YYYY HH24:MI:SS.FF2') AS "datetime";      datetime------------------05/04/2020 11:22:33.98

SELECT TO_CHAR(CAST('23:22:33.987' AS TIME), 'HH12 MI SS.FF1') AS "time";      time------------------11 22 33.9

DAY_OF_MONTH(datetime_exp)

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

DAY_OF_WEEK(datetime_exp)

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

DAY_OF_YEAR(datetime_exp)

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

DAY_NAME(datetime_exp)

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

HOUR_OF_DAY(datetime_exp)

SELECT HOUR_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS hour;     hour---------------10

ISO_DAY_OF_WEEK(datetime_exp)

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

ISO_WEEK_OF_YEAR(datetime_exp)

SELECT ISO_WEEK_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS week;     week---------------8

MINUTE_OF_DAY(datetime_exp)

SELECT MINUTE_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS minute;    minute---------------623

MINUTE_OF_HOUR(datetime_exp)

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

MONTH(datetime_exp)

SELECT MONTH_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS month;     month---------------2

MONTH_NAME(datetime_exp)

SELECT MONTH_NAME(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS month;     month---------------February

NOW()

SELECT NOW() AS result;         result------------------------2018-12-12T14:48:52.448Z

SELECT first_name FROM emp WHERE hire_date > NOW() - INTERVAL 100 YEARS ORDER BY first_name ASC LIMIT 5;  first_name---------------AlejandroAmabileAnnekeAnooshArumugam

SECOND_OF_MINUTE(datetime_exp)

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

QUARTER(datetime_exp)

SELECT QUARTER(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS quarter;    quarter---------------1

TODAY()

SELECT TODAY() AS result;         result------------------------2018-12-12

SELECT first_name FROM emp WHERE hire_date > TODAY() - INTERVAL 35 YEARS ORDER BY first_name ASC LIMIT 5; first_name------------AlejandroAmabileAnooshBasilBrendon

WEEK_OF_YEAR(datetime_exp)

SELECT WEEK(CAST('1988-01-05T09:22:10Z' AS TIMESTAMP)) AS week, ISOWEEK(CAST('1988-01-05T09:22:10Z' AS TIMESTAMP)) AS isoweek;      week     |   isoweek---------------+---------------2              |1

YEAR(datetime_exp)

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

EXTRACT(    datetime_function    FROM datetime_exp)

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

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