9.9. Операторы и функции даты/времени #

Все существующие функции для обработки даты/времени перечислены в Таблице 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 + integerdate

Добавляет к дате заданное число дней

date '2001-09-28' + 72001-10-05

date + intervaltimestamp

Добавляет к дате интервал

date '2001-09-28' + interval '1 hour'2001-09-28 01:00:00

date + timetimestamp

Добавляет к дате время

date '2001-09-28' + time '03:00'2001-09-28 03:00:00

interval + intervalinterval

Складывает интервалы

interval '1 day' + interval '1 hour'1 day 01:00:00

timestamp + intervaltimestamp

Добавляет к отметке времени интервал

timestamp '2001-09-28 01:00' + interval '23 hours'2001-09-29 00:00:00

time + intervaltime

Добавляет к времени интервал

time '01:00' + interval '3 hours'04:00:00

- intervalinterval

Меняет направление интервала

- interval '23 hours'-23:00:00

date - dateinteger

Вычитает даты, выдавая разницу в днях

date '2001-10-01' - date '2001-09-28'3

date - integerdate

Вычитает из даты заданное число дней

date '2001-10-01' - 72001-09-24

date - intervaltimestamp

Вычитает из даты интервал

date '2001-09-28' - interval '1 hour'2001-09-27 23:00:00

time - timeinterval

Вычитает из одного времени другое

time '05:00' - time '03:00'02:00:00

time - intervaltime

Вычитает из времени интервал

time '05:00' - interval '2 hours'03:00:00

timestamp - intervaltimestamp

Вычитает из отметки времени интервал

timestamp '2001-09-28 23:00' - interval '23 hours'2001-09-28 00:00:00

interval - intervalinterval

Вычитает из одного интервала другой

interval '1 day' - interval '1 hour'1 day -01:00:00

timestamp - timestampinterval

Вычитает из одной отметки времени другую (преобразуя 24-часовые интервалы в дни подобно justify_hours())

timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'63 days 15:00:00

interval * double precisioninterval

Умножает интервал на скалярное значение

interval '1 second' * 90000:15:00

interval '1 day' * 2121 days

interval '1 hour' * 3.503:30:00

interval / double precisioninterval

Делит интервал на скалярное значение

interval '1 hour' / 1.500:40:00


Таблица 9.33. Функции даты/времени

Функция

Описание

Пример(ы)

age ( timestamp, timestamp ) → interval

Вычитает аргументы и выдаёт «символический» результат с годами и месяцами, а не просто днями

age(timestamp '2001-04-10', timestamp '1957-06-13')43 years 9 mons 27 days (43 года 9 месяцев 27 дней)

age ( timestamp ) → interval

Вычитает аргумент из current_date (полночь текущего дня)

age(timestamp '1957-06-13')62 years 6 mons 10 days (62 года 6 месяцев 10 дней)

clock_timestamp ( ) → timestamp with time zone

Текущая дата и время (меняется в процессе выполнения операторов); см. Подраздел 9.9.5

clock_timestamp()2019-12-23 14:39:53.662522-05

current_datedate

Текущая дата; см. Подраздел 9.9.5

current_date2019-12-23

current_timetime with time zone

Текущее время суток; см. Подраздел 9.9.5

current_time14:39:53.662522-05

current_time ( integer ) → time with time zone

Текущее время суток (с ограниченной точностью); см. Подраздел 9.9.5

current_time(2)14:39:53.66-05

current_timestamptimestamp with time zone

Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5

current_timestamp2019-12-23 14:39:53.662522-05

current_timestamp ( integer ) → timestamp with time zone

Текущие дата и время (на момент начала транзакции; с ограниченной точностью); см. Подраздел 9.9.5

current_timestamp(0)2019-12-23 14:39:53-05

date_add ( timestamp with time zone, interval [, text] ) → timestamp with time zone

Добавляет значение типа interval к значению типа timestamp with time zone, вычисляя время суток и корректировки перехода на летнее время в соответствии с часовым поясом, указанным в третьем аргументе, или, если он опущен, с текущим параметром TimeZone. Форма с двумя аргументами эквивалентна оператору timestamp with time zone + interval.

date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw')2021-10-31 23:00:00+00

date_bin ( interval, timestamp, timestamp ) → timestamp

Подгоняет заданное значение под интервал, отсчитывая от указанного начального момента; см. Подраздел 9.9.3

date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')2001-02-16 20:35:00

date_part ( text, timestamp ) → double precision

Возвращает поле даты/времени (равнозначно extract); см. Подраздел 9.9.1

date_part('hour', timestamp '2001-02-16 20:38:40')20

date_part ( text, interval ) → double precision

Возвращает поле интервала (равнозначно extract); см. Подраздел 9.9.1

date_part('month', interval '2 years 3 months')3

date_subtract ( timestamp with time zone, interval [, text] ) → timestamp with time zone

Вычитает значение типа interval из значения типа timestamp with time zone, вычисляя время суток и корректировки перехода на летнее время в соответствии с часовым поясом, указанным в третьем аргументе, или текущим параметром TimeZone, если он опущен. Форма с двумя аргументами эквивалентна оператору timestamp with time zone - interval.

date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw')2021-10-30 22:00:00+00

date_trunc ( text, timestamp ) → timestamp

Отсекает компоненты даты до заданной точности; см. Подраздел 9.9.2

date_trunc('hour', timestamp '2001-02-16 20:38:40')2001-02-16 20:00:00

date_trunc ( text, timestamp with time zone, text ) → timestamp with time zone

Отсекает компоненты даты до заданной точности в указанном часовом поясе; см. Подраздел 9.9.2

date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')2001-02-16 13:00:00+00

date_trunc ( text, interval ) → interval

Отсекает компоненты даты до заданной точности; см. Подраздел 9.9.2

date_trunc('hour', interval '2 days 3 hours 40 minutes')2 days 03:00:00

extract ( field from timestamp ) → numeric

Возвращает поле даты/времени; см. Подраздел 9.9.1

extract(hour from timestamp '2001-02-16 20:38:40')20

extract ( field from interval ) → numeric

Возвращает поле интервала; см. Подраздел 9.9.1

extract(month from interval '2 years 3 months')3

isfinite ( date ) → boolean

Проверяет конечность даты (её отличие от +/-бесконечности)

isfinite(date '2001-02-16')true

isfinite ( timestamp ) → boolean

Проверяет конечность времени (его отличие от +/-бесконечности)

isfinite(timestamp 'infinity')false

isfinite ( interval ) → boolean

Проверяет конечность интервала (его отличие от +/-бесконечности)

isfinite(interval '4 hours')true

justify_days ( interval ) → interval

Преобразует интервал так, что каждый 30-дневный период считается одним месяцем

justify_days(interval '1 year 65 days')1 year 2 mons 5 days

justify_hours ( interval ) → interval

Преобразует интервал так, что каждый 24-часовой период считается одним днём

justify_hours(interval '50 hours 10 minutes')2 days 02:10:00

justify_interval ( interval ) → interval

Преобразует интервал с применением justify_days и justify_hours и дополнительно корректирует знаки

justify_interval(interval '1 mon -1 hour')29 days 23:00:00 (29 дней 23:00:00)

localtimetime

Текущее время суток; см. Подраздел 9.9.5

localtime14:39:53.662522

localtime ( integer ) → time

Текущее время суток (с ограниченной точностью); см. Подраздел 9.9.5

localtime(0)14:39:53

localtimestamptimestamp

Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5

localtimestamp2019-12-23 14:39:53.662522

localtimestamp ( integer ) → timestamp

Текущие дата и время (на момент начала транзакции; с ограниченной точностью); см. Подраздел 9.9.5

localtimestamp(2)2019-12-23 14:39:53.66

make_date ( year int, month int, day int ) → date

Образует дату из полей: year (год), month (месяц) и day (день) (отрицательное значение поля year означает год до н. э.)

make_date(2013, 7, 15)2013-07-15

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_interval(days => 10)10 days

make_time ( hour int, min int, sec double precision ) → time

Образует время из полей: hour (час), minute (минута) и sec (секунда)

make_time(8, 15, 23.5)08:15:23.5

make_timestamp ( year int, month int, day int, hour int, min int, sec double precision ) → timestamp

Образует момент времени из полей: year (год), month (месяц), day (день), hour (час), minute (минута) и sec (секунда) (отрицательное значение поля year означает год до н. э.)

make_timestamp(2013, 7, 15, 8, 15, 23.5)2013-07-15 08:15:23.5

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

make_timestamptz(2013, 7, 15, 8, 15, 23.5, 'America/New_York')2013-07-15 13:15:23.5+01

now ( ) → timestamp with time zone

Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5

now()2019-12-23 14:39:53.662522-05

statement_timestamp ( ) → timestamp with time zone

Текущая дата и время (на момент начала текущего оператора); см. Подраздел 9.9.5

statement_timestamp()2019-12-23 14:39:53.662522-05

timeofday ( ) → text

Текущая дата и время (как clock_timestamp, но в виде строки типа text); см. Подраздел 9.9.5

timeofday()Mon Dec 23 14:39:53.662522 2019 EST

transaction_timestamp ( ) → timestamp with time zone

Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5

transaction_timestamp()2019-12-23 14:39:53.662522-05

to_timestamp ( double precision ) → timestamp with time zone

Преобразует время эпохи Unix (число секунд с 1970-01-01 00:00:00+00) в дату/время с часовым поясом

to_timestamp(1284352323)2010-09-13 04:32:03+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 или timestamp with time zone значения interval (или при вычитании из него interval), поля месяцев, дней и микросекунд обрабатываются по очереди. Сначала дата в метке времени изменяется на указанное количество месяцев, сохраняя день неизменным, если он не превышает количество дней в новом месяце, иначе будет задан последний день нового месяца. (Например, 31 марта плюс 1 месяц становится 30 апреля, а 31 марта плюс 2 месяца — 31 мая.) Затем дата изменяется на указанное количество дней. На обоих этих этапах местное время суток остаётся неизменным. Наконец, если указано ненулевое значение микросекунд, оно добавляется или вычитается буквально. При выполнении арифметических действий со значением типа timestamp with time zone в часовом поясе с переходом на летнее время это означает, что добавление или вычитание (например) 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(поле FROM источник)

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

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

century

Век; для значений 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
day

День месяца (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
decade

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

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

День недели, считая с воскресенья (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
epoch

Для значений 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
isodow

День недели, считая с понедельника (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
minute

Минуты (0–59)

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

Номер месяца в году (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
quarter

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

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

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

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

Смещение часового пояса от 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
year

Поле года. Учтите, что года 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 для значений timestamp; epoch, hour, day, year, decade, century и millennium для значений interval). Для других полей возвращается 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 LOCAL #

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

Таблица 9.34. Варианты AT TIME ZONE и AT LOCAL

Оператор

Описание

Пример(ы)

timestamp without time zone AT TIME ZONE часовой_поясtimestamp with time zone

Переводит значение даты/времени без часового пояса в дату/время с часовым поясом, в предположении, что входное значение задано в указанном поясе.

timestamp '2001-02-16 20:38:40' at time zone 'America/Denver'2001-02-17 03:38:40+00

timestamp without time zone AT LOCALtimestamp with time zone

Переводит значение даты/времени без часового пояса в дату/время с часовым поясом, заданным значением TimeZone сеанса.

timestamp '2001-02-16 20:38:40' at local2001-02-17 03:38:40+00

timestamp with time zone AT TIME ZONE часовой_поясtimestamp without time zone

Переводит значение даты/времени с часовым поясом в дату/время без часового пояса, которое соответствует входному значению в указанном поясе.

timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver'2001-02-16 18:38:40

timestamp with time zone AT LOCALtimestamp without time zone

Переводит значение даты/времени с часовым поясом в дату/время без часового пояса, поскольку время будет отображаться с часовым поясом, указанным в качестве значения TimeZone сеанса.

timestamp with time zone '2001-02-16 20:38:40-05' at local2001-02-16 18:38:40

time with time zone AT TIME ZONE часовой_поясtime with time zone

Переводит значение времени с часовым поясом в другой часовой пояс. Так как время задаётся без даты, в расчёте используется действующее в данный момент смещение указанного часового пояса от UTC.

time with time zone '05:34:17-05' at time zone 'UTC'10:34:17+00

time with time zone AT LOCALtime with time zone

Переводит значение времени с часовым поясом в другой часовой пояс. Так как время задаётся без даты, в расчёте используется действующее в данный момент смещение значения TimeZone сеанса от UTC.

Предположим, что для TimeZone сеанса установлено значение UTC:

time with time zone '05:34:17-05' at local10:34:17+00


В этих выражениях желаемый часовой_пояс можно задать либо в виде текстовой строки (например, 'America/Los_Angeles'), либо как интервал (например, INTERVAL '-08:00'). В первом случае название часового пояса можно указать любым из способов, описанных в Подразделе 8.5.3. Вариант с интервалом полезен, только если для часового пояса смещение от UTC всегда постоянно, что на практике встречается нечасто.

Синтаксис AT LOCAL можно использовать в качестве сокращённой записи для AT TIME ZONE локальный, где локальный — это значение TimeZone сеанса.

Примеры (предполагается, что текущее значение параметра TimeZoneAmerica/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
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL;
Результат: 2001-02-16 17:38:40
SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;
Результат: 17:38:40

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

Пятый пример служит в качестве предостережения. Поскольку с входным значением не связана дата, преобразование выполняется с использованием текущей даты сеанса. Поэтому этот статический пример может показывать неверный результат в зависимости от времени года, поскольку в часовом поясе 'America/Los_Angeles' есть переход на летнее время.

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

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

Функция timezone(timestamp) равнозначна SQL-совместимой конструкции timestamp AT LOCAL.

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

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 или её вариации, убедитесь в том, что в текущем сеансе нет ненужных блокировок. В противном случае в состояние ожидания могут перейти и другие сеансы, так что это отразится на системе в целом.