› › ›

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

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() принимает precision в качестве необязательного параметра для округления дробных знаков после секунды (наносекунды). По умолчанию precision равен 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

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

`CURRENT_TIMESTAMP`

Синопсис:

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])

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

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

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

Описание: Возвращает дату/время, когда текущий запрос достиг сервера. Как функция, CURRENT_TIMESTAMP() принимает precision в качестве необязательного параметра для округления дробных знаков после секунды (наносекунды). По умолчанию precision равен 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_FORMAT`

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

DATE_FORMAT(
    date_exp/datetime_exp/time_exp, 
    string_exp)

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

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

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

Описание: Возвращает дату/время в виде строки, используя формат, указанный во втором аргументе. Шаблон форматирования — один из спецификаторов, используемых в функции MySQL DATE_FORMAT().

Если первый аргумент имеет тип time, то шаблон, указанный вторым аргументом, не может содержать единицы, относящиеся к дате (например, dd, MM, yyyy и т. д.). Если он содержит такие единицы, возвращается ошибка. Диапазоны для спецификаторов месяца и дня (%c, %D, %d, %e, %m) начинаются с единицы, в отличие от MySQL, где они начинаются с нуля, из-за того, что MySQL допускает хранение неполных дат, таких как 2014-00-00. В этом случае Elasticsearch возвращает ошибку.

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`

Синопсис:

DATE_PARSE(
    string_exp, 
    string_exp)

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

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

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

Описание: Возвращает дату, разобрав первое аргумент с помощью формата, указанного во втором аргументе. Шаблон формата разбора используется из 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 будет иметь часовой пояс, указанный пользователем через time_zone/timezone параметры REST/драйвера без применения преобразований.

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

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

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

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

Описание: Возвращает дату/datetime/время в виде строки с использованием формата, указанного во втором аргументе. Использованный шаблон форматирования взят из 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)

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

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

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

Описание: Возвращает datetime, разобрав первое аргумент с помощью формата, указанного во втором аргументе. Шаблон формата разбора используется из 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 и шаблоне разбора, полученная datetime будет иметь часовой пояс, указанный пользователем через time_zone/timezone параметры REST/драйвера без применения преобразований.

{
    "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 будет иметь смещение часового пояса, указанного пользователем через time_zone/timezone параметры REST/драйвера в эпоху 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
день года	дн, г
день	дни, 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`.

Вывод: datetime/interval

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

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

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

Вывод: строка

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

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

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

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

Вывод: строка

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

Если 1-й аргумент имеет тип time, то шаблон, указанный во 2-м аргументе, не может содержать единицы измерения даты (например, dd, MM, YYYY и т. д.). Если он содержит такие единицы, возвращается ошибка.
Результаты шаблонов 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)

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

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

Вывод: целое число

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

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)

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

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

Вывод: целое число

Описание: Извлечение дня недели из даты/datetime. Воскресенье - 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)

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

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

Вывод: целое число

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

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

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

`DAY_NAME/DAYNAME`

Описание:

DAY_NAME(datetime_exp)

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

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

Вывод: строка

Описание: Извлечение дня недели из даты/datetime в текстовом формате (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)

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

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

Вывод: целое число

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

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)

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

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

Вывод: целое число

Описание: Извлечение дня недели из даты/datetime, следуя стандарту 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)

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

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

Вывод: целое число

Описание: Извлечение недели года из даты/datetime, следуя стандарту 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)

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

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

Вывод: целое число

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

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)

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

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

Вывод: целое число

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

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()

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

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

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

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/8.17/sql-functions-datetime.html