Как извлечь время из даты sql
EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval expression. The expr can be any expression that evaluates to a datetime or interval data type compatible with the requested field:
If YEAR or MONTH is requested, then expr must evaluate to an expression of data type DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL YEAR TO MONTH .
If DAY is requested, then expr must evaluate to an expression of data type DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL DAY TO SECOND .
If HOUR , MINUTE , or SECOND is requested, then expr must evaluate to an expression of data type TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , or INTERVAL DAY TO SECOND . DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which has no time fields.
If TIMEZONE_HOUR , TIMEZONE_MINUTE , TIMEZONE_ABBR , TIMEZONE_REGION , or TIMEZONE_OFFSET is requested, then expr must evaluate to an expression of data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE .
EXTRACT interprets expr as an ANSI datetime data type. For example, EXTRACT treats DATE not as legacy Oracle DATE but as ANSI DATE , without time elements. Therefore, you can extract only YEAR , MONTH , and DAY from a DATE value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP WITH TIME ZONE data type.
When you specify TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a VARCHAR2 string containing the appropriate time zone region name or abbreviation. When you specify any of the other datetime fields, the value returned is an integer value of NUMBER data type representing the datetime value in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone region names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.
This function can be very useful for manipulating datetime field values in very large tables, as shown in the first example below.
Time zone region names are needed by the daylight saving feature. These names are stored in two types of time zone files: one large and one small. One of these files is the default file, depending on your environment and the release of Oracle Database you are using. For more information regarding time zone files and names, see Oracle Database Globalization Support Guide .
Some combinations of datetime field and datetime or interval value expression result in ambiguity. In these cases, Oracle Database returns UNKNOWN (see the examples that follow for additional information).
Oracle Database Globalization Support Guide for a complete listing of the time zone region names in both files
Appendix C in Oracle Database Globalization Support Guide for the collation derivation rules, which define the collation assigned to the character return value of EXTRACT
Datetime/Interval Arithmetic for a description of datetime_value_expr and interval_value_expr
Oracle Database Reference for information on the dynamic performance views
The following example returns from the oe.orders table the number of orders placed in each month:
The following example returns the year 1998.
The following example selects from the sample table hr.employees all employees who were hired after 2007:
The following example results in ambiguity, so Oracle returns UNKNOWN :
The ambiguity arises because the time zone numerical offset is provided in the expression, and that numerical offset may map to more than one time zone region name.
Как извлечь время из даты sql
Все существующие функции для обработки даты/времени перечислены в Таблице 9.33, а подробнее они описаны в следующих подразделах. Поведение основных арифметических операторов ( + , * и т. д.) описано в Таблице 9.32. Функции форматирования этих типов данных были перечислены в Разделе 9.8. Общую информацию об этих типах вы получили (или можете получить) в Разделе 8.5.
Помимо этого, для типов даты/времени имеются обычные операторы сравнения, показанные в Таблице 9.1. Значения даты и даты со временем (с часовым поясом или без него) можно сравнивать как угодно, тогда как значения только времени (с часовым поясом или без него) и интервалы допустимо сравнивать, только если их типы совпадают. При сравнении даты со временем без часового пояса и даты со временем с часовым поясом предполагается, что первое значение задано в часовом поясе, установленном параметром TimeZone, и оно пересчитывается в UTC для сравнения со вторым значением (внутри уже представленным в UTC). Аналогичным образом, при сравнении значений даты и даты со времени первое считается соответствующим полночи в часовом поясе TimeZone .
Все описанные ниже функции и операторы, принимающие аргументы time или timestamp , фактически представлены в двух вариациях: одна принимает тип time with time zone или timestamp with time zone , а вторая — time without time zone или timestamp without time zone . Для краткости эти вариации здесь не разделяются. Кроме того, операторы + и * определяются парами, наделяющими их переместительным свойством (например, date + integer и integer + date ); здесь приводится только один вариант для каждой пары.
Таблица 9.32. Операторы даты/времени
date + integer → date
Добавляет к дате заданное число дней
date + interval → timestamp
Добавляет к дате интервал
date + time → timestamp
Добавляет к дате время
interval + interval → interval
timestamp + interval → timestamp
Добавляет к отметке времени интервал
time + interval → time
Добавляет к времени интервал
Меняет направление интервала
date — date → integer
Вычитает даты, выдавая разницу в днях
date — integer → date
Вычитает из даты заданное число дней
date — interval → timestamp
Вычитает из даты интервал
time — time → interval
Вычитает из одного времени другое
time — interval → time
Вычитает из времени интервал
timestamp — interval → timestamp
Вычитает из отметки времени интервал
interval — interval → interval
Вычитает из одного интервала другой
timestamp — timestamp → interval
Вычитает из одной отметки времени другую (преобразуя 24-часовые интервалы в дни подобно justify_hours() )
interval * double precision → interval
Умножает интервал на скалярное значение
interval ‘1 second’ * 900 → 00:15:00
interval ‘1 day’ * 21 → 21 days
interval / double precision → interval
Делит интервал на скалярное значение
Таблица 9.33. Функции даты/времени
age ( timestamp , timestamp ) → interval
Вычитает аргументы и выдаёт « символический » результат с годами и месяцами, а не просто днями
age ( timestamp ) → interval
Вычитает аргумент из current_date (полночь текущего дня)
clock_timestamp ( ) → timestamp with time zone
Текущая дата и время (меняется в процессе выполнения операторов); см. Подраздел 9.9.5
current_time → time with time zone
Текущее время суток; см. Подраздел 9.9.5
current_time ( integer ) → time with time zone
Текущее время суток (с ограниченной точностью); см. Подраздел 9.9.5
current_timestamp → timestamp with time zone
Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5
current_timestamp ( integer ) → timestamp with time zone
Текущие дата и время (на момент начала транзакции; с ограниченной точностью); см. Подраздел 9.9.5
date_bin ( interval , timestamp , timestamp ) → timestamp
Подгоняет заданное значение под интервал, отсчитывая от указанного начального момента; см. Подраздел 9.9.3
date_part ( text , timestamp ) → double precision
Возвращает поле даты/времени (равнозначно extract ); см. Подраздел 9.9.1
date_part ( text , interval ) → double precision
Возвращает поле интервала (равнозначно extract ); см. Подраздел 9.9.1
date_trunc ( text , timestamp ) → timestamp
Отсекает компоненты даты до заданной точности; см. Подраздел 9.9.2
date_trunc ( text , timestamp with time zone , text ) → timestamp with time zone
Отсекает компоненты даты до заданной точности в указанном часовом поясе; см. Подраздел 9.9.2
date_trunc ( text , interval ) → interval
Отсекает компоненты даты до заданной точности; см. Подраздел 9.9.2
extract ( field from timestamp ) → numeric
Возвращает поле даты/времени; см. Подраздел 9.9.1
extract ( field from interval ) → numeric
Возвращает поле интервала; см. Подраздел 9.9.1
isfinite ( date ) → boolean
Проверяет конечность даты (её отличие от +/-бесконечности)
isfinite ( timestamp ) → boolean
Проверяет конечность времени (его отличие от +/-бесконечности)
isfinite ( interval ) → boolean
Проверяет конечность интервала (в настоящее время все интервалы конечны)
justify_days ( interval ) → interval
Преобразует интервал так, что каждый 30-дневный период считается одним месяцем
justify_hours ( interval ) → interval
Преобразует интервал так, что каждый 24-часовой период считается одним днём
justify_interval ( interval ) → interval
Преобразует интервал с применением justify_days и justify_hours и дополнительно корректирует знаки
Текущее время суток; см. Подраздел 9.9.5
localtime ( integer ) → time
Текущее время суток (с ограниченной точностью); см. Подраздел 9.9.5
Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5
localtimestamp ( integer ) → timestamp
Текущие дата и время (на момент начала транзакции; с ограниченной точностью); см. Подраздел 9.9.5
make_date ( year int , month int , day int ) → date
Образует дату из полей: year (год), month (месяц) и day (день) (отрицательное значение поля year означает год до н. э.)
make_interval ( [ years int [ , months int [ , weeks int [ , days int [ , hours int [ , mins int [ , secs double precision ] ] ] ] ] ] ] ) → interval
Образует интервал из полей: years (годы), months (месяцы), weeks (недели), days (дни), hours (часы), minutes (минуты) и secs (секунды), каждое из которых по умолчанию считается равным нулю.
make_time ( hour int , min int , sec double precision ) → time
Образует время из полей: hour (час), minute (минута) и sec (секунда)
make_timestamp ( year int , month int , day int , hour int , min int , sec double precision ) → timestamp
Образует момент времени из полей: year (год), month (месяц), day (день), hour (час), minute (минута) и sec (секунда) (отрицательное значение поля year означает год до н. э.)
make_timestamptz ( year int , month int , day int , hour int , min int , sec double precision [ , timezone text ] ) → timestamp with time zone
Образует дату и время с часовым поясом из полей: year (год), month (месяц), day (день), hour (час), minute (минута) и sec (секунда) (отрицательное значение поля year означает год до н. э.). Если параметр timezone (часовой пояс) не указан, используется текущий часовой пояс; в примерах предполагается часовой пояс Europe/London (Европа/Лондон).
make_timestamptz(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5+01
now ( ) → timestamp with time zone
Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5
statement_timestamp ( ) → timestamp with time zone
Текущая дата и время (на момент начала текущего оператора); см. Подраздел 9.9.5
Текущая дата и время (как clock_timestamp , но в виде строки типа text ); см. Подраздел 9.9.5
transaction_timestamp ( ) → timestamp with time zone
Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5
to_timestamp ( double precision ) → timestamp with time zone
Преобразует время эпохи Unix (число секунд с 1970-01-01 00:00:00+00) в дату/время с часовым поясом
В дополнение к этим функциям поддерживается SQL-оператор OVERLAPS :
Его результатом будет true, когда два периода времени (определённые своими границами) пересекаются, и false в противном случае. Границы периода можно задать либо в виде пары дат, времени или дат со временем, либо как дату, время (или дату со временем) c интервалом. Когда указывается пара значений, первым может быть и начало, и конец периода: OVERLAPS автоматически считает началом периода меньшее значение. Периоды времени считаются наполовину открытыми, т. е. начало <= время < конец , если только начало и конец не равны — в этом случае период представляет один момент времени. Это означает, например, что два периода, имеющие только общую границу, не будут считаться пересекающимися.
При добавлении к значению типа timestamp with time zone значения interval (или при вычитании из него interval ), поле дней в этой дате увеличивается (или уменьшается) на указанное число суток, а время суток остаётся неизменным. При пересечении границы перехода на летнее время (если в часовом поясе текущего сеанса производится этот переход) это означает, что interval ‘1 day’ и interval ’24 hours’ не обязательно будут равны. Например, в часовом поясе America/Denver :
Эта разница объясняется тем, что 2005-04-03 02:00 в часовом поясе America/Denver произошёл переход на летнее время.
Обратите внимание на возможную неоднозначность в поле months в результате функции age , вызванную тем, что число дней в разных месяцах неодинаково. Вычисляя оставшиеся дни месяца, Postgres Pro рассматривает месяц меньшей из двух дат. Например, результатом age(‘2004-06-01’, ‘2004-04-30’) будет 1 mon 1 day , так как в апреле 30 дней, а то же выражение с датой 30 мая выдаст 1 mon 2 days , так как в мае 31 день.
Вычитание дат и дат со временем также может быть нетривиальной операцией. Один принципиально простой способ выполнить такое вычисление — преобразовать каждое значение в количество секунд, используя EXTRACT(EPOCH FROM . ) , а затем найти разницу результатов; при этом будет получено число секунд между двумя датами. При этом будет учтено неодинаковое число дней в месяцах, изменения часовых поясов и переходы на летнее время. При вычитании дат или дат со временем с помощью оператора « — » выдаётся число дней (по 24 часа) и часов/минут/секунд между данными значениями, с учётом тех же факторов. Функция age возвращает число лет, месяцев, дней и часов/минут/секунд, выполняя вычитание по полям, а затем пересчитывая отрицательные значения. Различие этих подходов иллюстрируют следующие запросы. Показанные результаты были получены для часового пояса ‘US/Eastern’ ; между двумя заданными датами произошёл переход на летнее время:
9.9.1. EXTRACT , date_part
Функция extract получает из значений даты/времени поля, такие как год или час. Здесь источник — значение типа timestamp , time или interval . (Выражения типа date приводятся к типу timestamp , так что допускается и этот тип.) Указанное поле представляет собой идентификатор, по которому из источника выбирается заданное поле. Функция extract возвращает значения типа numeric . Допустимые поля:
Первый век начался 0001-01-01 00:00:00, хотя люди в то время и не считали так. Это определение распространяется на все страны с григорианским календарём. Века с номером 0 не было; считается, что 1 наступил после -1. Если такое положение вещей вас не устраивает, направляйте жалобы по адресу: Ватикан, Собор Святого Петра, Папе. day
Для значений timestamp это день месяца (1–31); для значений interval — число дней decade
Год, делённый на 10 dow
День недели, считая с воскресенья ( 0 ) до субботы ( 6 )
Заметьте, что в extract дни недели нумеруются не так, как в функции to_char(. ‘D’) . doy
День года (1–365/366) epoch
Для значений timestamp with time zone это число секунд с 1970-01-01 00:00:00 UTC (отрицательное для предшествующего времени); для значений date и timestamp — номинальное число секунд с 1970-01-01 00:00:00 без учёта часового пояса, переходов на летнее время и т. п.; для значений interval — общее количество секунд в интервале
Преобразовать время эпохи назад, в значение timestamp with time zone , с помощью to_timestamp можно так:
Имейте в виду, что применяя to_timestamp к времени эпохи, извлечённому из значения date или timestamp , можно получить не вполне ожидаемый результат: эта функция подразумевает, что изначальное значение задано в часовом поясе UTC, но это может быть не так. hour
День недели, считая с понедельника ( 1 ) до воскресенья ( 7 )
Результат отличается от dow только для воскресенья. Такая нумерация соответствует ISO 8601. isoyear
Этого поля не было в PostgreSQL до версии 8.3. julian
Юлианская дата, соответствующая дате или дате/времени (для интервала не определена). Значение будет дробным, если заданное время отличается от начала суток по местному времени. За дополнительной информацией обратитесь к Разделу B.7. microseconds
Значение секунд с дробной частью, умноженное на 1 000 000; заметьте, что оно включает и целые секунды millennium
Годы 20 века относятся ко второму тысячелетию. Третье тысячелетие началось 1 января 2001 г. milliseconds
Значение секунд с дробной частью, умноженное на 1 000; заметьте, что оно включает и целые секунды. minute
Минуты (0–59) month
Для значений timestamp это номер месяца в году (1–12), а для interval — остаток от деления числа месяцев на 12 (0–11) quarter
Квартал (1–4), к которому относится дата second
Секунды, включая дробную часть timezone
Смещение часового пояса от UTC, представленное в секундах. Положительные значения соответствуют часовым поясам к востоку от UTC, а отрицательные — к западу. (Строго говоря, в Postgres Pro используется не UTC, так как секунды координации не учитываются.) timezone_hour
Поле часов в смещении часового пояса timezone_minute
Поле минут в смещении часового пояса week
В системе нумерации недель ISO первые числа января могут относиться к 52-ой или 53-ей неделе предыдущего года, а последние числа декабря — к первой неделе следующего года. Например, 2005-01-01 относится к 53-ей неделе 2004 г., а 2006-01-01 — к 52-ей неделе 2005 г., тогда как 2012-12-31 включается в первую неделю 2013 г. Поэтому для получения согласованных результатов рекомендуется использовать поле isoyear в паре с week . year
Поле года. Учтите, что года 0 не было, и это следует иметь в виду, вычитая из годов нашей эры годы до нашей эры.
Примечание
С аргументом +/-бесконечность extract возвращает +/-бесконечность для монотонно увеличивающихся полей ( epoch , julian , year , isoyear , decade , century и millennium ). Для других полей возвращается NULL. До версии 9.6 Postgres Pro возвращал ноль для всех случаев с бесконечными аргументами.
Функция extract в основном предназначена для вычислительных целей. Функции форматирования даты/времени описаны в Разделе 9.8.
Функция date_part эмулирует традиционный для Ingres эквивалент стандартной SQL -функции extract :
Заметьте, что здесь параметр поле должен быть строковым значением, а не именем. Функция date_part воспринимает те же поля, что и extract . По историческим причинам функция date_part возвращает значения типа double precision . В некоторых случаях это может привести к потере точности. Поэтому вместо неё рекомендуется использовать функцию extract .
9.9.2. date_trunc
Функция date_trunc работает подобно trunc для чисел.
Здесь значение — выражение типа timestamp , timestamp with time zone или interval . (Значения типов date и time автоматически приводятся к типам timestamp и interval , соответственно.) Параметр поле определяет, до какой точности обрезать переданное значение. В возвращаемом значении, имеющем также тип timestamp , timestamp with time zone или interval , все поля, менее значимые, чем заданное, будут равны нулю (или одному, если это номер дня или месяца).
Параметр поле может принимать следующие значения:
| microseconds |
| milliseconds |
| second |
| minute |
| hour |
| day |
| week |
| month |
| quarter |
| year |
| decade |
| century |
| millennium |
Когда входное значение имеет тип timestamp with time zone , оно обрезается с учётом заданного часового пояса; например, если обрезать значение до поля day (день), в результате будет получена полночь в этом часовом поясе. По умолчанию входное значение обрезается с учётом параметра TimeZone, но дополнительный аргумент часовой_пояс позволяет выбрать и другой пояс. Название часового пояса может задаваться любым из способов, описанных в Подразделе 8.5.3.
Часовой пояс нельзя задать для значений типа timestamp without time zone или interval . Такие значения всегда воспринимаются как есть.
Несколько примеров (в предположении, что выбран часовой пояс America/New_York ):
9.9.3. date_bin
Функция date_bin « подгоняет » заданное значение под интервал (шаг), отсчитывая от указанного начального момента.
Здесь значение — выражение типа timestamp , timestamp with time zone . (Значения типов date автоматически приводятся к типу timestamp .) В качестве шага передаётся выражение типа interval . Результат этой функции, также имеющий тип timestamp , timestamp with time zone или interval , — начало интервала, под который подгоняется значение .
В случае целых единиц измерения (1 минута, 1 час и т. д.), она даёт тот же результат, что и аналогичный вызов функции date_trunc , с той лишь разницей, что date_bin может округлить дату до произвольного интервала.
Параметр шаг интервала должен быть больше нуля и задаваться в единицах измерения меньше месяца.
9.9.4. AT TIME ZONE
Оператор AT TIME ZONE преобразует дату/время без часового пояса в дату/время с часовым поясом и обратно, а также пересчитывает значения time with time zone для различных часовых поясов. Его вариации показаны в Таблице 9.34.
Таблица 9.34. Разновидности AT TIME ZONE
timestamp without time zone AT TIME ZONE часовой_пояс → timestamp with time zone
Переводит значение даты/времени без часового пояса в дату/время с часовым поясом, в предположении, что входное значение задано в указанном поясе.
timestamp with time zone AT TIME ZONE часовой_пояс → timestamp without time zone
Переводит значение даты/времени с часовым поясом в дату/время без часового пояса, которое соответствует входному значению в указанном поясе.
time with time zone AT TIME ZONE часовой_пояс → time with time zone
Переводит значение времени с часовым поясом в другой часовой пояс. Так как время задаётся без даты, в расчёте используется действующее в данный момент смещение указанного часового пояса от UTC.
В этих выражениях желаемый часовой_пояс можно задать либо в виде текстовой строки (например, ‘America/Los_Angeles’ ), либо как интервал (например, INTERVAL ‘-08:00’ ). В первом случае название часового пояса можно указать любым из способов, описанных в Подразделе 8.5.3. Вариант с интервалом полезен, только если для часового пояса смещение от UTC всегда постоянно, что на практике встречается нечасто.
Примеры (в предположении, что параметр TimeZone имеет значение America/Los_Angeles ):
В первом примере для значения, заданного без часового пояса, указывается часовой пояс и полученное время выводится в текущем часовом поясе (заданном параметром TimeZone ). Во втором примере значение времени смещается в заданный часовой пояс и выдаётся без указания часового пояса. Этот вариант позволяет хранить и выводить значения с часовым поясом, отличным от текущего. В третьем примере время в часовом поясе Токио пересчитывается для часового пояса Чикаго.
Функция timezone ( часовой_пояс , время ) равнозначна SQL-совместимой конструкции время AT TIME ZONE часовой_пояс .
9.9.5. Текущая дата/время
Postgres Pro предоставляет набор функций, результат которых зависит от текущей даты и времени. Все следующие функции соответствуют стандарту SQL и возвращают значения, отражающие время начала текущей транзакции:
CURRENT_TIME и CURRENT_TIMESTAMP возвращают время с часовым поясом. В результатах LOCALTIME и LOCALTIMESTAMP нет информации о часовом поясе.
CURRENT_TIME , CURRENT_TIMESTAMP , LOCALTIME и LOCALTIMESTAMP могут принимать необязательный параметр точности, определяющий, до какого знака после запятой следует округлять поле секунд. Если этот параметр отсутствует, результат будет иметь максимально возможную точность.
Так как эти функции возвращают время начала текущей транзакции, во время транзакции эти значения не меняются. Это считается не ошибкой, а особенностью реализации: цель такого поведения в том, чтобы в одной транзакции « текущее » время было одинаковым и для разных изменений в одной транзакций записывалась одна отметка времени.
Примечание
В других СУБД эти значения могут изменяться чаще.
В Postgres Pro есть также функции, возвращающие время начала текущего оператора, а также текущее время в момент вызова функции. Таким образом, в Postgres Pro есть следующие функции, не описанные в стандарте SQL:
Функция transaction_timestamp() равнозначна конструкции CURRENT_TIMESTAMP , но в её названии явно отражено, что она возвращает. Функция statement_timestamp() возвращает время начала текущего оператора (более точно, время получения последнего командного сообщения от клиента). Функции statement_timestamp() и transaction_timestamp() возвращают одно и то же значение в первой команде транзакции, но в последующих их показания будут расходиться. Функция clock_timestamp() возвращает фактическое текущее время, так что её значение меняется в рамках одной команды SQL. Функция timeofday() существует в Postgres Pro по историческим причинам и, подобно clock_timestamp() , она возвращает фактическое текущее время, но представленное в виде форматированной строки типа text , а не значения timestamp with time zone . Функция now() — традиционный для Postgres Pro эквивалент функции transaction_timestamp() .
Все типы даты/времени также принимают специальное буквальное значение now , подразумевающее текущую дату и время (тоже на момент начала транзакции). Таким образом, результат следующих трёх операторов будет одинаковым:
Подсказка
Не используйте третью форму для указания значения, которое будет вычисляться позднее, например, в предложении DEFAULT для столбца таблицы. Система преобразует now в значение timestamp в момент разбора константы, поэтому когда будет вставляться такое значение по умолчанию, в соответствующем столбце окажется время создания таблицы! Первые две формы будут вычисляться, только когда значение по умолчанию потребуется, так как это вызовы функции. Поэтому они дадут желаемый результат при добавлении строки в таблицу. (См. также Подраздел 8.5.1.4.)
9.9.6. Задержка выполнения
В случае необходимости выполнение серверного процесса можно приостановить, используя следующие функции:
Функция pg_sleep переводит процесс текущего сеанса в спящее состояние на указанное число секунд (это число может быть дробным). В дополнение к ней для удобства добавлены две функции: pg_sleep_for , принимающая время задержки в типе interval , и pg_sleep_until , позволяющая задать определённое время выхода из спящего состояния. Например:
Примечание
Действительное разрешение интервала задержки зависит от платформы; обычно это 0.01. Фактическая длительность задержки не будет меньше указанного времени, но может быть больше, в зависимости, например от нагрузки на сервер. В частности, не гарантируется, что pg_sleep_until проснётся именно в указанное время, но она точно не проснётся раньше.
Предупреждение
Прежде чем вызывать pg_sleep или её вариации, убедитесь в том, что в текущем сеансе нет ненужных блокировок. В противном случае в состояние ожидания могут перейти и другие сеансы, так что это отразится на системе в целом.
SQL extract — Get a Field from a Date or Time Value
SQL extract provides access to the components of temporal data types—i.e. date , time , timestamp , and interval .
SQL extract uses the keyword from to separate the field name from the value.
The field names are SQL keywords too—you must not put them in double or single quotes.
SQL extract returns an exact numeric value. For second , it also includes fractions.0 The following table lists the extract fields defined by the SQL standard.
| Meaning | extract field |
|---|---|
| Year | YEAR |
| Month | MONTH |
| Day of month | DAY |
| 24 hour | HOUR |
| Minute | MINUTE |
| Seconds (including fractions) | SECOND |
| Time zone hour | TIMEZONE_HOUR |
| Time zone minute | TIMEZONE_MINUTE |
Related Features
Extract can only get single fields. To extract the full date (year, month, day) or time (hour, minute, second) from a timestamp , cast can be used:1
This is particularly useful for the group by clause. In the where clause, it is often the wrong choice. For more on this, see “Inappropriate Use in The Where Clause” below.
Caution: Oracle Database
The Oracle database doesn’t have a predefined date type without time components. Even the Oracle type date has a time component—in this regard, Oracle date is more like the standard timestamp .2 A type conversion ( cast ) to date therefore does not drop the time component in the Oracle database.
To use the date only—without time components—it is common practice to use the proprietary trunc function to set all time fields to zero ( 0 ):
Note that the result still has the time components—they are just set to zero. The effect is basically like the following cast expression in standard SQL:
Compatibility
SQL extract was available in SQL-92 (intermediate) and is now part of the optional feature F052, “Intervals and datetime arithmetic”. Despite its maturity and relevance, extract is still not supported by all major databases yet.
extract(… from <datetime>)
extract(… from <interval>)
cast(<timestamp> as date)
cast(<timestamp> as time)
- No time zone fields • SECOND does not include fractions
- No time zone fields
- No time zone fields • SECOND does not include fractions. Use SECOND_MICROSECOND
- Doesn’t support data type interval
- See “Caution: Oracle Database” above
- Use date(<timestamp>) instead
- Use time(<timestamp>) instead
Related Anti-Patterns
String Formatting Functions
A very common anti-pattern is to use string formatting functions (e.g. to_char ) instead of extract to get single date or time fields. These string function often apply unintended formatting such as leading spaces or zeros, or a comma ( , ) instead of a period ( . ) as decimal mark based on the current locale.
This environmentally dependent behavior can lead to bugs that don’t show up in all environments and are thus hard to correct.
Inappropriate Use in The Where Clause
Consider the following anti-pattern:
This anti-pattern is often followed to avoid specifying the “last moment of” the relevant time frame. This is, in fact, an important and desirable goal because specifying the “last moment of” is actually impossible:
Time units are not uniform
It is well known that the length of a month is not uniform. The rules for leap years are known at least in part. Just considering these facts, any “last moment of” could be determined algorithmically.
But there are also leap seconds, which are irregular. They are inserted occasionally on demand. For example, the last UTC second of the year 2016 happened to be 23:59:60. If you consider a day to end at 23:59:59 UTC, you might miss a whole second.3
Due to the irregularity and the rather short lead time when it comes to leap seconds insertions—the 2016 leap second was announced less than six month in advance—it is impossible to tell the last moment of a month for more than six month in advance.
In addition to this more or less theoretic special case, it is also good to avoid the need to specify the “last moment of” because it is rather awkward to calculate.
The time component’s resolution is unknown (at least in the future)
Even if you have correctly determined the last day and last second of a period, you might need to include a sufficient number of fractional digits to specify the “last moment of” a period. If you know that the type of the relevant column does not allow fractions (e.g., timestamp(0) ), you don’t need to consider any fractions right now. But if the type is changed to timestamp(6) later, chances are the “last moment of” assumptions are not updated.
It is therefore a very good practice to avoid using the “last moment of”. Using extract , cast , or string formatting functions is just the wrong approach to reaching that goal.
Support My Work
The following where clause is equivalent to the extract example from above and still avoids specifying the “last moment of” the year 2016:
Note the pattern: use an inclusive comparison ( >= ) for the lower bound but an excluding comparison ( < ) for the upper bound. Consequently you need to specify the first moment to be excluded from the result as the upper bound. The inclusive/exclusive pattern avoids the need to specify the “last moment of” the relevant time frame by using the less troublesome “first moment of” twice.
Note that SQL’s between cannot be used for this pattern because between includes both boundary values.
Compared to the extract solution, the inclusive/exclusive condition has two advantages:
It works for arbitrary time frames
You can easily select a single month, day, …—even if it is not aligned to the calendar. Consider how you would implement the following example using extract , to_char , or something similar:4
It can use an index on the date/time column
An index on some_date is mostly useless if the where clause wraps the indexed columns through a function or expression like extract .5 The explicit inclusive/exclusive pattern can make use of such an index. Learn more about indexing use at Use The Index, Luke!
Proprietary Extensions: Additional Fields
Some databases support further extract fields. The following table summarizes the more commonly available proprietary extract fields. Please note that these are proprietary extensions: they may behave differently from product to product. The field week , for example, works in three tested database, but returns a different result for each of them.
EXTRACT ФУНКЦИЯ
Oracle/PLSQL функция EXTRACT извлекает значение из даты или значения интервала.
Синтаксис
Синтаксис Oracle/PLSQL функции EXTRACT:
Примечание
- Функция EXTRACT возвращает numeric значение, когда предоставляются следующие параметры: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_MINUTE.
- Функция EXTRACT возвращает VARCHAR2, когда предоставляются параметры TIMEZONE_REGION или TIMEZONE_ABBR (поскольку возвращается имя часового пояса или информация об аббревиатуре).
- Вы можете извлечь только YEAR, MONTH, и DAY из даты.
- Вы можете извлечь только TIMEZONE_HOUR и TIMEZONE_MINUTE из даты/времени с типом данных часового пояса.
Применение
Функцию EXTRACT можно использовать в следующих версиях Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
Пример
Рассмотрим несколько примеров функции EXTRACT и изучим, как использовать функцию EXTRACT в Oracle/PLSQL.