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. Операторы даты/времени
Оператор Описание Пример(ы) |
---|
Добавляет к дате заданное число дней
|
Добавляет к дате интервал
|
Добавляет к дате время
|
Складывает интервалы
|
Добавляет к отметке времени интервал
|
Добавляет к времени интервал
|
Меняет направление интервала
|
Вычитает даты, выдавая разницу в днях
|
Вычитает из даты заданное число дней
|
Вычитает из даты интервал
|
Вычитает из одного времени другое
|
Вычитает из времени интервал
|
Вычитает из отметки времени интервал
|
Вычитает из одного интервала другой
|
Вычитает из одной отметки времени другую (преобразуя 24-часовые интервалы в дни подобно
|
Умножает интервал на скалярное значение
|
Делит интервал на скалярное значение
|
Таблица 9.33. Функции даты/времени
Функция Описание Пример(ы) |
---|
Вычитает аргументы и выдаёт «символический» результат с годами и месяцами, а не просто днями
|
Вычитает аргумент из
|
Текущая дата и время (меняется в процессе выполнения операторов); см. Подраздел 9.9.5
|
Текущая дата; см. Подраздел 9.9.5
|
Текущее время суток; см. Подраздел 9.9.5
|
Текущее время суток (с ограниченной точностью); см. Подраздел 9.9.5
|
Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5
|
Текущие дата и время (на момент начала транзакции; с ограниченной точностью); см. Подраздел 9.9.5
|
Добавляет значение типа
|
Подгоняет заданное значение под интервал, отсчитывая от указанного начального момента; см. Подраздел 9.9.3
|
Возвращает поле даты/времени (равнозначно
|
Возвращает поле интервала (равнозначно
|
Вычитает значение типа
|
Отсекает компоненты даты до заданной точности; см. Подраздел 9.9.2
|
Отсекает компоненты даты до заданной точности в указанном часовом поясе; см. Подраздел 9.9.2
|
Отсекает компоненты даты до заданной точности; см. Подраздел 9.9.2
|
Возвращает поле даты/времени; см. Подраздел 9.9.1
|
Возвращает поле интервала; см. Подраздел 9.9.1
|
Проверяет конечность даты (её отличие от +/-бесконечности)
|
Проверяет конечность времени (его отличие от +/-бесконечности)
|
Проверяет конечность интервала (его отличие от +/-бесконечности)
|
Преобразует интервал так, что каждый 30-дневный период считается одним месяцем
|
Преобразует интервал так, что каждый 24-часовой период считается одним днём
|
Преобразует интервал с применением
|
Текущее время суток; см. Подраздел 9.9.5
|
Текущее время суток (с ограниченной точностью); см. Подраздел 9.9.5
|
Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5
|
Текущие дата и время (на момент начала транзакции; с ограниченной точностью); см. Подраздел 9.9.5
|
Образует дату из полей: year (год), month (месяц) и day (день) (отрицательное значение поля year означает год до н. э.)
|
Образует интервал из полей: years (годы), months (месяцы), weeks (недели), days (дни), hours (часы), minutes (минуты) и secs (секунды), каждое из которых по умолчанию считается равным нулю.
|
Образует время из полей: hour (час), minute (минута) и sec (секунда)
|
Образует момент времени из полей: year (год), month (месяц), day (день), hour (час), minute (минута) и sec (секунда) (отрицательное значение поля year означает год до н. э.)
|
Образует дату и время с часовым поясом из полей: year (год), month (месяц), day (день), hour (час), minute (минута) и sec (секунда) (отрицательное значение поля year означает год до н. э.). Если параметр
|
Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5
|
Текущая дата и время (на момент начала текущего оператора); см. Подраздел 9.9.5
|
Текущая дата и время (как
|
Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.5
|
Преобразует время эпохи 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
или 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
Оператор Описание Пример(ы) |
---|
Переводит значение даты/времени без часового пояса в дату/время с часовым поясом, в предположении, что входное значение задано в указанном поясе.
|
Переводит значение даты/времени без часового пояса в дату/время с часовым поясом, заданным значением
|
Переводит значение даты/времени с часовым поясом в дату/время без часового пояса, которое соответствует входному значению в указанном поясе.
|
Переводит значение даты/времени с часовым поясом в дату/время без часового пояса, поскольку время будет отображаться с часовым поясом, указанным в качестве значения
|
Переводит значение времени с часовым поясом в другой часовой пояс. Так как время задаётся без даты, в расчёте используется действующее в данный момент смещение указанного часового пояса от UTC.
|
Переводит значение времени с часовым поясом в другой часовой пояс. Так как время задаётся без даты, в расчёте используется действующее в данный момент смещение значения Предположим, что для
|
В этих выражениях желаемый часовой_пояс
можно задать либо в виде текстовой строки (например, 'America/Los_Angeles'
), либо как интервал (например, INTERVAL '-08:00'
). В первом случае название часового пояса можно указать любым из способов, описанных в Подразделе 8.5.3. Вариант с интервалом полезен, только если для часового пояса смещение от UTC всегда постоянно, что на практике встречается нечасто.
Синтаксис AT LOCAL
можно использовать в качестве сокращённой записи для AT TIME ZONE
, где локальный
локальный
— это значение TimeZone
сеанса.
Примеры (предполагается, что текущее значение параметра 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
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'
есть переход на летнее время.
Функция
равнозначна SQL-совместимой конструкции timezone
(часовой_пояс
, время
)
.время
AT TIME ZONE часовой_пояс
Функция
равнозначна SQL-совместимой конструкции timezone
(часовой_пояс
, время
)
.время
AT TIME ZONE часовой_пояс
Функция
равнозначна SQL-совместимой конструкции timezone
(timestamp
)
.timestamp
AT LOCAL
Функция
равнозначна SQL-совместимой конструкции timezone
(время
)
.время
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
или её вариации, убедитесь в том, что в текущем сеансе нет ненужных блокировок. В противном случае в состояние ожидания могут перейти и другие сеансы, так что это отразится на системе в целом.