Как извлечь время из даты sql
Перейти к содержимому

Как извлечь время из даты sql

  • автор:

Как извлечь время из даты в SQL

Извлечение времени из даты в SQL можно выполнить с помощью функции EXTRACT. Вот примеры использования:

 -- Извлечение часа из даты и времени SELECT EXTRACT(HOUR FROM my_datetime_column) AS hour FROM my_table; -- Извлечение минуты из даты и времени SELECT EXTRACT(MINUTE FROM my_datetime_column) AS minute FROM my_table; -- Извлечение секунды из даты и времени SELECT EXTRACT(SECOND FROM my_datetime_column) AS second FROM my_table; 

Здесь my_datetime_column — это столбец с датой и временем из вашей таблицы, а my_table — имя вашей таблицы. Замените их соответствующими значениями в вашем запросе. Эти запросы извлекут только час, минуту или секунду из ваших дат и времени. Если вам нужно извлечь другие части времени, такие как год, месяц или день, вы можете использовать соответствующие ключевые слова в функции EXTRACT. Успехи в извлечении времени из даты в SQL!

Детальный ответ

Привет ученику! Сегодня мы поговорим о том, как извлечь время из даты в SQL. Это очень важный навык, который пригодится тебе при работе с базами данных. Давай разберемся, как это делается. Наиболее распространенными типами данных для хранения дат и времени в SQL являются DATE и DATETIME . Они позволяют хранить и оперировать датами и временем в удобном формате. Сейчас я покажу тебе примеры, как можно извлечь время из этих типов данных.

Извлечение времени из типа данных DATE

Предположим, у нас есть таблица с названием orders , в которой есть столбец order_date типа DATE . Мы хотим извлечь только время из этого столбца order_date . Для этого мы можем использовать функцию DATE_FORMAT() следующим образом:

SELECT DATE_FORMAT(order_date, '%H:%i:%s') AS order_time FROM orders;

В результате выполнения этого запроса, мы получим столбец order_time со значениями времени в формате часы:минуты:секунды .

Извлечение времени из типа данных DATETIME

Тип данных DATETIME позволяет хранить и дату, и время в одном поле. Представим, что у нас есть таблица events , в которой есть столбец event_datetime типа DATETIME . Мы хотим извлечь только время из этого столбца. Для этого мы можем использовать функцию TIME() следующим образом:

SELECT TIME(event_datetime) AS event_time FROM events;

Этот запрос вернет нам столбец event_time со значениями только времени.

Извлечение других элементов времени

  • YEAR() : извлекает год из даты или времени
  • MONTH() : извлекает месяц (от 1 до 12) из даты или времени
  • DAY() : извлекает число (от 1 до 31) из даты или времени
  • HOUR() : извлекает час (от 0 до 23) из даты или времени
  • MINUTE() : извлекает минуту (от 0 до 59) из даты или времени
  • SECOND() : извлекает секунду (от 0 до 59) из даты или времени

Вот примеры использования этих функций:

SELECT YEAR(order_date) AS order_year, MONTH(order_date) AS order_month, DAY(order_date) AS order_day, HOUR(event_time) AS event_hour, MINUTE(event_time) AS event_minute, SECOND(event_time) AS event_second FROM orders;

Каждая из этих функций возвращает соответствующее значение из даты или времени.

Заключение

Теперь ты знаешь, как извлечь время из даты в SQL. Это очень полезный навык при работе с базами данных. Мы рассмотрели, как извлекать время из типов данных DATE и DATETIME с помощью функций DATE_FORMAT() и TIME() . Также мы узнали, как извлечь другие элементы времени с помощью функций YEAR() , MONTH() , DAY() , HOUR() , MINUTE() и SECOND() .

Удачи в изучении SQL! Если у тебя возникнут еще вопросы, не стесняйся задавать их.

Как извлечь время из даты 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.

  1. No time zone fields • SECOND does not include fractions
  2. No time zone fields
  3. No time zone fields • SECOND does not include fractions. Use SECOND_MICROSECOND
  4. Doesn’t support data type interval
  5. See “Caution: Oracle Database” above
  6. Use date() instead
  7. Use time() 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.

Похожие публикации:

  1. Как экранировать кавычки в sql
  2. Какие лицензии нужны для 1с sql
  3. Какой sql server поставить на windows 10
  4. Какую функцию выполняет оператор distinct sql

Как получить время из даты SQL: полезные советы и примеры

В этом примере, замените «your_date_column» на имя столбца таблицы, содержащего дату, с которой вы хотите извлечь время. Замените «your_table» на имя таблицы, в которой находится столбец.

Детальный ответ

Как получить время из даты SQL

Если у вас есть столбец с типом данных «дата и время» в вашей базе данных SQL и вам нужно получить только время из этой даты, вам нужно использовать функцию, специфичную для вашей базы данных.

MySQL

В MySQL вы можете использовать функцию TIME() для извлечения времени из даты.

 SELECT TIME(datetime_column) AS time_column FROM your_table; 

Здесь datetime_column — это столбец с типом данных «дата и время» в вашей таблице, а your_table — это имя вашей таблицы.

PostgreSQL

В PostgreSQL вы можете использовать функцию EXTRACT() для получения времени из даты.

 SELECT EXTRACT(TIME FROM timestamp_column) AS time_column FROM your_table; 

Здесь timestamp_column — это столбец с типом данных «дата и время», а your_table — имя вашей таблицы.

Microsoft SQL Server

В Microsoft SQL Server вы можете использовать функцию CONVERT() в сочетании с FORMAT() для извлечения времени из даты.

 SELECT CONVERT(TIME, CONVERT(VARCHAR, datetime_column, 108)) AS time_column FROM your_table; 

Здесь datetime_column — это столбец с типом данных «дата и время», а your_table — имя вашей таблицы.

Oracle

В Oracle вы можете использовать функцию TO_CHAR() для получения времени из даты.

 SELECT TO_CHAR(datetime_column, 'HH24:MI:SS') AS time_column FROM your_table; 

Здесь datetime_column — это столбец с типом данных «дата и время», а your_table — имя вашей таблицы.

SQLite

В SQLite вы можете использовать функцию strftime() для извлечения времени из даты.

 SELECT strftime('%H:%M:%S', datetime_column) AS time_column FROM your_table; 

Здесь datetime_column — это столбец с типом данных «дата и время», а your_table — имя вашей таблицы.

Примеры

Предположим, что у нас есть таблица «orders» со столбцом «order_date», содержащим как дату, так и время заказа. Мы хотим получить только время заказов.

MySQL
 SELECT TIME(order_date) AS order_time FROM orders; 
PostgreSQL
 SELECT EXTRACT(TIME FROM order_date) AS order_time FROM orders; 
Microsoft SQL Server
 SELECT CONVERT(TIME, CONVERT(VARCHAR, order_date, 108)) AS order_time FROM orders; 
Oracle
 SELECT TO_CHAR(order_date, 'HH24:MI:SS') AS order_time FROM orders; 
SQLite
 SELECT strftime('%H:%M:%S', order_date) AS order_time FROM orders; 

Это примеры кода для каждой из баз данных, которые помогут вам получить только время из даты в SQL. Убедитесь, что замените «datetime_column» на соответствующие столбцы в вашей таблице и «your_table» на имя вашей таблицы, чтобы получить ожидаемый результат. Знание того, как получить время из даты в SQL, может быть очень полезным при работе с данными, содержащими дату и время, и когда вам необходимо выполнить операции только с временем.

Как извлечь время из даты sql

Все существующие функции для обработки даты/времени перечислены в Таблице 9.32, а подробнее они описаны в следующих подразделах. Поведение основных арифметических операторов ( + , * и т. д.) описано в Таблице 9.31. Функции форматирования этих типов данных были перечислены в Разделе 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.31. Операторы даты/времени

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.32. Функции даты/времени

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 :

(начало1, конец1) OVERLAPS (начало2, конец2) (начало1, длительность1) OVERLAPS (начало2, длительность2)

Его результатом будет true, когда два периода времени (определённые своими границами) пересекаются, и false в противном случае. Границы периода можно задать либо в виде пары дат, времени или дат со временем, либо как дату, время (или дату со временем) c интервалом. Когда указывается пара значений, первым может быть и начало, и конец периода: OVERLAPS автоматически считает началом периода меньшее значение. Периоды времени считаются наполовину открытыми, т. е. начало время < конец , если только начало и конец не равны — в этом случае период представляет один момент времени. Это означает, например, что два периода, имеющие только общую границу, не будут считаться пересекающимися.

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Результат:true SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Результат:false SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS (DATE '2001-10-30', DATE '2001-10-31'); Результат:false SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS (DATE '2001-10-30', DATE '2001-10-31'); Результат:true

При добавлении к метке типа timestamp with time zone значения interval (или при вычитании из него interval ), поле дней в этой дате увеличивается (или уменьшается) на указанное число суток, а время суток остаётся неизменным. При пересечении границы перехода на летнее время (если в часовом поясе текущего сеанса производится этот переход) это означает, что interval ‘1 day’ и interval ’24 hours’ не обязательно будут равны. Например, в часовом поясе America/Denver :

SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day'; Результат: 2005-04-03 12:00:00-06 SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '24 hours'; Результат: 2005-04-03 13:00:00-06 

Эта разница объясняется тем, что 2005-04-03 02:00 в часовом поясе America/Denver произошёл переход на летнее время.

Обратите внимание на возможную неоднозначность в поле months в результате функции age , вызванную тем, что число дней в разных месяцах неодинаково. Вычисляя оставшиеся дни месяца, PostgreSQL рассматривает месяц меньшей из двух дат. Например, результатом 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’ ; между двумя заданными датами произошёл переход на летнее время:

SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') - EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'); Результат:10537200.000000 SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') - EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00')) / 60 / 60 / 24; Результат:121.9583333333333333 SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00'; Результат:121 days 23:00:00 SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00'); Результат:4 mons

9.9.1. EXTRACT , date_part

EXTRACT(field FROM source)

Функция extract извлекает поля, такие как год или час, из значений даты и времени. Источником должно быть заданное выражением значение типа timestamp , date , time или interval . (Значения timestamp и time могут быть как с указанием часового пояса, так и без.) Поле — это идентификатор или строка, с помощью которой выбирается поле для извлечения из значения источника. Для разных типов входных данных допустимы разные поля: например, поля меньше дней не могут быть извлечены из date , а поля дней и более не могут быть извлечены из time . Функция extract возвращает значения типа numeric .

Допустимы следующие имена полей:

Век; для значений interval — значение поля года, разделённое на сто

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13'); Результат:20 SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:21 SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); Результат:1 SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC'); Результат:-1 SELECT EXTRACT(CENTURY FROM INTERVAL '2001 years'); Результат:20

День месяца (1–31); для значений interval — количество дней

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:16 SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute'); Результат:40

Год, делённый на 10

SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:200

День недели, считая с воскресенья ( 0 ) до субботы ( 6 )

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:5

Заметьте, что в extract дни недели нумеруются не так, как в функции to_char(. ‘D’) . doy

День года (1–365/366)

SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:47

Для значений timestamp with time zone это число секунд с 1970-01-01 00:00:00 UTC (отрицательное для предшествующего времени); для значений date и timestamp — номинальное число секунд с 1970-01-01 00:00:00 без учёта часового пояса, переходов на летнее время и т. п.; для значений interval — общее количество секунд в интервале

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08'); Результат:982384720.120000 SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12'); Результат:982355920.120000 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); Результат:442800.000000

Преобразовать время эпохи назад, в значение timestamp with time zone , с помощью to_timestamp можно так:

SELECT to_timestamp(982384720.12); Результат:2001-02-17 04:38:40.12+00

Имейте в виду, что применяя to_timestamp к времени эпохи, извлечённому из значения date или timestamp , можно получить не вполне ожидаемый результат: эта функция подразумевает, что изначальное значение задано в часовом поясе UTC, но это может быть не так. hour

Поле часов (0–23 в метках времени, в интервалах — без ограничений)

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:20

День недели, считая с понедельника ( 1 ) до воскресенья ( 7 )

SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40'); Результат:7

Результат отличается от dow только для воскресенья. Такая нумерация соответствует ISO 8601. isoyear

Год с нумерацией недель согласно ISO 8601, на который приходится дата

SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01'); Результат:2005 SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02'); Результат:2006

Год по недельному календарю ISO начинается с понедельника недели, в которой оказывается 4 января, так что в начале января или в конце декабря год по ISO может отличаться от года по григорианскому календарю. Подробнее об этом рассказывается в описании поля week . julian

Юлианская дата, соответствующая дате или дате/времени. Значение будет дробным, если заданное время отличается от начала суток по местному времени. За дополнительной информацией обратитесь к Разделу B.7.

SELECT EXTRACT(JULIAN FROM DATE '2006-01-01'); Результат:2453737 SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00'); Результат:2453737.50000000000000000000

microseconds

Значение секунд с дробной частью, умноженное на 1 000 000; заметьте, что оно включает и целые секунды

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5'); Результат:28500000

millennium

Тысячелетие; для значений interval — значение поля года, разделённое на тысячу

SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:3 SELECT EXTRACT(MILLENNIUM FROM INTERVAL '2001 years'); Результат:2

Годы 20 века относятся ко второму тысячелетию. Третье тысячелетие началось 1 января 2001 г. milliseconds

Значение секунд с дробной частью, умноженное на 1 000; заметьте, что оно включает и целые секунды.

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5'); Результат:28500.000
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:38

Номер месяца в году (1–12), а для interval — остаток от деления числа месяцев на 12 (0–11)

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:2 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months'); Результат:3 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months'); Результат:1

Квартал (1–4), к которому относится дата

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:1

Секунды, включая дробную часть

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:40.000000 SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); Результат:28.500000

Смещение часового пояса от UTC, представленное в секундах. Положительные значения соответствуют часовым поясам к востоку от UTC, а отрицательные — к западу. (Строго говоря, в PostgreSQL используется не UTC, так как секунды координации не учитываются.) timezone_hour

Поле часов в смещении часового пояса timezone_minute

Поле минут в смещении часового пояса week

Номер недели в году по недельному календарю ISO 8601. По определению, недели ISO 8601 начинаются с понедельника, а первая неделя года включает 4 января этого года. Другими словами, первый четверг года всегда оказывается в 1 неделе этого года.

В системе нумерации недель ISO первые числа января могут относиться к 52-ой или 53-ей неделе предыдущего года, а последние числа декабря — к первой неделе следующего года. Например, 2005-01-01 относится к 53-ей неделе 2004 г., а 2006-01-01 — к 52-ей неделе 2005 г., тогда как 2012-12-31 включается в первую неделю 2013 г. Поэтому для получения согласованных результатов рекомендуется использовать поле isoyear в паре с week .

SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:7

Поле года. Учтите, что года 0 не было, и это следует иметь в виду, вычитая из годов нашей эры годы до нашей эры.

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:2001

При обработке значения interval функция extract создаёт значения полей, соответствующие интерпретации, которая используется функцией вывода интервала. При этом, если начать с ненормализованного представления интервала, могут быть получены неожиданные результаты, например:

SELECT INTERVAL '80 minutes'; Результат: 01:20:00 SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes'); Результат: 20 

Примечание

С аргументом +/-бесконечность extract возвращает +/-бесконечность для монотонно увеличивающихся полей ( epoch , julian , year , isoyear , decade , century и millennium ). Для других полей возвращается NULL. До версии 9.6 PostgreSQL возвращал ноль для всех случаев с бесконечными аргументами.

Функция extract в основном предназначена для вычислительных целей. Функции форматирования даты/времени описаны в Разделе 9.8.

Функция date_part эмулирует традиционный для Ingres эквивалент стандартной SQL -функции extract :

date_part('поле', источник)

Заметьте, что здесь параметр поле должен быть строковым значением, а не именем. Функция date_part воспринимает те же поля, что и extract . По историческим причинам функция date_part возвращает значения типа double precision . В некоторых случаях это может привести к потере точности. Поэтому вместо неё рекомендуется использовать функцию extract .

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40'); Результат:16 SELECT date_part('hour', INTERVAL '4 hours 3 minutes'); Результат:4

9.9.2. date_trunc

Функция date_trunc работает подобно trunc для чисел.

date_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 ):

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40'); Результат: 2001-02-16 20:00:00 SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40'); Результат: 2001-01-01 00:00:00 SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00'); Результат: 2001-02-16 00:00:00-05 SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney'); Результат: 2001-02-16 08:00:00-05 SELECT date_trunc('hour', INTERVAL '3 days 02:47:33'); Результат: 3 days 02:00:00 

9.9.3. date_bin

Функция date_bin « подгоняет » заданное значение под интервал (шаг), отсчитывая от указанного начального момента.

date_bin(шаг, значение, источник)

Здесь значение — выражение типа timestamp , timestamp with time zone . (Значения типов date автоматически приводятся к типу timestamp .) В качестве шага передаётся выражение типа interval . Результат этой функции, также имеющий тип timestamp , timestamp with time zone или interval , — начало интервала, под который подгоняется значение .

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01'); Результат: 2020-02-11 15:30:00 SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30'); Результат: 2020-02-11 15:32:30 

В случае целых единиц измерения (1 минута, 1 час и т. д.), она даёт тот же результат, что и аналогичный вызов функции date_trunc , с той лишь разницей, что date_bin может округлить дату до произвольного интервала.

Параметр шаг интервала должен быть больше нуля и задаваться в единицах измерения меньше месяца.

9.9.4. AT TIME ZONE

Оператор AT TIME ZONE преобразует дату/время без часового пояса в дату/время с часовым поясом и обратно, а также пересчитывает значения time with time zone для различных часовых поясов. Его вариации показаны в Таблице 9.33.

Таблица 9.33. Разновидности 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 ):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver'; Результат: 2001-02-16 19:38:40-08 SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver'; Результат: 2001-02-16 18:38:40 SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago'; Результат: 2001-02-16 05:38:40 

В первом примере для значения, заданного без часового пояса, указывается часовой пояс и полученное время выводится в текущем часовом поясе (заданном параметром TimeZone ). Во втором примере значение времени смещается в заданный часовой пояс и выдаётся без указания часового пояса. Это позволяет хранить и выводить значения с часовым поясом, отличным от текущего. В третьем примере время в часовом поясе Токио преобразуется для часового пояса Чикаго.

Функция timezone ( часовой_пояс , время ) равнозначна SQL-совместимой конструкции время AT TIME ZONE часовой_пояс .

9.9.5. Текущая дата/время

PostgreSQL предоставляет набор функций, результат которых зависит от текущей даты и времени. Все следующие функции соответствуют стандарту SQL и возвращают значения, отражающие время начала текущей транзакции:

CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TIME(точность) CURRENT_TIMESTAMP(точность) LOCALTIME LOCALTIMESTAMP LOCALTIME(точность) LOCALTIMESTAMP(точность)

CURRENT_TIME и CURRENT_TIMESTAMP возвращают время с часовым поясом. В результатах LOCALTIME и LOCALTIMESTAMP нет информации о часовом поясе.

CURRENT_TIME , CURRENT_TIMESTAMP , LOCALTIME и LOCALTIMESTAMP могут принимать необязательный параметр точности, определяющий, до какого знака после запятой следует округлять поле секунд. Если этот параметр отсутствует, результат будет иметь максимально возможную точность.

SELECT CURRENT_TIME; Результат: 14:39:53.662522-05 SELECT CURRENT_DATE; Результат: 2019-12-23 SELECT CURRENT_TIMESTAMP; Результат: 2019-12-23 14:39:53.662522-05 SELECT CURRENT_TIMESTAMP(2); Результат: 2019-12-23 14:39:53.66-05 SELECT LOCALTIMESTAMP; Результат: 2019-12-23 14:39:53.662522 

Так как эти функции возвращают время начала текущей транзакции, во время транзакции эти значения не меняются. Это считается не ошибкой, а особенностью реализации: цель такого поведения в том, чтобы в одной транзакции « текущее » время было одинаковым и для разных изменений в одной транзакций записывалась одна отметка времени.

Примечание

В других СУБД эти значения могут изменяться чаще.

В PostgreSQL есть также функции, возвращающие время начала текущего оператора, а также текущее время в момент вызова функции. Таким образом, в PostgreSQL есть следующие функции, не описанные в стандарте SQL:

transaction_timestamp() statement_timestamp() clock_timestamp() timeofday() now()

Функция transaction_timestamp() равнозначна конструкции CURRENT_TIMESTAMP , но в её названии явно отражено, что она возвращает. Функция statement_timestamp() возвращает время начала текущего оператора (более точно, время получения последнего командного сообщения от клиента). Функции statement_timestamp() и transaction_timestamp() возвращают одно и то же значение в первой команде транзакции, но в последующих их показания будут расходиться. Функция clock_timestamp() возвращает фактическое текущее время, так что её значение меняется в рамках одной команды SQL. Функция timeofday() существует в PostgreSQL по историческим причинам и, подобно clock_timestamp() , она возвращает фактическое текущее время, но представленное в виде форматированной строки типа text , а не значения timestamp with time zone . Функция now() — традиционный для PostgreSQL эквивалент функции transaction_timestamp() .

Все типы даты/времени также принимают специальное буквальное значение now , подразумевающее текущую дату и время (тоже на момент начала транзакции). Таким образом, результат следующих трёх операторов будет одинаковым:

SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP 'now'; -- см. замечание ниже

Подсказка

Не используйте третью форму для указания значения, которое будет вычисляться позднее, например, в предложении DEFAULT для столбца таблицы. Система преобразует now в значение timestamp в момент разбора константы, поэтому когда будет вставляться такое значение по умолчанию, в соответствующем столбце окажется время создания таблицы! Первые две формы будут вычисляться, только когда значение по умолчанию потребуется, так как это вызовы функции. Поэтому они дадут желаемый результат при добавлении строки в таблицу. (См. также Подраздел 8.5.1.4.)

9.9.6. Задержка выполнения

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

pg_sleep( double precision ) pg_sleep_for( interval ) pg_sleep_until( timestamp with time zone )

Функция pg_sleep переводит процесс текущего сеанса в спящее состояние на указанное число секунд (это число может быть дробным). В дополнение к ней для удобства добавлены две функции: pg_sleep_for , принимающая время задержки в типе interval , и pg_sleep_until , позволяющая задать определённое время выхода из спящего состояния. Например:

SELECT pg_sleep(1.5); SELECT pg_sleep_for('5 minutes'); SELECT pg_sleep_until('tomorrow 03:00');

Примечание

Действительное разрешение интервала задержки зависит от платформы; обычно это 0.01. Фактическая длительность задержки не будет меньше указанного времени, но может быть больше, в зависимости, например от нагрузки на сервер. В частности, не гарантируется, что pg_sleep_until проснётся именно в указанное время, но она точно не проснётся раньше.

Предупреждение

Прежде чем вызывать pg_sleep или её вариации, убедитесь в том, что в текущем сеансе нет ненужных блокировок. В противном случае в состояние ожидания могут перейти и другие сеансы, так что это отразится на системе в целом.

Пред. Наверх След.
9.8. Функции форматирования данных Начало 9.10. Функции для перечислений

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *