8.5. Типы даты/времени

8.5.1. Ввод даты/времени

Значения даты и времени принимаются практически в любом разумном формате, включая ISO 8601, SQL-совместимый, традиционный формат POSTGRES и другие. В некоторых форматах порядок даты, месяца и года во вводимой дате неоднозначен и поэтому поддерживается явное определение формата. Для этого предназначен параметр DateStyle. Когда он имеет значение MDY, выбирается интерпретация месяц-день-год, значению DMY соответствует день-месяц-год, а YMD — год-месяц-день.

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

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

тип [ (p) ] 'значение'

Здесь p — необязательное указание точности, определяющее число знаков после точки в секундах. Точность может быть определена для типов time, timestamp и interval в пределах, описанных выше. Если в определении константы точность не указана, она считается равной точности значения в строке.

1999-01-08 04:05:06

1999-01-08 04:05:06 -8:00

January 8 04:05:06 1999 PST

TIMESTAMP '2004-10-19 10:23:54'

TIMESTAMP '2004-10-19 10:23:54+02'

TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

8.5.2. Вывод даты/времени

В качестве выходного формата типов даты/времени можно использовать один из четырёх стилей: ISO 8601, SQL (Ingres), традиционный формат POSTGRES (формат date в Unix) или German. По умолчанию выбран формат ISO. (Стандарт SQL требует, чтобы использовался именно ISO 8601. Другой формат называется "SQL" исключительно по историческим причинам.) Примеры всех стилей вывода перечислены в Таблице 8-14. Вообще со значениями типов date и time выводилась бы только часть даты или времени из показанных примеров, но со стилем POSTGRES значение даты без времени выводится в формате ISO.

В стилях SQL и POSTGRES день выводится перед месяцем, если установлен порядок DMY, а в противном случае месяц выводится перед днём. (Как этот параметр также влияет на интерпретацию входных значений, описано в Подразделе 8.5.1) Соответствующие примеры показаны в Таблице 8-15.

Стиль даты/времени пользователь может выбрать с помощью команды SET datestyle, параметра DateStyle в файле конфигурации postgresql.conf или переменной окружения PGDATESTYLE на сервере или клиенте.

Для большей гибкости при форматировании выводимой даты/времени можно использовать функцию to_char (см. Раздел 9.8).

8.5.3. Часовые пояса

Часовые пояса и правила их применения определяются, как вы знаете, не только по географическим, но и по политическим соображениям. Часовые пояса во всём мире были более-менее стандартизированы в начале прошлого века, но они продолжают претерпевать изменения, в частности это касается перехода на летнее время. Для расчёта времени в прошлом PostgreSQL получает исторические сведения о правилах часовых поясов из распространённой базы данных IANA (Olson). Для будущего времени предполагается, что в заданном часовом поясе будут продолжать действовать последние принятые правила.

PostgreSQL стремится к совместимости со стандартом SQL в наиболее типичных случаях. Однако стандарт SQL допускает некоторые странности при смешивании типов даты и времени. Две очевидные проблемы:

• Хотя для типа date часовой пояс указать нельзя, это можно сделать для типа time. В реальности это не очень полезно, так как без даты нельзя точно определить смещение при переходе на летнее время.

• По умолчанию часовой пояс задаётся постоянным смещением от UTC. Это также не позволяет учесть летнее время при арифметических операций с датами, пересекающими границы летнего времени.

Поэтому мы советуем использовать часовой пояс с типами, включающими и время, и дату. Мы не рекомендуем использовать тип 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.

• В дополнение к полным названиям и аббревиатурам часовых поясов PostgreSQL принимает указания часового пояса в стиле POSIX: STDсмещение или STDсмещениеDST, где STD — аббревиатура пояса, смещение — разница с UTC, а DST — необязательное дополнение, обозначающее летнее время и добавляющее к смещению ещё один час. Например, если бы обозначение EST5EDT не являлось названием часового пояса, оно всё равно было бы воспринято и функционально соответствовало бы поясу североамериканского восточного времени. При такой записи аббревиатура пояса может быть набором букв или обычной строкой, заключённой в угловые скобки ((<>). Когда представлено название пояса с летним временем, при пересчёте времени будут использоваться правила перехода на летнее время, описанные в базе данных IANA в файле posixrules. В стандартной инсталляции PostgreSQL файл posixrules соответствует описанию US/Eastern, так что для указаний часовых поясов в стиле POSIX применяются правила перехода на летнее время, принятые в США. При необходимости это можно изменить, заменив файл posixrules.

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

• Если установлена переменная окружения PGTZ, клиенты libpq используют её значение, выполняя при подключении к серверу команду SET TIME ZONE.

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.

В альтернативном формате:

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.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.