| Документация по PostgreSQL 8.2devel | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 8. Типы данных | Fast Forward | Next |
PostgreSQL поддерживает полный список SQL типов даты и времени, который представлен в Table 8-9. Операции, которые доступны для этих типов данных, описываются в Section 9.9.
Table 8-9. Типы даты/времени
| Имя | Размер хранения | Описание | Наименьшее значение | Наибольшее значение | Разрешение(шаг) |
|---|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8 байт | и дата и время | 4713 BC | 5874897 AD | 1 микросекунда / 14 разрядов |
| timestamp [ (p) ] with time zone | 8 байт | и дата и время без временной зоны | 4713 BC | 5874897 AD | 1 микросекунда / 14 разрядов |
| interval [ (p) ] | 12 байт | время в интервалах | -178000000 лет | 178000000 лет | 1 микросекунда / 14 разрядов |
| date | 4 байта | только даты | 4713 BC | 5874897 AD | 1 день |
| time [ (p) ] [ without time zone ] | 8 байт | только время | 00:00:00 | 24:00:00 | 1 микросекунда / 14 разрядов |
| time [ (p) ] with time zone | 12 байт | только время без временной зоны | 00:00:00+1359 | 24:00:00-1359 | 1 микросекунда / 14 разрядов |
Note: До PostgreSQL 7.3, указание timestamp был эквивалентно типу дата и время без временной зоны. Для соответствия стандарту SQL это был изменено.
Для типов time, timestamp и interval можно указывать необязательное значение точности p, которое задаёт количество дробных разрядов после запятой. По умолчанию, точность в явном виде не указывается. Допустимый диапазон значения p от 0 до 6 для типа timestamp и для типа interval.
Note: Если значения timestamp сохраняются как число с плавающей запятой двойной точности (по умолчанию это так), то эффективное ограничение на точность может быть меньше 6. Значения timestamp хранятся как количество секунд перед или после 2000-01-01. Микросекундная точность достигается для дат, которые находятся внутри отрезка, начинающегося с 2000-01-01, но эта точность снижается для дат вне этого отрезка. Когда значения timestamp сохраняются как восьмибайтные целые числа (опция задаваемая при компиляции), то микросекундная точность становится доступной для всего диапазона значений дат. Однако, восьмибайтные целые значения timestamp имеют более ограниченный диапазон дат, как говорилось выше: от 4713 BC до 294276 AD. Та же опция во время компиляции также определяет будут ли значения time т interval храниться как числа с плавающей точкой или как восьмибайтные целые числа. В случае чисел с плавающей точкой, бОльшие значения interval имеют меньшую точность из-за увеличения размера самого интервала.
Для типов time разрешается диапазон p от 0 до 6, когда для хранения значений используется восьмибайтное целое, или от 0 до 10, когда используется число с плавающей запятой.
Тип time with time zone определяется стандартом SQL, но это определение говорит о свойствах, которые приводят нас к сомнениям относительно пригодности. В большинстве случаев, комбинация типов date, time, timestamp without time zone и timestamp with time zone предоставляет полный диапазон функциональности для работы с датой и временем, который бы мог потребоваться какому-либо приложению.
Типы abstime и reltime имеют низкую точность и используются для внутренних целей. Вы должны воздержаться от использования этих типов в новый приложениях, а также перейти на правильные типы в старых приложениях. В будущих версиях некоторые или даже все эти внутренние типы могут исчезнуть.
Вводимые значения даты и времени примаются в любом разумном формате, включая ISO 8601, SQL-совместимый, традиционный формат POSTGRES и другие. Для некоторых форматов, порядок следования полей месяца, дня и года в значении даты является неоднозначным и для таких форматов поддерживается указание порядка следования этих полей. Установите параметр DateStyle в значение MDY, чтобы выбрать порядок следования месяц-день-год, в значение DMY, чтобы выбрать порядок следования день-месяц-год или в YMD, чтобы выбрать порядок следования год-месяц-день.
В PostgreSQL управление вводом значений даты и времени устроено более удобно, чем этого требует стандарт SQL. Смотрите Appendix B, чтобы узнать о точных правилах разбора вводимых значений даты/времени и о распознавании текстовых полей, включая месяцы, дни недели и временные зоны.
Помните, что любые вводимые значения даты или времени необходимо заключать в одиночные кавычки, как и текстовые строки. Больше информации можно найти в Section 4.1.2.5. SQL требует следующего синтаксиса
тип [ (p) ] 'значение'
где p в необязательной спецификации точности является целым числом, соответствующим количеству дробрых разрядов во втором поле. Точность может быть задана для типов time, timestamp и interval. Допустимые значения были рассмотрены выше. Если точность в спецификации константы не указана, то она устанавливается в значение по умолчанию для литеральных значений.
Table 8-10 показывает некоторые возможные формы ввода для типа date.
Table 8-10. Ввод значений даты
| Пример | Описание |
|---|---|
| January 8, 1999 | значение является однозначным для любого datestyle режима ввода |
| 1999-01-08 | ISO 8601; 8 января в любом режиме (рекомендуемый формат) |
| 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 год до Нашей Эры |
Типами времени являются time [ (p) ] without time zone и time [ (p) ] with time zone. Тип time эквивалентен типу time without time zone.
Правильные вводимые значения для этих типов состоят из времени дня, за которым следует необязательное значение временной зоны. (См. Table 8-11 и Table 8-12.) Если временная зона задаётся при вводе значений типа time without time zone, то она молча игнорируется. Вы также всегда можете задать дату, но она будет проигнорирована, за исключением случая, когда вы используете полное имя временной зоны, как например America/New_York. В этом случае дата задаётся принудительно, чтобы понять какое смещение временной зоны нужно применять. Оно будет применено, если при вводе было указано любое допустимое смещение временной зоны, при условии, что дата и время находятся в положенном месте вводимого значения.
Table 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; значение часа должно быть <= 12 |
| 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 | временная зона, заданная полным именем |
Table 8-12. Ввод значений временной зоны
| Пример | Описание |
|---|---|
| PST | Pacific Standard Time |
| America/New_York | Полное имя временной зоны |
| -8:00 | ISO-8601 смещение для PST |
| -800 | ISO-8601 смещение для PST |
| -8 | ISO-8601 смещение для PST |
| zulu | Военная аббревиатура для UTC |
| z | Краткая форма zulu |
Список имён временных зон, которые распознаются при вводе смотрите вo Appendix B.
Правильные вводимые значения для типа даты и времени состоят из слитного значения даты и времени, за которым следует необязательное значение временной зоны, за которым следует необязательный суффикс AD или BC. (В качестве альтернативы, AD/BC могут указываться перед временной зоной, но так делать не рекомендуется.) Таким образом
1999-01-08 04:05:06
и
1999-01-08 04:05:06 -8:00
являются правильными значениями, которые соответствуют стандарту ISO 8601. В дополнение, поддерживается расширенный формат стандарта. Вы также можете задать полное имя временной зоны:
1999-01-08 04:05:06 America/New_York
В дополнение, расширенный формат:
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 timezone, PostgreSQL будет молча игнорировать любые указания временной зоны. Таким образом, итоговое значение будет получено из полей даты/времени, которые присутствуют во вводимом значении и не будет скорректировано по временной зоне.
Для типа timestamp with time zone, значение внутри, всегда хранится в UTC (Universal Coordinated Time, обычно известное как время Гринвичского (нулевого) меридиана или GMT). Вводимое значение, которое имеет явно заданную временную зону, конвертируется в UTC, используя смещение, соответствующее этой временной зоне. Если при вводе временная зона не указана, то временная зона берётся из системного параметра timezone и точно также конвертируется в UTC, используя смещение временной зоны, указанной в переменной timezone.
Когда выводится значение типа timestamp with time zone, оно всегда конвертируется из UTC во временную зону, заданную переменной timezone и отображается как местное время в этой зоне. Чтобы увидеть это же время в другой временной зоне, или измените значение переменной timezone или используйте конструкцию AT TIME ZONE (см. Section 9.9.3).
Преобразования между типами timestamp without time zone и timestamp with time zone обычно означают, что значение timestamp without time zone должно быть принято или выдано как значение местного времени временной зоны, указанной в переменной timezone. Ссылка на другую временную зону при таком преобразовании может быть указана с помощью конструкции AT TIME ZONE.
Значения типа interval могут записываться по следующему синтаксису:
[@] количество элемент [количество элемент...] [направление]
Где: количество ‐ это количество (возможно со знаком); элементов, которые могут иметь значение second(секунда), minute(минута), hour(час), day(день), week(неделя), month(месяц), year(год), decade(декада), century(век), millennium(тысячелетие), или аббревиатуру или множество этих элементов; направление может иметь значения ago(назад) или пусто. Знак (@) не обязательно важен. Итоговые значения разных элементов неявно дополняются соответствующим знаком.
Количество дней, часов, минут и секунд может быть задано без явного указания элементов. Например, значение '1 12:59:10' будет распознано также как и значение '1 день 12 часов 59 минут 10 секунд'.
Необязательное значение точности p должно быть в интевале от 0 до 6, и используется как значение точности по умолчанию для вводимых значений.
Внутренние значения типа interval хранятся как месяцы, дни и секунды.
Это сделано потому что количество дней в месяце меняется и день может иметь
23 или 25 часов, если выполняется переход на летнее и зимнее время.
Поскольку интервалы обычно создаются из строковых констант или разности
значений timestamp, то данный метод хранения прекрасно работает
в большинстве случаев. Функции justify_days и
justify_hours доступны для корректировки дней и часов,
которые выходят за пределы их нормальных периодов.
PostgreSQL поддерживает для удобства несколько специальных значений ввода даты/времени, как показано в Table 8-13. Из этих значений infinity и -infinity специально представлены внутри СУБД и будут показываться в таком же виде; но другие являются просто сокращениями, которые будут преобразованы при вводе в обычные значения даты/времени. (Например, now и подобные строки преобразоываются в определённое значение времени в момент, когда их читает СУБД.) Все такие значения, когда они используется в командах SQL, должны записываться в одинарных кавычках.
Table 8-13. Специальные значения даты/времени
| Вводимая строка | Допустимые типы данных | Описание |
|---|---|---|
| epoch | date, timestamp | 1970-01-01 00:00:00+00 (Нулевое системное время Unix) |
| infinity | timestamp | позже, чем все другие временные штампы |
| -infinity | 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. Последние четыре функции позволяют задать необязательную точность. (См. Section 9.9.4.) Заметим однако, что это функции SQL и что они не рапознаются как строки данных.
Формат вывода значений типов даты/времени может быть установлен в одном из четырёх стилей: ISO 8601, SQL (Ingres), традиционный POSTGRES и German, с помощью команды SET datestyle. По умолчанию устанавливается формат ISO. (Стандарт SQL требует использования формата ISO 8601. Название формата вывода "SQL" является исторически сложившимся.) Table 8-14 показывает примеры каждого стиля вывода. Вывод значений типов date и time, разумеется, только часть даты и времени в соответствии с данными примерами.
Table 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 |
В стилях SQL и POSTGRES, день идёт перед месяцем, если быд задан порядок полей DMY, в противном случае месяц следует перед днём. (См. Section 8.5.1 о том как эта настройка также отказывает эффект на интерпретацию вводимых значений.) В Table 8-15 показан пример.
Table 8-15. Соглашения по датам
| Установка стиля даты | Входимое значение | Пример вывода |
|---|---|---|
| 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 |
Выводимые значения типа interval выглядят также как и при вводе, за исключением того, что единицы, такие как век или неделя преобразовываются в годы и дни, а ago преобразуется в соответствующий знак. В режиме ISO выводимое значение выглядит как
[ quantity unit [ ... ] ] [ days ] [ hours:minutes:seconds ]
Пользователь может выбрать нужные ему стили даты/времени с
помощью команды SET datestyle, параметра DateStyle в конфигурационном файле
postgresql.conf или с установив переменную
окружения PGDATESTYLE на сервере или клиенте.
Функция форматирования to_char
(см. Section 9.8) также может быть
использована как более гибкий способ форматирования выводимых
значений даты/времени.
Временные зоны и соглашения по временным зонам зависят не только геометрии планеты, но и от политического территориального деления. Временные зоны во всём мире стали чем-то стандартным, начиная с 1900 года, но продолжают соответствующим образом изменяться, учитывая правила летнего/зимнего времени. В настоящий момент PostgreSQL поддерживает правила перехода на летнее/зимнее время в периоде с 1902 по 2038 годы (соответствующие полному диапазону системного времени Unix). Время за пределами этого диапазона считается "стандартным временем" для выбранной временной зоны, не учитывая, что оно может быть неправильным для части года.
PostgreSQL прикладывает усилия для совместимости со стандартом SQL при работе с типовыми операциями. Однако, в стандарте SQL есть разнородная смесь типов даты и времени, а также совместимых с ними типов. Вот две очевидные проблемы:
Хотя тип date не имеет ассоциированную с ним временную зону, тип time может её иметь. Временные зоны в реальном мире имеют небольшую значимость, в отличие от ассоциированных с ними дат и времени, а смещение может изменяться в течении года при переходе на зимнее/летнее время.
Временная зона по умолчанию задаётся как постоянное цифровое смещение от времени UTC. Таким образом, становится невозможным адаптировать арифметические операции над датой/временем при переходе на летнее/зимнее время при работе с DST.
Из-за этих трудностей, мы рекомендуем при использовании временных зон использовать типы даты/времени, которые содержат как дату, так и время. Мы не рекомендуем использовать тип time with time zone (хотя он поддерживается PostgreSQL для старых приложений и для совместимости со стандартом SQL). PostgreSQL для всех типов, которые содержат только дату или только время использует вашу локальную временную зону.
Все даты, учитывающие временную зону и время хранятся внутри в UTC. Перед тем как значения будут выданы клиенту, они преобразуются в локальное время в соответствии с настройками, указанными в параметре timezone.
Параметр timezone может быть установлен в файле postgresql.conf или любым другим стандартным способом, описанным в Chapter 17. Также существует несколько специальных способов его установки:
Если параметр timezone не задан ни в файле postgresql.conf ни как опция командной строки для сервера, то сервер пытается использовать в качестве временной зоны по умолчанию значение переменной окружения TZ. Если переменная TZ не задана или не содержит в себе одно из известных PostgreSQL значений имени временной зоны, то сервер пытается определить временную зону по умолчанию у операционной системы, проверяя поведение библиотечной функции C localtime(). Временная зона по умолчанию выбирается в этом случае, как наиболее подходящая из известных PostgreSQL временных зон.
В SQL команда SET TIME ZONE устанавливает временную зону для текущей сессии. Эта команда является альтернативой команды SET TIMEZONE TO, которая имеет более соответствующий SQL синтаксис.
Если на стороне клиента установлена переменная окружения PGTZ, то при подключении к серверу, её значение будет использоваться для неявного выполнения команды SET TIME ZONE приложениями, которые используют библиотеку libpq.
Список доступных временных зон смотрите в Appendix B.
PostgreSQL использует для всех вычислений даты/времени Юлианский календарь. У него есть прекрасное свойство предсказывать/вычислять любую дату, которая следует за годом 4713 до н.э., потому что используется длина года, равная 365.2425 дням.
Соглашения по датам, которые использовались до 19-го века интересны, но не позволяют целостно использовать их при кодировании обработки даты/времени.