Работа с функциями даты и времени в PostgreSQL
Работа с расчетами даты и времени является неотъемлемой частью любого решения Data Analytics. Будь то финансовые данные или любые данные, связанные с продажами, даты всегда играют ключевую роль в анализе тенденций и, следовательно, выводят из них закономерность. Некоторые из наиболее часто используемых KPI, которые включают расчет даты и времени, — это ежемесячные продажи , ежеквартальный веб-трафик, годовой рост и т. д. Эти KPI помогают компаниям понять и измерить свою эффективность, а также помогают принимать решения, необходимые для их развития. В этой статье мы собираемся изучить некоторые важные функции даты и времени, которые можно использовать в PostgreSQL, и способы их реализации в дальнейшем.
PostgreSQL — одна из ведущих систем реляционных баз данных с открытым исходным кодом, которую можно использовать для создания операционных баз данных, а также хранилищ данных. PostgreSQL по умолчанию предоставляет ряд различных функций даты и времени, которые можно использовать для расчета таких ключевых показателей эффективности. Кроме того, мы можем рассчитывать временные интервалы с помощью этих функций, которые помогут нам проанализировать, что изменилось между этими двумя таймфреймами. В этой статье мы сосредоточимся на следующих концепциях.
- Извлечение части даты из существующих меток времени
- Использование функции INTERVAL в PostgreSQL
- Использование функции CAST в PostgreSQL для преобразования меток времени в другие форматы
Извлечь части даты из существующих меток времени
Работая над большим проектом базы данных, мы часто сталкиваемся с несколькими столбцами, в которых данные хранятся в формате временной метки. Эти столбцы могут быть с информацией о часовом поясе или без нее. Иногда требуется, чтобы мы не использовали всю метку времени для анализа, а просто использовали ее часть, например, день или час. Давайте сначала разберемся, как выглядит столбец с отметкой времени в PostgreSQL.
Как вы можете видеть на рисунке выше, я использовал функцию now() в PostgreSQL, которая возвращает текущее время системы. Поскольку мой коллега находится в Дублине, и его часовой пояс — UTC + 1 (летнее время). Давайте сначала разберемся с анатомией метки времени и разберем метку времени, чтобы выделить каждую из частей в разные столбцы.
- 2021 г. — ГОД — Извлекает часть года из метки времени.
- 07 — МЕСЯЦ — Извлекает месяц из метки времени.
- 28 год — ДЕНЬ — Извлекает день из метки времени.
- 04 — ЧАС — Извлекает час из отметки времени.
- 39 — МИНУТА — Извлекает минуты из отметки времени.
- 39 — СЕКУНДЫ — Извлекает секунды из метки времени.
- 247007 — МИЛЛИСЕКУНД — Извлекает миллисекунды из отметки времени.
Как поясняется в приведенной выше таблице, метку времени можно разбить на эти части. Теперь, чтобы извлечь и распечатать каждую из этих частей по отдельности, мы можем использовать функцию EXTRACT в PostgreSQL. Синтаксис функции EXTRACT следующий.
EXTRACT(part FROM timestamp);
Давайте теперь попробуем извлечь значения даты из метки времени. Это можно сделать следующим образом.
Как вы можете видеть на рисунке выше, мы извлекли год, месяц и дату из часового пояса. Эти значения теперь доступны в виде отдельных столбцов. Эти столбцы можно использовать для выполнения различных расчетов. Затем мы также можем извлечь значения времени из метки времени. Это можно сделать, используя следующее.
На приведенном выше рисунке значения часа, минуты и секунды были извлечены из метки времени, и их можно использовать как части расчета, в которых используются дата и время. Помимо этого, функция EXTRACT также предоставляет некоторые другие методы для извлечения дополнительных частей даты из временных меток. Это следующие.
-
- WEEK (НЕДЕЛЯ) — Извлекает неделю из даты.
- DOW — Возвращает день недели из даты.
- DOY — Возвращает день года из даты.
- EPOCH (ЭПОХА) — Возвращает отметку времени UNIX. Вычислено 01.01.1970.
- QUARTER (ЧЕТВЕРТЬ) — Извлекает четверть из даты.
- TIMEZONE (ЧАСОВОЙ ПОЯС) — Извлекает информацию о часовом поясе.
- TIMEZONE_HOUR — Возвращает разницу часовых поясов в часах.
- TIMEZONE_MINUTE — Возвращает разницу часовых поясов в минутах.
Как вы можете видеть на рисунках выше, я использовал большинство часто используемых частей даты, которые используются с функцией EXTRACT в PostgreSQL.
Использование функции INTERVAL в PostgreSQL
При работе с вычислениями даты и времени в PostgreSQL иногда нам нужно вычислить продолжительность между двумя заданными временными метками. Это может быть что угодно, например, разница между днями в двух метках времени или разница между часами в двух метках времени. Такие расчеты важны в цепочках логистики или доставки продуктов питания, где клиентам может потребоваться рассчитать время, необходимое для доставки товаров или услуг. Давайте теперь посмотрим, как мы можем вычислить разницу между двумя отметками времени, а также добавить время к существующей отметке времени. Предположим, у нас есть две отметки времени, как показано ниже.
- Order_Date = 2021-02-28 20:15:21.298284+01
- Delivery_Date = 2021-03-10 10:39:11.943206+01
В первом сценарии мы хотели бы рассчитать время в днях, прошедшее с момента размещения заказа до его доставки. Это можно сделать в PostgreSQL с помощью функции AGE(). Эта функция принимает в качестве аргументов две метки времени и затем возвращает интервал между ними. Давайте теперь посмотрим, как это работает.
Видно, что разница между метками времени составляет около 9,5 дней. Точно так же мы можем использовать функцию INTERVAL, чтобы добавить время к существующей метке времени и вернуть новую метку времени. Это полезно, если мы хотим установить дату через фиксированный период времени. Например, при размещении заказа нам может потребоваться рассчитать дату доставки на основе интервала времени доставки. Это можно сделать следующим образом.
Как видно на рисунке выше, я рассчитал дату доставки, добавив 2 дня к дате заказа. Это простой способ добавить дни, часы или минуты и т. Д. К существующей метке времени.
Использование функции CAST в PostgreSQL
Функция CAST в PostgreSQL используется для преобразования значений из одного формата в другой. Обычно это делается для возврата фиксированного типа данных для поля. Здесь важно отметить, что для использования функции CAST возвращаемые значения должны быть совместимы с типом данных, указанным в функции CAST. Синтаксис функции CAST следующий.
CAST(expression as datatype)
CAST expression::datatypeМы можем использовать любое из выражений для использования функции CAST в PostgreSQL. Давайте теперь посмотрим, как это работает.
Как вы можете видеть на приведенном выше рисунке, типы данных, возвращаемые двумя столбцами, — это дата и временная метка соответственно. Однако выражение даты, которое мы предоставили функции CAST, остается прежним. Таким образом, вы можете использовать функцию CAST для преобразования одного типа данных в другой.
Заключение
В этой статье мы рассмотрели несколько важных функций даты и времени в PostgreSQL. При построении любой современной модели данных чрезвычайно важно реализовать правильное измерение даты, которое можно полностью использовать для анализа данных. Использование этих функций даты и времени значительно упростит процесс анализа данных, так как вы можете легко перейти к анализу даты и внести необходимые изменения по мере необходимости. С вами был Иван Сёмин автор и создатель IT портала Pyatilistnik.org.
Популярные Похожие записи:
Использование таблиц в качестве шаблонов в базе данных Azure для PostgreSQL- Как сделать сводную таблицу, за минуту
Get-ClusterLog и поиск ошибок в Failover Clusters- Просмотр и очистка DFS кэша
- Указана недопустимая метка диска, решаем за минуту
Как отключить телеметрию в Linux
EXTRACT — извлечение из даты части (год, месяц, день. )
Функция extract получает из значений даты/времени поля, такие как год или час. Здесь source — значение типа timestamp , time или interval . (Выражения типа date приводятся к типу timestamp , так что допускается и этот тип.) Указанное поле представляет собой идентификатор, по которому из источника выбирается заданное поле. Функция extract возвращает значения типа numeric . Допустимые поля:
century
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13')# date_part 1 20 SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');# date_part 1 21 Первый век начался 0001-01-01 00:00:00, хотя люди в то время и не считали так. Это определение распространяется на все страны с григорианским календарём. Века с номером 0 не было; считается, что 1 наступил после -1. Если такое положение вещей вас не устраивает, направляйте жалобы по адресу: Ватикан, Собор Святого Петра, Папе.
day
Для значений timestamp это день месяца (1–31); для значений interval — число дней
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');# date_part 1 16 SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');# date_part 1 40 decade
Год, делённый на 10
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');# date_part 1 200 dow
День недели, считая с воскресенья (0) до субботы (6)
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');# date_part 1 5 Заметьте, что в extract дни недели нумеруются не так, как в функции to_char(. ‘D’).
doy
День года (1–365/366)
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40')# date_part 1 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');# date_part 1 982384720.12 SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');# date_part 1 982355920.12 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');# date_part 1 442800 Преобразовать время эпохи назад, в значение timestamp with time zone, с помощью to_timestamp можно так:
SELECT to_timestamp(982384720.12);# to_timestamp 1 2001-02-17 10:38:40.12+06 Имейте в виду, что применяя to_timestamp к времени эпохи, извлечённому из значения date или timestamp, можно получить не вполне ожидаемый результат: эта функция подразумевает, что изначальное значение задано в часовом поясе UTC, но это может быть не так.
hour
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');# date_part 1 20 isodow
День недели, считая с понедельника (1) до воскресенья (7)
SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');# date_part 1 7 Результат отличается от dow только для воскресенья. Такая нумерация соответствует ISO 8601.
isoyear
Год по недельному календарю ISO 8601, в который попадает дата (неприменимо к интервалам)
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');# date_part 1 2005 SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');# date_part 1 2006 Год по недельному календарю ISO начинается с понедельника недели, в которой оказывается 4 января, так что в начале января или в конце декабря год по ISO может отличаться от года по григорианскому календарю. Подробнее об этом рассказывается в описании поля week.
Этого поля не было в PostgreSQL до версии 8.3.
julian
Юлианская дата, соответствующая дате или дате/времени (для интервала не определена). Значение будет дробным, если заданное время отличается от начала суток по местному времени. За дополнительной информацией обратитесь к Разделу B.7.
SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');# date_part 1 2453737 SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');# date_part 1 2453737.5 microseconds
Значение секунд с дробной частью, умноженное на 1 000 000; заметьте, что оно включает и целые секунды
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');# date_part 1 28500000 millennium
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');# date_part 1 3 Годы 20 века относятся ко второму тысячелетию. Третье тысячелетие началось 1 января 2001 г.
milliseconds
Значение секунд с дробной частью, умноженное на 1 000; заметьте, что оно включает и целые секунды.
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');# date_part 1 28500 minute
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');# date_part 1 38 month
Для значений timestamp это номер месяца в году (1–12), а для interval — остаток от деления числа месяцев на 12 (0–11)
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');# date_part 1 2 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');# date_part 1 3 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');# date_part 1 1 quarter
Квартал (1–4), к которому относится дата
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');# date_part 1 1 second
Секунды, включая дробную часть
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');# date_part 1 40 SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');# date_part 1 28.5 timezone
Смещение часового пояса от UTC, представленное в секундах. Положительные значения соответствуют часовым поясам к востоку от UTC, а отрицательные — к западу. (Строго говоря, в Postgres Pro используется не 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');# date_part 1 7 year
Поле года. Учтите, что года 0 не было, и это следует иметь в виду, вычитая из годов нашей эры годы до нашей эры.
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');# date_part 1 2001 Примечание
С аргументом +/-бесконечность extract возвращает +/-бесконечность для монотонно увеличивающихся полей (epoch, julian, year, isoyear, decade, century и millennium). Для других полей возвращается NULL. До версии 9.6 Postgres Pro возвращал ноль для всех случаев с бесконечными аргументами.
Функция 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');# date_part 1 16 SELECT date_part('hour', INTERVAL '4 hours 3 minutes');Postgresql как выделить год из даты
Все существующие функции для обработки даты/времени перечислены в Таблице 9.28, а подробнее они описаны в следующих подразделах. Поведение основных арифметических операторов ( + , * и т. д.) описано в Таблице 9.27. Функции форматирования этих типов данных были перечислены в Разделе 9.8. Общую информацию об этих типах вы получили (или можете получить) в Разделе 8.5.
Все описанные ниже функции и операторы принимают две разновидности типов time или timestamp : с часовым поясом ( time with time zone и timestamp with time zone ) и без него ( time without time zone и timestamp without time zone ). Для краткости здесь они рассматриваются вместе. Кроме того, операторы + и * обладают переместительным свойством (например, date + integer = integer + date); здесь будет приведён только один вариант для каждой пары.
Таблица 9.27. Операторы даты/времени
Оператор Пример Результат + date ‘2001-09-28’ + integer ‘7’ date ‘2001-10-05’ + date ‘2001-09-28’ + interval ‘1 hour’ timestamp ‘2001-09-28 01:00:00’ + date ‘2001-09-28′ + time ’03:00’ timestamp ‘2001-09-28 03:00:00’ + interval ‘1 day’ + interval ‘1 hour’ interval ‘1 day 01:00:00’ + timestamp ‘2001-09-28 01:00′ + interval ’23 hours’ timestamp ‘2001-09-29 00:00:00’ + time ’01:00′ + interval ‘3 hours’ time ’04:00:00′ — — interval ’23 hours’ interval ‘-23:00:00’ — date ‘2001-10-01’ — date ‘2001-09-28’ integer ‘3’ (дня) — date ‘2001-10-01’ — integer ‘7’ date ‘2001-09-24’ — date ‘2001-09-28’ — interval ‘1 hour’ timestamp ‘2001-09-27 23:00:00’ — time ’05:00′ — time ’03:00′ interval ’02:00:00′ — time ’05:00′ — interval ‘2 hours’ time ’03:00:00′ — timestamp ‘2001-09-28 23:00′ — interval ’23 hours’ timestamp ‘2001-09-28 00:00:00’ — interval ‘1 day’ — interval ‘1 hour’ interval ‘1 day -01:00:00’ — timestamp ‘2001-09-29 03:00’ — timestamp ‘2001-09-27 12:00’ interval ‘1 day 15:00:00’ * 900 * interval ‘1 second’ interval ’00:15:00′ * 21 * interval ‘1 day’ interval ’21 days’ * double precision ‘3.5’ * interval ‘1 hour’ interval ’03:30:00′ / interval ‘1 hour’ / double precision ‘1.5’ interval ’00:40:00′ Таблица 9.28. Функции даты/времени
Функция Тип результата Описание Пример Результат 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’) 43 years 8 mons 3 days (43 года 8 месяцев 3 дня) clock_timestamp() timestamp with time zone Текущая дата и время (меняется в процессе выполнения операторов); см. Подраздел 9.9.4 current_date date Текущая дата; см. Подраздел 9.9.4 current_time time with time zone Текущее время суток; см. Подраздел 9.9.4 current_timestamp timestamp with time zone Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.4 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_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 , interval ) interval Отсекает компоненты даты до заданной точности; см. также Подраздел 9.9.2 date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’) 2 days 03:00:00 extract ( field from timestamp ) double precision Возвращает поле даты; см. Подраздел 9.9.1 extract(hour from timestamp ‘2001-02-16 20:38:40’) 20 extract ( field from interval ) double precision Возвращает поле даты; см. Подраздел 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 ‘2001-02-16 21:28:30’) true isfinite( interval ) boolean Проверяет конечность интервала isfinite(interval ‘4 hours’) true justify_days( interval ) interval Преобразует интервал так, что каждый 30-дневный период считается одним месяцем justify_days(interval ’35 days’) 1 mon 5 days (1 месяц 5 дней) justify_hours( interval ) interval Преобразует интервал так, что каждый 24-часовой период считается одним днём justify_hours(interval ’27 hours’) 1 day 03:00:00 (1 день 03:00: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) localtime time Текущее время суток; см. Подраздел 9.9.4 localtimestamp timestamp Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.4 make_date( year int , month int , day int ) date Образует дату из полей: year (год), month (месяц) и day (день) make_date(2013, 7, 15) 2013-07-15 make_interval( years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) 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 (секунда) 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 (секунда). Если параметр timezone (часовой пояс) не указан, используется текущий часовой пояс. make_timestamptz(2013, 7, 15, 8, 15, 23.5) 2013-07-15 08:15:23.5+01 now() timestamp with time zone Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.4 statement_timestamp() timestamp with time zone Текущая дата и время (на момент начала текущего оператора); см. Подраздел 9.9.4 timeofday() text Текущая дата и время (как clock_timestamp , но в виде строки типа text ); см. Подраздел 9.9.4 transaction_timestamp() timestamp with time zone Текущая дата и время (на момент начала транзакции); см. Подраздел 9.9.4 В дополнение к этим функциям поддерживается 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'); Результат:
trueSELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Результат:falseSELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS (DATE '2001-10-30', DATE '2001-10-31'); Результат:falseSELECT (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’ не обязательно будут равны. Например, в часовом поясе CST7CDT результатом выражения timestamp with time zone ‘2005-04-02 12:00-07’ + interval ‘1 day’ будет timestamp with time zone ‘2005-04-03 12:00-06′ , тогда как, если добавить interval ’24 hours’ к тому же значению timestamp with time zone , в результате получится timestamp with time zone ‘2005-04-03 13:00-06’ . Эта разница объясняется тем, что 2005-04-03 02:00 в часовом поясе CST7CDT произошёл переход на летнее время.
Обратите внимание на возможную неоднозначность в поле 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’ ; между двумя заданными датами произошёл переход на летнее время:
SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') - EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'); Результат:
10537200SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') - EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00')) / 60 / 60 / 24; Результат:121.958333333333SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00'; Результат:121 days 23:00:00SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00'); Результат:4 mons9.9.1. EXTRACT , date_part
EXTRACT(
fieldFROMsource)Функция extract получает из значений даты/времени поля, такие как год или час. Здесь источник — значение типа timestamp , time или interval . (Выражения типа date приводятся к типу timestamp , так что допускается и этот тип.) Указанное поле представляет собой идентификатор, по которому из источника выбирается заданное поле. Функция extract возвращает значения типа double precision . Допустимые поля:
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13'); Результат:
20SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:21Первый век начался 0001-01-01 00:00:00, хотя люди в то время и не считали так. Это определение распространяется на все страны с григорианским календарём. Века с номером 0 нет было; считается, что 1 наступил после -1. Если такое положение вещей вас не устраивает, направляйте жалобы по адресу: Ватикан, Собор Святого Петра, Папе. day
Для значений timestamp это день месяца (1 — 31), для значений interval — число дней
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:
16SELECT 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.12SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); Результат:442800Преобразовать время эпохи назад, в значение дата/время можно так:
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';
(Это преобразование осуществляет функция to_timestamp .) hour
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'); Результат:
2005SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02'); Результат:2006Год по недельному календарю ISO начинается с понедельника недели, в которой оказывается 4 января, так что в начале января или в конце декабря год по ISO может отличаться от года по григорианскому календарю. Подробнее об этом рассказывается в описании поля week .
Этого поля не было в PostgreSQL до версии 8.3. microseconds
Значение секунд с дробной частью, умноженное на 1 000 000; заметьте, что оно включает и целые секунды
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5'); Результат:
28500000millennium
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:
3Годы 20 века относятся ко второму тысячелетию. Третье тысячелетие началось 1 января 2001 г. milliseconds
Значение секунд с дробной частью, умноженное на 1 000; заметьте, что оно включает и целые секунды.
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5'); Результат:
28500SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:
38Для значений timestamp это номер месяца в году (1 — 12), а для interval — остаток от деления числа месяцев на 12 (в интервале 0 — 11)
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:
2SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months'); Результат:3SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months'); Результат:1Квартал года (1 — 4), к которому относится дата
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:
1Секунды, включая дробную часть (0 — 59 [7] )
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40'); Результат:
40SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); Результат:28.5Смещение часового пояса от UTC, представленное в секундах. Положительные значения соответствуют часовым поясам к востоку от UTC, а отрицательные — к западу. (Выражаясь технически точно, Postgres Pro использует UT1 , так как секунды координации не учитываются.) 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Функция extract в основном предназначена для вычислительных целей. Функции форматирования даты/времени описаны в Разделе 9.8.
Функция date_part эмулирует традиционный для Ingres эквивалент стандартной SQL -функции extract :
date_part('поле',источник)Заметьте, что здесь параметр поле должен быть строковым значением, а не именем. Функция date_part воспринимает те же поля, что и extract .
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40'); Результат:16SELECT date_part('hour', INTERVAL '4 hours 3 minutes'); Результат:49.9.2. date_trunc
Функция date_trunc работает подобно trunc для чисел.
date_trunc('поле',значение)Здесь значение — это выражение типа timestamp или interval . (Значения типов date и time автоматически приводятся к типам timestamp и interval , соответственно.) Параметр поле определяет, до какой точности обрезать переданное значение. Возвращаемое значение будет иметь тип timestamp или interval и все его значения, менее значимые, чем заданное поле, будут равны нулю (или единице, если это номер дня или месяца).
Параметр поле может принимать следующие значения:
microseconds milliseconds second minute hour day week month quarter year decade century millennium SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40'); Результат:2001-02-16 20:00:00SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40'); Результат:2001-01-01 00:00:009.9.3. AT TIME ZONE
Указание AT TIME ZONE позволяет переводить дату/время без часового пояса в дату/время с часовым поясом и обратно, а также пересчитывать значения времени для различных часовых поясов. Все разновидности этого указания проиллюстрированы в Таблице 9.29.
Таблица 9.29. Разновидности 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 Переводит данное значение timestamp с часовым поясом в другой часовой пояс, но не сохраняет информацию о нём в результате time with time zone AT TIME ZONE часовой_пояс time with time zone Переводит данное время с часовым поясом в другой часовой пояс В этих выражениях желаемый часовой_пояс можно задать либо в виде текстовой строки (например, ‘America/Los_Angeles’ ), либо как интервал (например, INTERVAL ‘-08:00’ ). В первом случае название часового пояса можно указать любым из способов, описанных в Подразделе 8.5.3.
Примеры (в предположении, что местный часовой пояс America/Los_Angeles ):
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver'; Результат:
2001-02-16 19:38:40-08SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver'; Результат:2001-02-16 18:38:40SELECT TIMESTAMP '2001-02-16 20:38:40-05' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago'; Результат:2001-02-16 05:38:40В первом примере для значения, заданного без часового пояса, указывается часовой пояс и полученное время выводится в текущем часовом поясе (заданном параметром TimeZone ). Во втором примере значение времени смещается в заданный часовой пояс и выдаётся без указания часового пояса. Этот вариант позволяет хранить и выводить значения с часовым поясом, отличным от текущего. В третьем примере время в часовом поясе Токио пересчитывается для часового пояса Чикаго. При переводе значений времени без даты в другие часовые пояса используются определения часовых поясов, действующие в данный момент.
Функция timezone ( часовой_пояс , время ) равнозначна SQL-совместимой конструкции время AT TIME ZONE часовой_пояс .
9.9.4. Текущая дата/время
Postgres Pro предоставляет набор функций, результат которых зависит от текущей даты и времени. Все следующие функции соответствуют стандарту 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-05SELECT CURRENT_DATE; Результат:2001-12-23SELECT CURRENT_TIMESTAMP; Результат:2001-12-23 14:39:53.662522-05SELECT CURRENT_TIMESTAMP(2); Результат:2001-12-23 14:39:53.66-05SELECT LOCALTIMESTAMP; Результат:2001-12-23 14:39:53.662522Так как эти функции возвращают время начала текущей транзакции, во время транзакции эти значения не меняются. Это считается не ошибкой, а особенностью реализации: цель такого поведения в том, чтобы в одной транзакции « текущее » время было одинаковым и для разных изменений в одной транзакций записывалась одна отметка времени.
Примечание
В других СУБД эти значения могут изменяться чаще.
В Postgres Pro есть также функции, возвращающие время начала текущего оператора, а также текущее время в момент вызова функции. Таким образом, в Postgres Pro есть следующие функции, не описанные в стандарте SQL:
transaction_timestamp() statement_timestamp() clock_timestamp() timeofday() now()
Функция 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 , подразумевающее текущую дату и время (тоже на момент начала транзакции). Таким образом, результат следующих трёх операторов будет одинаковым:
SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP 'now'; -- не подходит для DEFAULT
Подсказка
Третья форма не подходит для указания в качестве значения DEFAULT при создании таблицы. Система преобразует now в значение timestamp в момент разбора константы, поэтому, когда будет вставляться значение по умолчанию, в соответствующем столбце окажется время создания таблицы! Первые две формы не будут вычисляться, пока не потребуется значение по умолчанию, так как это вызовы функции. Поэтому они дадут желаемый результат при добавлении строки в таблицу.
9.9.5. Задержка выполнения
В случае необходимости вы можете приостановить выполнение серверного процесса, используя следующие функции:
pg_sleep(
сек) pg_sleep_for(interval) pg_sleep_until(timestamp with time zone)Функция pg_sleep переводит процесс текущего сеанса в спящее состояние на указанное число секунд ( сек ). Параметр сек имеет тип double precision , так что в нём можно указать и дробное число. Функция 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 или её вариации, убедитесь в том, что в текущем сеансе нет ненужных блокировок. В противном случае в состояние ожидания могут перейти и другие сеансы, так что это отразится на системе в целом.
[7] 60, если операционная система поддерживает секунды координации
Пред. Наверх След. 9.8. Функции форматирования данных Начало 9.10. Функции для перечислений 8.5. Типы даты/времени
PostgreSQL поддерживает полный набор типов даты и времени SQL , показанный в Таблице 8-9. Операции, возможные с этими типами данных, описаны в Разделе 9.9. Все даты считаются по Григорианскому календарю, даже для времени до его введения (за дополнительными сведениями обратитесь к Разделу B.4).
Таблица 8-9. Типы даты/времени
Имя Размер Описание Наименьшее значение Наибольшее значение Точность timestamp [ (p) ] [ without time zone ] 8 байт дата и время (без часового пояса) 4713 до н. э. 294276 н. э. 1 микросекунда / 14 цифр timestamp [ (p) ] with time zone 8 байт дата и время (с часовым поясом) 4713 до н. э. 294276 н. э. 1 микросекунда / 14 цифр date 4 байта дата (без времени суток) 4713 до н. э. 5874897 н. э. 1 день time [ (p) ] [ without time zone ] 8 байт время суток (без даты) 00:00:00 24:00:00 1 микросекунда / 14 цифр time [ (p) ] with time zone 12 байт только время суток (с часовым поясом) 00:00:00+1459 24:00:00-1459 1 микросекунда / 14 цифр interval [ поля ] [ (p) ] 16 байт временной интервал -178000000 лет 178000000 лет 1 микросекунда / 14 цифр Замечание: Стандарт SQL требует, чтобы тип timestamp подразумевал timestamp without time zone (время без часового пояса), и PostgreSQL следует этому. Для краткости timestamp with time zone можно записать как timestamptz; это расширение PostgreSQL .
Типы time, timestamp и interval принимают необязательное значение точности p, определяющее, сколько знаков после запятой должно сохраняться в секундах. По умолчанию точность не ограничивается. Для типов timestamp и interval p может принимать значения от 0 до 6.
Замечание: Когда значения timestamp хранятся в восьмибайтных целых (сейчас по умолчанию это так), на всём интервале значений обеспечивается точность в микросекундах. Если же значения этого типа сохраняются в числах двойной точности с плавающей точкой (устаревший вариант компиляции), фактический предел точности может быть меньше 6. Значения timestamp сохраняются в секундах до или после полуночи 1 января 2000 г. Когда при этом используются числа с плавающей точкой, микросекундная точность достигается для дат в пределах нескольких лет от этой даты, а при удалении от неё точность теряется. Однако заметьте, что даты в числах с плавающей точкой позволяют представить больший диапазон timestamp, чем было показано выше: от 4713 до н. э. до 5874897 н. э.
В зависимости от того же варианта компиляции, типы time и interval могут сохраняться в виде чисел с плавающей точкой или в восьмибайтных целых. В случае с плавающей точкой при больших значениях interval точность уменьшается.
Для типа time p может принимать значения от 0 до 6 при хранении типа в восьмибайтном целом и от 0 до 10 при хранении в числе с плавающей точкой.
Тип interval дополнительно позволяет ограничить набор сохраняемых полей следующими фразами:
YEAR MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE HOUR TO SECOND MINUTE TO SECOND
Заметьте, что если указаны и поля, и точность p, указание полей должно включать SECOND, так как точность применима только к секундам.
Тип time with time zone определён стандартом SQL, но в его определении описаны свойства сомнительной ценности. В большинстве случае сочетание типов date, time, timestamp without time zone и timestamp with time zone удовлетворяет все потребности в функционале дат/времени, возникающие в приложениях.
Типы abstime и reltime имеют меньшую точность и предназначены для внутреннего использования. Эти типы не рекомендуется использоваться в обычных приложениях; их может не быть в будущих версиях.
8.5.1. Ввод даты/времени
Значения даты и времени принимаются практически в любом разумном формате, включая ISO 8601, SQL -совместимый, традиционный формат POSTGRES и другие. В некоторых форматах порядок даты, месяца и года во вводимой дате неоднозначен и поэтому поддерживается явное определение формата. Для этого предназначен параметр DateStyle. Когда он имеет значение MDY, выбирается интерпретация месяц-день-год, значению DMY соответствует день-месяц-год, а YMD — год-месяц-день.
PostgreSQL обрабатывает вводимые значения даты/времени более гибко, чем того требует стандарт SQL . Точные правила разбора даты/времени и распознаваемые текстовые поля, в том числе названия месяцев, дней недели и часовых поясов описаны в Приложении B.
Помните, что любые вводимые значения даты и времени нужно заключать в апострофы, как текстовые строки. За дополнительной информацией обратитесь к Подразделу 4.1.2.7. SQL предусматривает следующий синтаксис:
тип [ (p) ] 'значение'
Здесь p — необязательное указание точности, определяющее число знаков после точки в секундах. Точность может быть определена для типов time, timestamp и interval в пределах, описанных выше. Если в определении константы точность не указана, она считается равной точности значения в строке.
8.5.1.1. Даты
В Таблице 8-10 приведены некоторые допустимые значения типа date.
Таблица 8-10. Вводимые даты
Пример Описание 1999-01-08 ISO 8601; 8 января в любом режиме (рекомендуемый формат) January 8, 1999 воспринимается однозначно в любом режиме datestyle 1/8/1999 8 января в режиме MDY и 1 августа в режиме DMY 1/18/1999 18 января в режиме MDY; недопустимая дата в других режимах 01/02/03 2 января 2003 г. в режиме MDY; 1 февраля 2003 г. в режиме DMY и 3 февраля 2001 г. в режиме YMD 1999-Jan-08 8 января в любом режиме Jan-08-1999 8 января в любом режиме 08-Jan-1999 8 января в любом режиме 99-Jan-08 8 января в режиме YMD; ошибка в других режимах 08-Jan-99 8 января; ошибка в режиме YMD Jan-08-99 8 января; ошибка в режиме YMD 19990108 ISO 8601; 8 января 1999 в любом режиме 990108 ISO 8601; 8 января 1999 в любом режиме 1999.008 год и день года J2451187 дата по юлианскому календарю January 8, 99 BC 99 до н. э. 8.5.1.2. Время
Для хранения времени суток без даты предназначены типы time [ (p) ] without time zone и time [ (p) ] with time zone. Тип time без уточнения эквивалентен типу time without time zone.
Допустимые вводимые значения этих типов состоят из записи времени суток и необязательного указания часового пояса. (См. Таблицу 8-11 и Таблицу 8-12.) Если в значении для типа time without time zone указывается часовой пояс, он просто игнорируется. Так же будет игнорироваться дата, если её указать, за исключением случаев, когда в указанном часовом поясе принят переход на летнее время, например America/New_York. В данном случае указать дату необходимо, чтобы система могла определить, применяется ли обычное или летнее время. Соответствующее смещение часового пояса записывается в значении time with time zone.
Таблица 8-11. Вводимое время
Пример Описание 04:05:06.789 ISO 8601 04:05:06 ISO 8601 04:05 ISO 8601 040506 ISO 8601 04:05 AM то же, что и 04:05; AM не меняет значение времени 04:05 PM то же, что и 16:05; часы должны быть 04:05:06.789-8 ISO 8601 04:05:06-08:00 ISO 8601 04:05-08:00 ISO 8601 040506-08 ISO 8601 04:05:06 PST часовой пояс задаётся аббревиатурой 2003-04-12 04:05:06 America/New_York часовой пояс задаётся полным названием Таблица 8-12. Вводимый часовой пояс
Пример Описание PST аббревиатура (Pacific Standard Time, Стандартное тихоокеанское время) America/New_York полное название часового пояса PST8PDT указание часового пояса в стиле POSIX -8:00 смещение часового пояса PST по ISO-8601 -800 смещение часового пояса PST по ISO-8601 -8 смещение часового пояса PST по ISO-8601 zulu принятое у военных сокращение UTC z краткая форма zulu Подробнее узнать о том, как указывается часовой пояс, можно в Подразделе 8.5.3.
8.5.1.3. Даты и время
Допустимые значения типов timestamp состоят из записи даты и времени, после которого может указываться часовой пояс и необязательное уточнение AD или BC, определяющее эпоху до нашей эры и нашу эру соответственно. (AD/BC можно указать и перед часовым поясом, но предпочтительнее первый вариант.) Таким образом:
1999-01-08 04:05:06
1999-01-08 04:05:06 -8:00
допустимые варианты, соответствующие стандарту ISO 8601. В дополнение к этому поддерживается распространённый формат:
January 8 04:05:06 1999 PST
Стандарт SQL различает константы типов timestamp without time zone и timestamp with time zone по знаку «+» или «-» и смещению часового пояса, добавленному после времени. Следовательно, согласно стандарту, записи
TIMESTAMP '2004-10-19 10:23:54'
должен соответствовать тип timestamp without time zone, а
TIMESTAMP '2004-10-19 10:23:54+02'
тип timestamp with time zone. PostgreSQL никогда не анализирует содержимое текстовой строки, чтобы определить тип значения, и поэтому обе записи будут обработаны как значения типа timestamp without time zone. Чтобы текстовая константа обрабатывалась как timestamp with time zone, укажите этот тип явно:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
В константе типа timestamp without time zone PostgreSQL просто игнорирует часовой пояс. То есть результирующее значение вычисляется только из полей даты/времени и не подстраивается под указанный часовой пояс.
Значения timestamp with time zone внутри всегда хранятся в UTC (Universal Coordinated Time, Всемирное скоординированное время или время по Гринвичу, GMT ). Вводимое значение, в котором явно указан часовой пояс, переводится в UTC с учётом смещения данного часового пояса. Если во входной строке не указан часовой пояс, подразумевается часовой пояс, заданный системным параметром TimeZone и время так же пересчитывается в UTC со смещением timezone.
Когда значение timestamp with time zone выводится, оно всегда преобразуется из UTC в текущий часовой пояс timezone и отображается как локальное время. Чтобы получить время для другого часового пояса, нужно либо изменить timezone, либо воспользоваться конструкцией AT TIME ZONE (см. Подраздел 9.9.3).
В преобразованиях между timestamp without time zone и timestamp with time zone обычно предполагается, что значение timestamp without time zone содержит местное время (для часового пояса timezone). Другой часовой пояс для преобразования можно задать с помощью AT TIME ZONE.
8.5.1.4. Специальные значения
PostgreSQL для удобства поддерживает несколько специальных значений даты/времени, перечисленных в Таблице 8-13. Значения infinity и -infinity имеют особое представление в системе и они отображаются в том же виде, тогда как другие варианты при чтении преобразуются в значения даты/времени. (В частности, now и подобные строки преобразуются в актуальные значения времени в момент чтения.) Чтобы использовать эти значения в качестве констант в командах SQL, их нужно заключать в апострофы.
Таблица 8-13. Специальные значения даты/времени
Вводимая строка Допустимые типы Описание epoch date, timestamp 1970-01-01 00:00:00+00 (точка отсчёта времени в Unix) infinity date, timestamp время после максимальной допустимой даты -infinity date, timestamp время до минимальной допустимой даты now date, time, timestamp время начала текущей транзакции today date, timestamp время начала текущих суток tomorrow date, timestamp время начала следующих суток yesterday date, timestamp время начала предыдущих суток allballs time 00:00:00.00 UTC Для получения текущей даты/времени соответствующего типа можно также использовать следующие SQL -совместимые функции: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME и LOCALTIMESTAMP. Последние четыре функции принимают необязательное указание точности. (См. Подраздел 9.9.4.) Заметьте, что во входных строках эти SQL-функции не распознаются.
8.5.2. Вывод даты/времени
В качестве выходного формата типов даты/времени можно использовать один из четырёх стилей: ISO 8601, SQL (Ingres), традиционный формат POSTGRES (формат date в Unix) или German. По умолчанию выбран формат ISO . (Стандарт SQL требует, чтобы использовался именно ISO 8601. Другой формат называется «SQL» исключительно по историческим причинам.) Примеры всех стилей вывода перечислены в Таблице 8-14. Вообще со значениями типов date и time выводилась бы только часть даты или времени из показанных примеров, но со стилем POSTGRES значение даты без времени выводится в формате ISO .
Таблица 8-14. Стили вывода даты/время
Стиль Описание Пример ISO ISO 8601, стандарт SQL 1997-12-17 07:37:16-08 SQL традиционный стиль 12/17/1997 07:37:16.00 PST Postgres изначальный стиль Wed Dec 17 07:37:16 1997 PST German региональный стиль 17.12.1997 07:37:16.00 PST Замечание: ISO 8601 указывает, что дата должна отделяться от времени буквой T в верхнем регистре. PostgreSQL принимает этот формат при вводе, но при выводе вставляет вместо T пробел, как показано выше. Это сделано для улучшения читаемости и для совместимости с RFC 3339 и другими СУБД.
В стилях SQL и POSTGRES день выводится перед месяцем, если установлен порядок DMY, а в противном случае месяц выводится перед днём. (Как этот параметр также влияет на интерпретацию входных значений, описано в Подразделе 8.5.1) Соответствующие примеры показаны в Таблице 8-15.
Таблица 8-15. Соглашения о порядке компонентов даты
Параметр datestyle Порядок при вводе Пример вывода SQL, DMY день/месяц/год 17/12/1997 15:37:16.00 CET SQL, MDY месяц/день/год 12/17/1997 07:37:16.00 PST Postgres, DMY день/месяц/год Wed 17 Dec 07:37:16 1997 PST Стиль даты/времени пользователь может выбрать с помощью команды SET datestyle, параметра DateStyle в файле конфигурации postgresql.conf или переменной окружения PGDATESTYLE на сервере или клиенте.
Для большей гибкости при форматировании выводимой даты/времени можно использовать функцию to_char (см. Раздел 9.8).
8.5.3. Часовые пояса
Часовые пояса и правила их применения определяются, как вы знаете, не только по географическим, но и по политическим соображениям. Часовые пояса во всём мире были более-менее стандартизированы в начале прошлого века, но они продолжают претерпевать изменения, в частности это касается перехода на летнее время. Для расчёта времени в прошлом PostgreSQL получает исторические сведения о правилах часовых поясов из распространённой базы данных IANA (Olson). Для будущего времени предполагается, что в заданном часовом поясе будут продолжать действовать последние принятые правила.
PostgreSQL стремится к совместимости со стандартом SQL в наиболее типичных случаях. Однако стандарт SQL допускает некоторые странности при смешивании типов даты и времени. Две очевидные проблемы:
Хотя для типа date часовой пояс указать нельзя, это можно сделать для типа time. В реальности это не очень полезно, так как без даты нельзя точно определить смещение при переходе на летнее время.
Поэтому мы советуем использовать часовой пояс с типами, включающими и время, и дату. Мы не рекомендуем использовать тип time with time zone (хотя PostgreSQL поддерживает его для старых приложений и совместимости со стандартом SQL ). Для типов, включающих только дату или только время, в PostgreSQL предполагается местный часовой пояс.
Все значения даты и времени с часовым поясом представляются внутри в UTC , а при передаче клиентскому приложению они переводятся в местное время, при этом часовой пояс по умолчанию определяется параметром конфигурации TimeZone.
PostgreSQL позволяет задать часовой пояс тремя способами:
Полное название часового пояса, например America/New_York. Все допустимые названия перечислены в представлении pg_timezone_names (см. Раздел 48.72). Определения часовых поясов PostgreSQL берёт из широко распространённой базы IANA, так что имена часовых поясов PostgreSQL будут воспринимать и многие другие приложения.
Аббревиатура часового пояса, например PST. Такое определение просто задаёт смещение от UTC, в отличие от полных названий поясов, которые кроме того подразумевают и правила перехода на летнее время. Распознаваемые аббревиатуры перечислены в представлении pg_timezone_abbrevs (см. Раздел 48.71). Аббревиатуры можно использовать во вводимых значениях даты/времени и в операторе AT TIME ZONE, но не в параметрах конфигурации TimeZone и log_timezone.
Вкратце, различие между аббревиатурами и полными названиями заключаются в следующем: аббревиатуры представляют определённый сдвиг от UTC, а полное название подразумевает ещё и местное правило по переходу на летнее время, то есть, возможно, два сдвига от UTC. Например, 2014-06-04 12:00 America/New_York представляет полдень по местному времени в Нью-Йорк, что для данного дня было бы летним восточным временем (EDT или UTC-4). Так что 2014-06-04 12:00 EDT обозначает тот же момент времени. Но 2014-06-04 12:00 EST задаёт стандартное восточное время (UTC-5), не зависящее о того, действовало ли летнее время в этот день.
Мало того, в некоторых юрисдикциях одна и та же аббревиатура часового пояса означала разные сдвиги UTC в разное время; например, аббревиатура московского времени MSK несколько лет означала UTC+3, а затем стала означать UTC+4. PostgreSQL обрабатывает такие аббревиатуры в соответствии с их значениями на заданную дату, но, как и с примером выше EST, это не обязательно будет соответствовать местному гражданскому времени в этот день.
При этом следует использовать возможность указания часового пояса в стиле POSIX с осторожностью, так как при этом могут быть приняты заведомо неверные данные, потому что разумность аббревиатуры никак не проверяется. Например, команда SET TIMEZONE TO FOOBAR0 будет работать и система примет эту довольно оригинальную аббревиатуру для UTC. Также следует учитывать, что в названиях часовых поясов POSIX положительные смещения соответствуют сдвигу к западу Гринвича. Во всех остальных формах PostgreSQL следует соглашению ISO-8601, по которому положительным смещениям соответствует сдвиг к востоку от Гринвича.
Независимо от формы, регистр в названиях и аббревиатурах часовых поясов не важен. (В PostgreSQL до версии 8.2 он где-то имел значение, а где-то нет.)
Ни названия, ни аббревиатуры часовых поясов, не зашиты в самом сервере; они считываются из файлов конфигурации, находящихся в путях . /share/timezone/ и . /share/timezonesets/ относительно каталога установки (см. Раздел B.3).
Параметр конфигурации TimeZone можно установить в postgresql.conf или любым другим стандартным способом, описанным в Главе 18. Часовой пояс может быть также определён следующими специальными способами:
Часовой пояс для текущего сеанса можно установить с помощью SQL -команды SET TIME ZONE. Это альтернативная запись команды SET TIMEZONE TO, более соответствующая SQL-стандарту.
8.5.4. Ввод интервалов
Значения типа interval могут быть записаны в следующей расширенной форме:
[@] количество единица [количество единица. ] [направление]
где количество — это число (возможно, со знаком); единица — одно из значений: microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium (которые обозначают соответственно микросекунды, миллисекунды, секунды, минуты, часы, дни, недели, месяцы, годы, десятилетия, века и тысячелетия), либо эти же слова во множественном числе, либо их сокращения; направление может принимать значение ago (назад) или быть пустым. Знак @ является необязательным. Все заданные величины различных единиц суммируются вместе с учётом знака чисел. Указание ago меняет знак всех полей на противоположный. Этот синтаксис также используется при выводе интервала, если параметр IntervalStyle имеет значение postgres_verbose.
Количества дней, часов, минут и секунд можно определить, не указывая явно соответствующие единицы. Например, запись '1 12:59:10' равнозначна '1 day 12 hours 59 min 10 sec'. Сочетание года и месяца также можно записать через минус; например '200-10' означает то, же что и '200 years 10 months'. (На самом деле только эти краткие формы разрешены стандартом SQL и они используются при выводе, когда IntervalStyle имеет значение sql_standard.)
Интервалы можно также записывать в виде, определённом в ISO 8601, либо в «формате с кодами» , описанном в разделе 4.4.3.2 этого стандарта, либо в «альтернативном формате» , описанном в разделе 4.4.3.3. Формат с кодами выглядит так:
P количество единица [ количество единица . ] [ T [ количество единица . ]]
Строка должна начинаться с символа P и может включать также T перед временем суток. Допустимые коды единиц перечислены в Таблице 8-16. Коды единиц можно опустить или указать в любом порядке, но компоненты времени суток должны идти после символа T. В частности, значение кода M зависит от того, располагается ли он до или после T.
Таблица 8-16. Коды единиц временных интервалов ISO 8601
Код Значение Y годы M месяцы (в дате) W недели D дни H часы M минуты (во времени) S секунды В альтернативном формате:
P [ год-месяц-день ] [ T часы:минуты:секунды ]
строка должна начинаться с P, а T разделяет компоненты даты и времени. Значения выражаются числами так же, как и в датах ISO 8601.
При записи интервальной константы с указанием полей или присвоении колонке типа interval строки с полями, интерпретация непомеченных величин зависит от полей. Например, INTERVAL '1' YEAR воспринимается как 1 год, а INTERVAL '1' — как 1 секунда. Кроме того, значения «справа» от меньшего значащего поля, заданного в определении полей, просто отбрасываются. Например, в записи INTERVAL '1 day 2:03:04' HOUR TO MINUTE будут отброшены секунды, но не день.
Согласно стандарту SQL , все компоненты значения interval должны быть одного знака, и ведущий минус применяется ко всем компонентам; например, минус в записи '-1 2:03:04' применяется и к дню, и к часам/минутам/секундам. PostgreSQL позволяет задавать для разных компонентов разные знаки и традиционно обрабатывает знак каждого компонента в текстовом представлении отдельно от других, так что в данном случае часы/минуты/секунды будут считаться положительными. Если параметр IntervalStyle имеет значение sql_standard, ведущий знак применяется ко всем компонентам (но только если они не содержат знаки явно). В противном случае действуют традиционные правила PostgreSQL . Во избежание неоднозначности рекомендуется добавлять знак к каждому компоненту с отрицательным значением.
Тип interval представлен внутри в виде отдельных значений месяцев, дней и секунд. Это объясняется тем, что число дней в месяце может быть разным, а в сутках может быть и 23, и 25 часов в дни перехода на летнее/зимнее время. Значения месяцев и дней представлены целыми числами, а число секунд может быть дробным. Так как интервалы обычно получаются из строковых констант или при вычитании типов timestamp, этот способ хранения эффективен в большинстве случаев. Для корректировки числа дней и часов, когда они выходят за обычные границы, в PostgreSQL есть специальные функции justify_days и justify_hours .
В расширенном формате ввода и в некоторых полях более компактных форматов значения компонентов могут иметь дробные части, например '1.5 week' или '01:02:03.45'. Такое значение при сохранении пересчитывается в соответствующее число месяцев, дней и секунд. Когда при этом остаётся дробная часть в месяцах или в днях, она переносится в младший компонент с допущением, что 1 месяц = 30 дней, а 1 день = 24 часа. Например, значение '1.5 month' будет преобразовано в 1 месяц и 15 дней. В виде дробного числа хранятся и выводятся только секунды.
В Таблице 8-17 показано несколько примеров допустимых вводимых значений типа interval.
Таблица 8-17. Ввод интервалов
Пример Описание 1-2 Стандартный формат SQL: 1 год и 2 месяца 3 4:05:06 Стандартный формат SQL: 3 дня 4 часа 5 минут 6 секунд 1 year 2 months 3 days 4 hours 5 minutes 6 seconds Традиционный формат Postgres: 1 год 2 месяца 3 дня 4 часа 5 минут 6 секунд P1Y2M3DT4H5M6S «Формат с кодами» ISO 8601: то же значение, что и выше P0001-02-03T04:05:06 «Альтернативный формат» ISO 8601: то же значение, что и выше 8.5.5. Вывод интервалов
Формат вывода типа interval может определяться одним из четырёх стилей: sql_standard, postgres, postgres_verbose и iso_8601. Выбрать нужный стиль позволяет команда SET intervalstyle (по умолчанию выбран postgres). Примеры форматов разных стилей показаны в Таблице 8-18.
Стиль sql_standard выдаёт результат, соответствующий стандарту SQL, если значение интервала удовлетворяет ограничениям стандарта (и содержит либо только год и месяц, либо только день и время, и при этом все его компоненты одного знака). В противном случае выводится год-месяц, за которым идёт дата-время, а в компоненты для однозначности явно добавляются знаки.
Вывод в стиле postgres соответствует формату, который был принят в PostgreSQL до версии 8.4, когда параметр DateStyle имел значение ISO.
Вывод в стиле postgres_verbose соответствует формату, который был принят в PostgreSQL до версии 8.4, когда значением параметром DateStyle было не ISO.
Вывод в стиле iso_8601 соответствует «формату с кодами» описанному в разделе 4.4.3.2 формата ISO 8601.
Таблица 8-18. Примеры стилей вывода интервалов
Стиль Интервал год-месяц Интервал день-время Смешанный интервал sql_standard 1-2 3 4:05:06 -1-2 +3 -4:05:06 postgres 1 year 2 mons 3 days 04:05:06 -1 year -2 mons +3 days -04:05:06 postgres_verbose @ 1 year 2 mons @ 3 days 4 hours 5 mins 6 secs @ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago iso_8601 P1Y2M P3DT4H5M6S P-1Y-2M3DT-4H-5M-6S Пред. Начало След. Двоичные типы данных Уровень выше Логический тип