› › ›

Функции и операторы дат/времени и интервалов

Elasticsearch SQL предоставляет широкий набор возможностей для выполнения манипуляций с датами и временем.

Интервалы

Частая задача при работе с датами и временем в целом связана с понятием interval, тема, заслуживающая изучения в контексте Elasticsearch и Elasticsearch SQL.

Elasticsearch предоставляет полную поддержку математических операций с датами как внутри имен индексов, так и в запросах. В Elasticsearch SQL первое поддерживается напрямую, передавая выражение в имени таблицы, а второе — через стандартный SQL INTERVAL.

В таблице ниже показано отображение между Elasticsearch и Elasticsearch SQL:

Elasticsearch	Elasticsearch SQL
Математические операции с датой в индексе/таблице
`<index-{now/M{YYYY.MM}}>`
Математические операции с датой/временем в запросе
`1y`	`INTERVAL 1 YEAR`
`2M`	`INTERVAL 2 MONTH`
`3w`	`INTERVAL 21 DAY`
`4d`	`INTERVAL 4 DAY`
`5h`	`INTERVAL 5 HOUR`
`6m`	`INTERVAL 6 MINUTE`
`7s`	`INTERVAL 7 SECOND`

INTERVAL допускает смешение YEAR и MONTH или DAY, HOUR, MINUTE и SECOND.

Elasticsearch SQL также принимает множественное число для каждого временного интервала (например, и YEAR и YEARS являются допустимыми).

Примеры возможных комбинаций:

Интервал	Описание
`INTERVAL '1-2' YEAR TO MONTH`	1 год и 2 месяца
`INTERVAL '3 4' DAYS TO HOURS`	3 дня и 4 часа
`INTERVAL '5 6:12' DAYS TO MINUTES`	5 дней, 6 часов и 12 минут
`INTERVAL '3 4:56:01' DAY TO SECOND`	3 дня, 4 часа, 56 минут и 1 секунда
`INTERVAL '2 3:45:01.23456789' DAY TO SECOND`	2 дня, 3 часа, 45 минут, 1 секунда и 234567890 наносекунд
`INTERVAL '123:45' HOUR TO MINUTES`	123 часа и 45 минут
`INTERVAL '65:43:21.0123' HOUR TO SECONDS`	65 часов, 43 минуты, 21 секунда и 12300000 наносекунд
`INTERVAL '45:01.23' MINUTES TO SECONDS`	45 минут, 1 секунда и 230000000 наносекунд

Сравнение

Поля даты и времени можно сравнивать с выражениями математических операций с датами с помощью операторов равенства (=) и IN:

SELECT hire_date FROM emp WHERE hire_date = '1987-03-01||+4y/y';

       hire_date
------------------------
1991-01-26T00:00:00.000Z
1991-10-22T00:00:00.000Z
1991-09-01T00:00:00.000Z
1991-06-26T00:00:00.000Z
1991-08-30T00:00:00.000Z
1991-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.000Z
1990-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_DATE/CURDATE`

Синопсис:

CURRENT_DATE
CURRENT_DATE()
CURDATE()

Входные данные: нет

Выходные данные: дата

Описание: Возвращает дату (без части времени) момента достижения запросом сервера. Может использоваться как ключевое слово: CURRENT_DATE или как функция без аргументов: CURRENT_DATE().

В отличие от CURRENT_DATE, CURDATE() может использоваться только как функция без аргументов, а не как ключевое слово.

Этот метод всегда возвращает одно и то же значение для каждого своего вызова в рамках одного запроса.

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

Обычно эта функция (а также её двойник TODAY()) используется для фильтрации дат относительно текущей:

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

 first_name
------------
Alejandro
Amabile
Anoosh
Basil
Cristinel

`CURRENT_TIME/CURTIME`

Синопсис:

CURRENT_TIME
CURRENT_TIME([precision]) 
CURTIME

Входные данные:

дробные части; необязательно

Выходные данные: время

Описание: Возвращает время, когда текущий запрос достиг сервера. Как функция, CURRENT_TIME() принимает точность в качестве необязательного параметра для округления дробных частей секунды (наносекунды). По умолчанию точность равна 3, что означает, что будет возвращено время с точностью до миллисекунд.

Этот метод всегда возвращает одно и то же значение для каждого своего вызова в рамках одного запроса.

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
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam

В настоящее время использование точности больше 6 не оказывает никакого влияния на результат работы функции, так как максимальное количество дробных частей секунды, возвращаемых функцией, составляет 6.

`CURRENT_TIMESTAMP`

Синопсис:

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])

Входные данные:

дробные части; необязательно

Выходные данные: дата/время

Описание: Возвращает дату/время момента достижения текущего запроса сервером. Как функция, CURRENT_TIMESTAMP() принимает точность в качестве необязательного параметра для округления дробных частей секунды (наносекунды). По умолчанию точность равна 3, что означает, что будет возвращено значение даты/времени с точностью до миллисекунд.

Этот метод всегда возвращает одно и то же значение для каждого своего вызова в рамках одного запроса.

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

Обычно эта функция (а также её двойник NOW()) используется для фильтрации дат/времени относительно текущей:

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

  first_name
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam

`DATE_ADD/DATEADD/TIMESTAMP_ADD/TIMESTAMPADD`

Краткое описание:

DATE_ADD(
    string_exp, 
    integer_exp, 
    datetime_exp)

Входные данные:

	Строковое выражение, обозначающее единицу даты/времени, которую нужно добавить к дате/времени. Если `null`, функция возвращает `null`.
	Целочисленное выражение, обозначающее, сколько раз указанную выше единицу следует добавить к дате/времени или вычесть из неё (если используется отрицательное значение). Если `null`, функция возвращает `null`.
	Выражение даты/времени. Если `null`, функция возвращает `null`.

Выходные данные: datetime

Описание: Добавляет указанное количество единиц даты/времени к дате/времени. Если количество единиц отрицательно, оно вычитается из даты/времени.

Если второй аргумент имеет тип long, возможно усечение, так как будет извлечено и использовано целочисленное значение из этого long.

Единицы даты/времени для добавления/вычитания
unit	сокращения
year	years, yy, yyyy
quarter	quarters, qq, q
month	months, mm, m
dayofyear	dy, y
day	days, dd, d
week	weeks, wk, ww
weekday	weekdays, dw
hour	hours, hh
minute	minutes, mi, n
second	seconds, ss, s
millisecond	milliseconds, ms
microsecond	microseconds, mcs
nanosecond	nanoseconds, ns

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/DATEDIFF/TIMESTAMP_DIFF/TIMESTAMPDIFF`

Краткое описание:

DATE_DIFF(
    string_exp, 
    datetime_exp, 
    datetime_exp)

Входные данные:

	Строковое выражение, обозначающее единицу разницы даты/времени между следующими двумя выражениями даты/времени. Если `null`, функция возвращает `null`.
	Начальное выражение даты/времени. Если `null`, функция возвращает `null`.
	Конечное выражение даты/времени. Если `null`, функция возвращает `null`.

Выходные данные: integer

Описание: Вычитает второй аргумент из третьего аргумента и возвращает их разницу в кратных единице, указанной в первом аргументе. Если второй аргумент (начало) больше третьего аргумента (конец), возвращаются отрицательные значения.

Единицы разницы даты/времени
unit	сокращения
year	years, yy, yyyy
quarter	quarters, qq, q
month	months, mm, m
dayofyear	dy, y
day	days, dd, d
week	weeks, wk, ww
weekday	weekdays, dw
hour	hours, hh
minute	minutes, mi, n
second	seconds, ss, s
millisecond	milliseconds, ms
microsecond	microseconds, mcs
nanosecond	nanoseconds, ns

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

Для hour и minute, DATEDIFF не выполняет округление, а вместо этого сначала обрезает более подробные поля времени в двух датах до нуля, а затем вычисляет вычитание.

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_PARSE`

Краткое описание:

DATE_PARSE(
    string_exp, 
    string_exp)

Входные данные:

	Выражение даты в виде строки. Если `null` или пустая строка, функция возвращает `null`.
	Шаблон разбора. Если `null` или пустая строка, функция возвращает `null`.

Выходные данные: date

Описание: Возвращает дату, разбирая первый аргумент с использованием формата, указанного во втором аргументе. Используемый шаблон формата разбора взят из java.time.format.DateTimeFormatter.

Если шаблон разбора не содержит всех допустимых единиц даты (например, HH:mm:ss, dd-MM HH:mm:ss и т.д.), возвращается ошибка, так как функция должна возвращать значение типа date, которое будет содержать часть даты.

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

   date
-----------
2020-04-07

Получившийся date будет иметь часовой пояс, указанный пользователем через параметры REST/драйвера time_zone/timezone без применения преобразования.

{
    "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`

Описание:

DATETIME_FORMAT(
    date_exp/datetime_exp/time_exp, 
    string_exp)

Входные данные:

	выражение даты/времени. Если `null`, функция возвращает `null`.
	шаблон формата. Если `null` или пустая строка, функция возвращает `null`.

Выходные данные: строка

Описание: Возвращает дату/время/время в виде строки, используя формат, указанный во втором аргументе. Используемый шаблон форматирования взят из java.time.format.DateTimeFormatter.

Если первый аргумент имеет тип time, то шаблон, указанный во втором аргументе, не может содержать единицы измерения даты (например, dd, MM, yyyy и т. д.). Если он содержит такие единицы, возвращается ошибка.

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`

Описание:

DATETIME_PARSE(
    string_exp, 
    string_exp)

Входные данные:

	выражение даты и времени в виде строки. Если `null` или пустая строка, функция возвращает `null`.
	шаблон разбора. Если `null` или пустая строка, функция возвращает `null`.

Выходные данные: дата и время

Описание: Возвращает дату и время, анализируя первый аргумент с использованием формата, указанного во втором аргументе. Используемый шаблон формата разбора взят из java.time.format.DateTimeFormatter.

Если шаблон разбора содержит только единицы измерения даты или только время (например, dd/MM/yyyy, HH:mm:ss и т. д.), возвращается ошибка, так как функция должна возвращать значение типа datetime, которое должно содержать оба.

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

Если часовой пояс не указан в выражении строки даты и времени и в шаблоне разбора, полученный datetime будет иметь часовой пояс, указанный пользователем через параметры REST/драйвера time_zone/timezone без применения преобразования.

{
    "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`

Описание:

TIME_PARSE(
    string_exp, 
    string_exp)

Входные данные:

	выражение времени в виде строки. Если `null` или пустая строка, функция возвращает `null`.
	шаблон разбора. Если `null` или пустая строка, функция возвращает `null`.

Выходные данные: время

Описание: Возвращает время, анализируя первый аргумент с использованием формата, указанного во втором аргументе. Используемый шаблон формата разбора взят из java.time.format.DateTimeFormatter.

Если шаблон разбора содержит только единицы измерения даты (например, dd/MM/yyyy), возвращается ошибка, так как функция должна возвращать значение типа 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

Если часовой пояс не указан в выражении строки времени и в шаблоне разбора, полученное time будет иметь смещение часового пояса, указанное пользователем через параметры REST/драйвера time_zone/timezone на эпоху Unix (1970-01-01) без применения преобразования.

{
    "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/DATEPART`

Описание:

DATE_PART(
    string_exp, 
    datetime_exp)

Входные данные:

	строковое выражение, обозначающее единицу для извлечения из даты/времени. Если `null`, функция возвращает `null`.
	выражение даты/времени. Если `null`, функция возвращает `null`.

Выходные данные: целое число

Описание: Извлекает указанную единицу измерения из даты/времени. Аналогично EXTRACT, но с другими названиями и псевдонимами для единиц измерения и предоставляет больше опций (например: TZOFFSET).

Единицы измерения даты и времени для извлечения
единица	сокращения
год	годы, yy, yyyy
квартал	кварталы, qq, q
месяц	месяцы, mm, m
день года	день, y
день	дни, dd, d
неделя	недели, wk, ww
день недели	дни недели, dw
час	часы, hh
минута	минуты, mi, n
секунда	секунды, ss, s
миллисекунда	миллисекунды, ms
микросекунда	микросекунды, mcs
наносекунда	наносекунды, ns
смещение часового пояса	часовой пояс

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

Для week и weekday единица извлекается с использованием не-ISO вычисления, что означает, что заданная неделя считается начинающейся с воскресенья, а не с понедельника.

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

   week
----------
39

tzoffset возвращает общее количество минут (со знаком), которые представляют смещение часового пояса.

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/DATETRUNC`

Синопсис:

DATE_TRUNC(
    string_exp, 
    datetime_exp/interval_exp)

Входные данные:

	строковое выражение, обозначающее единицу, к которой следует обрезать дату/время/интервал. Если `null`, функция возвращает `null`.
	выражение даты/времени/интервала. Если `null`, функция возвращает `null`.

Выходные данные: дата/время/интервал

Описание: Обрезает дату/время/интервал до указанной единицы, устанавливая все поля, менее значимые, чем указанное, в ноль (или единицу для дня, дня недели и месяца). Если первый аргумент является week, а второй аргумент имеет тип interval, генерируется ошибка, так как тип данных interval не поддерживает единицу измерения времени week.

Единицы измерения обрезки даты и времени
единица	сокращения
тысячелетие	тысячелетия
век	века
десятилетие	десятилетия
год	годы, гг, гггг
квартал	кварталы, кв, к
месяц	месяцы, мм, м
неделя	недели, нед, нн
день	дни, дд, д
час	часы, чч
минута	минуты, мин, н
секунда	секунды, сс, с
миллисекунда	миллисекунды, мс
микросекунда	микросекунды, мкс
наносекунда	наносекунды, нс

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`

Синопсис:

FORMAT(
    date_exp/datetime_exp/time_exp, 
    string_exp)

Входные данные:

	выражение даты/времени/времени. Если `null`, функция возвращает `null`.
	шаблон формата. Если `null` или пустая строка, функция возвращает `null`.

Выходные данные: строка

Описание: Возвращает дату/время/время в виде строки, используя указанный во втором аргументе формат. Используемый шаблон форматирования взят из формата Microsoft SQL Server.

Если первый аргумент имеет тип time, то шаблон, указанный во втором аргументе, не может содержать единицы, относящиеся к дате (например, дд, ММ, ГГГГ и т. д.). Если он содержит такие единицы, возвращается ошибка.
Спецификатор формата F будет работать аналогично спецификатору формата f. Он вернёт дробную часть секунд, а количество цифр будет таким же, как и количество Fs, предоставленных в качестве входных данных (до 9 цифр). Результат будет содержать 0 в конце, чтобы соответствовать количеству F, предоставленных на вход. Например: для временной части 10:20:30.1234 и шаблона HH:mm:ss.FFFFFF строковое значение функции будет: 10:20:30.123400.
Спецификатор формата y вернёт год эры вместо одной/двух младших цифр. Пример: Для года 2009, y вернёт 2009 вместо 9. Для года 43, спецификатор формата y вернёт 43.
Специальные символы, такие как " , \ и %, будут возвращены в том же виде без изменений. Пример: форматирование даты 17-sep-2020 с %M вернёт %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`

Синопсис:

TO_CHAR(
    date_exp/datetime_exp/time_exp, 
    string_exp)

Входные данные:

	выражение даты/времени/времени. Если `null`, функция возвращает `null`.
	шаблон формата. Если `null` или пустая строка, функция возвращает `null`.

Выходные данные: строка

Описание: Возвращает дату/время/время в виде строки, используя указанный во втором аргументе формат. Шаблон форматирования соответствует PostgreSQL Шаблоны форматирования даты/времени.

Если первый аргумент имеет тип time, то шаблон, указанный во втором аргументе, не может содержать единицы, относящиеся к дате (например, дд, ММ, ГГГГ и т. д.). Если он содержит такие единицы, возвращается ошибка.
Результаты шаблонов TZ и tz (сокращения часовых поясов) в некоторых случаях отличаются от результатов, возвращаемых TO_CHAR в PostgreSQL. Причина в том, что сокращения часовых поясов, указанные в JDK, отличаются от сокращений, указанных в PostgreSQL. Эта функция может отобразить фактическое сокращение часового пояса вместо общих LMT или пустой строки или смещения, возвращаемых реализацией PostgreSQL. Летом/зимние маркеры также могут отличаться между двумя реализациями (например, будут отображать HT вместо HST для Гавайев).
Модификаторы шаблонов FX, TM, SP не поддерживаются и будут отображаться как FX, TM, SP в выводе.

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/DOM/DAY`

Синопсис:

DAY_OF_MONTH(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлекает день месяца из даты/времени.

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

      day
---------------
19

`DAY_OF_WEEK/DAYOFWEEK/DOW`

Синопсис:

DAY_OF_WEEK(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение дня недели из даты/времени. Воскресенье — 1, понедельник — 2 и т.д.

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

      day
---------------
2

`DAY_OF_YEAR/DOY`

Синопсис:

DAY_OF_YEAR(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение порядкового номера дня в году из даты/времени.

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

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

`DAY_NAME/DAYNAME`

Синопсис:

DAY_NAME(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: строка

Описание: Извлечение дня недели из даты/времени в текстовом формате (Monday, Tuesday…).

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

      day
---------------
Monday

`HOUR_OF_DAY/HOUR`

Синопсис:

HOUR_OF_DAY(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение часа из даты/времени.

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

     hour
---------------
10

`ISO_DAY_OF_WEEK/ISODAYOFWEEK/ISODOW/IDOW`

Синопсис:

ISO_DAY_OF_WEEK(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение дня недели из даты/времени, следуя стандарту ISO 8601. Понедельник — 1, вторник — 2 и т.д.

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

      day
---------------
1

`ISO_WEEK_OF_YEAR/ISOWEEKOFYEAR/ISOWEEK/IWOY/IW`

Синопсис:

ISO_WEEK_OF_YEAR(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение номера недели в году из даты/времени, следуя стандарту ISO 8601. Первая неделя года — это первая неделя, у которой большинство (4 или более) дней приходится на январь.

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

     week
---------------
8

`MINUTE_OF_DAY`

Синопсис:

MINUTE_OF_DAY(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение минуты дня из даты/времени.

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

    minute
---------------
623

`MINUTE_OF_HOUR/MINUTE`

Синопсис:

MINUTE_OF_HOUR(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение минуты часа из даты/времени.

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

    minute
---------------
23

`MONTH_OF_YEAR/MONTH`

Синопсис:

MONTH(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение месяца года из даты/времени.

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

     month
---------------
2

`MONTH_NAME/MONTHNAME`

Синопсис:

MONTH_NAME(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: строка

Описание: Извлечение месяца из даты/времени в текстовом формате (January, February…).

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

     month
---------------
February

`NOW`

Синопсис:

NOW()

Входные данные: нет

Выходные данные: datetime

Описание: Эта функция обеспечивает ту же функциональность, что и функция CURRENT_TIMESTAMP(): возвращает datetime, когда текущий запрос достиг сервера. Этот метод всегда возвращает одно и то же значение для каждого своего появления в рамках одного запроса.

SELECT NOW() AS result;

         result
------------------------
2018-12-12T14:48:52.448Z

Обычно эта функция (а также её аналог CURRENT_TIMESTAMP()) используется для фильтрации даты/времени относительно текущего момента:

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

  first_name
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam

`SECOND_OF_MINUTE/SECOND`

Синопсис:

SECOND_OF_MINUTE(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение секунды минуты из даты/времени.

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

    second
---------------
27

`QUARTER`

Синопсис:

QUARTER(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение квартала года, к которому относится дата/время.

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

    quarter
---------------
1

`TODAY`

Синопсис:

TODAY()

Входные данные: нет

Выходные данные: дата

Описание: Эта функция предоставляет ту же функциональность, что и функция CURRENT_DATE(): возвращает дату, когда текущий запрос достиг сервера. Этот метод всегда возвращает одно и то же значение для каждого своего вызова в рамках одного запроса.

SELECT TODAY() AS result;

         result
------------------------
2018-12-12

Обычно эта функция (а также её аналог CURRENT_TIMESTAMP()) используется для фильтрации по датам:

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

 first_name
------------
Alejandro
Amabile
Anoosh
Basil
Cristinel

`WEEK_OF_YEAR/WEEK`

Синопсис:

WEEK_OF_YEAR(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение недели года из даты/времени.

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`

Синопсис:

YEAR(datetime_exp)

Входные данные:

выражение даты/времени. Если null, функция возвращает null.

Выходные данные: целое число

Описание: Извлечение года из даты/времени.

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

     year
---------------
2018

`EXTRACT`

Синопсис:

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

© 2023-2025 Elasticsearch
As of September 2024, Elasticsearch is available under a choice of three licenses: the Server Side Public License (SSPL), the Elastic License, or the AGPLv3 (OSI approved).
Elasticsearch and the Elasticsearch logo are trademarks of Elasticsearch B.V., registered in the U.S. and in other countries.
https://www.elastic.co/guide/en/elasticsearch/reference/7.17/sql-functions-datetime.html