PostgreSQL - Дата и время

PostgreSQL поддерживает полный набор типов даты и времени SQL.Даты отсчитываются по григорианскому календарю, даже за годы до введения этого календаря.

time, timestamp, и intervalпринять необязательное значение точности p, указывающее количество дробных цифр, сохраняемых в поле секунд. По умолчанию нет явного ограничения точности. Допустимый диапазон pот 0 до 6.

У 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

Обратите внимание, что если указаны оба значения fieldsи , значение должно включать , поскольку точность применяется только к секундам.pfieldsSECOND

Тип time with time zoneопределен стандартом SQL, но определение демонстрирует свойства, которые приводят к сомнительной полезности. В большинстве случаев комбинация date, time, timestamp without time zoneи timestamp with time zoneдолжна обеспечивать полный набор функций даты/времени, необходимых любому приложению.

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

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

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

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

type [ (p) ] 'value'

где p— необязательная спецификация точности, указывающая количество дробных цифр в поле секунд. Точность может быть указана для типов time, , timestampи intervalи может варьироваться от 0 до 6. Если точность не указана в спецификации константы, по умолчанию используется точность буквального значения (но не более 6 цифр).

Даты

Ниже показаны некоторые возможные входные данные для данного dateтипа.

Время

Типы времени суток: и . один эквивалентен .time [ (p) ] without time zonetime [ (p) ] with time zonetimetime without time zone

Допустимый ввод для этих типов состоит из времени суток, за которым следует необязательный часовой пояс. Если часовой пояс указан во входных данных для time without time zone, он просто игнорируется. Вы также можете указать дату, но она будет проигнорирована, за исключением случаев, когда вы используете имя часового пояса, включающее правило перехода на летнее время, например America/New_York. В этом случае указание даты требуется для того, чтобы определить, применяется ли стандартное или летнее время. Соответствующее смещение часового пояса записывается в time with time zoneзначение.

Отметки времени

Допустимый ввод для типов отметок времени состоит из конкатенации даты и времени, за которыми следует необязательный часовой пояс, за которым следует необязательный ADили BC. (В качестве альтернативы AD/ BCможет стоять перед часовым поясом, но это не предпочтительный порядок.) Таким образом:

1999-01-08 04:05:06

и:

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

действительные значения, соответствующие стандарту ISO 8601. Кроме того, общий формат:

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 (универсальное скоординированное время, традиционно известное как среднее время по Гринвичу, GMT ). Входное значение с явно заданным часовым поясом преобразуется в формат UTC с использованием соответствующего смещения для этого часового пояса. Если во входной строке часовой пояс не указан, предполагается, что он находится в часовом поясе, указанном системным параметром TimeZone , и преобразуется в формат UTC с использованием смещения для timezoneзоны.

Когда timestamp with time zoneзначение выводится, оно всегда преобразуется из UTC в текущую timezoneзону и отображается как местное время в этой зоне. Чтобы увидеть время в другом часовом поясе, либо измените, timezoneлибо используйте AT TIME ZONEконструкцию.

Преобразования между timestamp without time zoneи timestamp with time zoneобычно предполагают, что timestamp without time zoneзначение должно быть взято или задано как timezoneместное время. Для преобразования можно указать другой часовой пояс с помощью AT TIME ZONE.

Специальные Значения

PostgreSQL для удобства поддерживает несколько специальных входных значений даты/времени. Значения infinityи -infinityспециально представлены внутри системы и будут отображаться без изменений; но другие являются просто условными обозначениями, которые при чтении будут преобразованы в обычные значения даты/времени. (В частности, nowсвязанные строки преобразуются в определенное значение времени, как только они считываются.) Все эти значения должны быть заключены в одинарные кавычки при использовании в качестве констант в командах SQL.

Следующие функции, совместимые с SQL , также можно использовать для получения текущего значения времени для соответствующего типа данных: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. Обратите внимание, что это функции SQL, и они не распознаются в строках ввода данных.

Хотя входные строки now, today, tomorrowи yesterdayпрекрасно подходят для использования в интерактивных командах SQL, они могут иметь неожиданное поведение, когда команда сохраняется для последующего выполнения, например, в подготовленных операторах, представлениях и определениях функций. Строка может быть преобразована в определенное значение времени, которое продолжает использоваться еще долгое время после того, как оно устареет. Вместо этого в таких контекстах используйте одну из функций SQL. Например, CURRENT_DATE + 1безопаснее, чем 'tomorrow'::date.

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

Формат вывода типов даты/времени может быть установлен в один из четырех стилей ISO 8601, SQL (Ingres), традиционный POSTGRES (формат даты Unix ) или немецкий. По умолчанию используется формат ISO . (Стандарт SQL требует использования формата ISO 8601. Название выходного формата « SQL » является исторической случайностью.) Вывод типов dateи timeобычно представляет собой только часть даты или времени в соответствии с приведенными примерами. Однако стиль POSTGRES выводит только значения даты в формате ISO .

В стилях SQL и POSTGRES день отображается перед месяцем, если указан порядок полей DMY, в противном случае месяц отображается перед днем. 

В стиле ISO часовой пояс всегда отображается как числовое смещение со знаком от UTC с положительным знаком, используемым для зон к востоку от Гринвича. Смещение будет отображаться как hh(только часы), если это целое число часов, иначе как hh: mmесли это целое число минут, иначе как hh: mm: ss. (Третий случай невозможен с любым современным стандартом часовых поясов, но он может появиться при работе с отметками времени, которые предшествуют принятию стандартизированных часовых поясов.) В других стилях даты часовой пояс отображается как буквенная аббревиатура, если он широко используется в текущей зоне. В противном случае он отображается как числовое смещение со знаком в базовом формате ISO 8601 ( hhили hhmm).

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

Функция форматирования to_charтакже доступна как более гибкий способ форматирования вывода даты/времени.

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

Часовые пояса и соглашения о часовых поясах зависят от политических решений, а не только от геометрии Земли. Часовые пояса по всему миру стали несколько стандартизированными в 1900-х годах, но по-прежнему подвержены произвольным изменениям, особенно в отношении правил перехода на летнее время. 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представлении. PostgreSQL использует для этой цели широко используемые данные о часовых поясах IANA, поэтому те же имена часовых поясов распознаются и другим программным обеспечением.
• Аббревиатура часового пояса, например PST. Такая спецификация просто определяет конкретное смещение от UTC, в отличие от полных имен часовых поясов, которые также могут подразумевать набор правил перехода на летнее время. Распознанные сокращения перечислены в pg_timezone_abbrevsпредставлении. Вы не можете установить параметры конфигурации TimeZone или log_timezone в аббревиатуру часового пояса, но вы можете использовать аббревиатуры во входных значениях даты/времени и с AT TIME ZONEоператором.
• В дополнение к именам и аббревиатурам часовых поясов PostgreSQL будет принимать спецификации часовых поясов в стиле POSIX. Этот вариант обычно не предпочтительнее использования именованного часового пояса, но он может быть необходим, если нет подходящей записи IANA о часовом поясе.

Короче говоря, это разница между аббревиатурами и полными именами: аббревиатуры представляют собой определенное смещение от UTC, тогда как многие из полных имен подразумевают местное правило перехода на летнее время и, таким образом, имеют два возможных смещения UTC. Например, 2014-06-04 12:00 America/New_Yorkпредставляет полдень по местному времени в Нью-Йорке, которое для этой конкретной даты было восточным летним временем (UTC-4). Так 2014-06-04 12:00 EDTуказывает тот самый момент времени. Но 2014-06-04 12:00 ESTуказывает полдень по стандартному восточному времени (UTC-5), независимо от того, действовало ли номинально летнее время в эту дату.

Чтобы усложнить ситуацию, некоторые юрисдикции использовали одну и ту же аббревиатуру часового пояса для обозначения разных смещений UTC в разное время; например, в Москве имелось в MSKвиду UTC+3 в некоторые годы и UTC+4 в другие. PostgreSQL интерпретирует такие аббревиатуры в соответствии с тем, что они значили (или совсем недавно значили) в указанную дату; но, как и в ESTприведенном выше примере, это не обязательно совпадает с местным гражданским временем в эту дату.

Интервальный ввод

intervalзначения могут быть записаны с использованием следующего подробного синтаксиса:

[ @ ] [ ... ] [ ]
quantity unitquantity unitdirection

где quantityчисло (возможно, подписанное); unitявляется microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, или аббревиатурами или множественными числами этих единиц; directionможет быть agoили пустым. Знак at ( @) является необязательным шумом. Суммы различных единиц неявно добавляются с учетом соответствующего знака. 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.)