Генерация последовательности дат и generate_series в PostgreSQL / Хабр
Велопредупреждение
Данная статья может оказаться сферическим примером велосипедостроения. Если вам известно стандартное или более изящное решение задачи, то буду рад увидеть его в комментариях.
Однажды на одном из проектов нам понадобилось составить отчет по финансовым операциям за период с группировкой промежуточных итогов на конец месяца.
Задача в общем-то простая, определить требуемые периоды внутри большого интервала, привязать каждую операцию к подходящему периоду, сгруппировать и сложить сумму.
Для генерации периодов внутри интервала я привычно взял функцию generate_series, которую часто использую для генерации числовых последовательностей. Сверился с документацией насчет возможности генерации последовательности дат, рассмотрел пример, написал запрос и озадачился.
select gs::date
from generate_series('2018-01-31', '2018-05-31', interval '1 month') as gs;| gs |
|---|
31. 01.2018 |
| 28.02.2018 |
| 28.03.2018 |
| 28.04.2018 |
| 28.05.2018 |
Результат оказался столь же неожиданным, как и логичным. Функция generate_series по честному итерационно сгенерировала последовательность дат по принципу последовательного прибавления сдвига к предыдущему значению. При этом на каждом шаге проверялась корректность и правка полученной даты. 31 февраля не бывает, поэтому дата преобразовалась в 28 февраля и дальнейшее прибавление месяца сбила всю последовательность на 28 число.
UPD. Пояснения после вопросов в комментариях. Вообще изначальная задача стоит шире — группировать данные на произвольные дни месяца. Например, сгруппировать по 20-м числам каждого месяца, по 15-м числам, но с такими датами проблем при генерации не наблюдается. Механизм, который мы ищем должен одинаково хорошо строить последовательность 10-х чисел каждого месяца, 21-х чисел и корректно отрабатывать концы месяцев.
Интересно как поведет себя операция сложения с несколькими месяцами сразу? Что будет если мы будем прибавлять интервал не итерационно, а «оптом»?
select '2018-01-31'::date +interval '1 mons' 28.02.2018 select '2018-01-31'::date +interval '2 mons' 31.03.2018
В этом случае прибавление производится по честному.
Как применяя этот подход сгенерировать нужные даты?
Если известно количество месяцев, то очень просто:
select '2018-01-31'::date +make_interval(0, i) as gs from generate_series(0, 4, 1) as i
| gs |
|---|
| 31.01.2018 |
| 28.02.2018 |
| 31.03.2018 |
| 30.04.2018 |
| 31.05.2018 |
Что делать если известны только дата начала и дата конца?
Данную задачу можно довольно просто решить написанием хранимой функции и простым циклом в ней, однако нас интересует вариант реализации когда нет возможности или желания засорять структуру БД лишними объектами.
Попробуем свести задачу к предыдущей.
Следующий код представляет собой в некоторой степени макетную плату и не претендует на изящность, первые варианты запросов мы в компании пишем с упором на гибкость и взаимозаменяемость блоков
/* Вводим что-то типа переменных, чтобы в едином месте можно было вводить входные данные, когда нет возможности использовать параметры */
with dates as (
select '2018-01-31'::date as dt1, '2018-05-31'::date as dt2
),
/* Вычисляем разницу между датами в "иерархических" единицах */
g_age as (
select age( (select dt2 from dates), (select dt1 from dates))
),
/* Считаем сколько месяцев в полученной разнице (годы*12 + месяцы) и добавляем +1 месяц на возможную потерю при округлении */
months as (
select (extract(year from (select * from g_age))*12 +
extract(month from (select * from g_age))+1)::integer
),
/* Количество посчитано, генерируем последовательность и добавляем проверку на выход из первоначального диапазона из-за возможного лишнего месяца, который мы добавили как корректировку округления */
seq as(
select ((select dt1 from dates) + make_interval(0, gs)) as gs
from generate_series (
0,
(select * from months),
1
) as gs
where ((select dt1 from dates) + make_interval(0, gs)) <= (select dt2 from dates)
)
/* Ну и собственно смотрим что у нас получилось */
select * from seq | gs |
|---|
31. |
| 28.02.2018 |
| 31.03.2018 |
| 30.04.2018 |
| 31.05.2018 |
Решение получилось достаточно громозким, но рабочим и его достаточно просто интегрировать в другие запросы через механизм with.
Отчет мы реализовали, однако мысль что этот запрос мало того, что громоздкий, так еще и ограничен в своем использовании только шагами по целым месяцам не давал покоя.
Вариант 2.
Спустя время меня осенило, что последовательная генерация дат по сути рекурсивная процедура. Только не в чистом виде, так как в нашем случае расчет следующей даты от предыдущей приводит к первоначальной проблеме. Зато на каждом шаге мы можем увеличивать интервал, прибавляемый к началу нашего периода:
/* Снова определяем наши псевдопараметры-псевдопеременные, расширив их тип до timestamp */
with recursive dates as (
select
'2018-01-31'::timestamp as dt1,
'2018-05-31'::timestamp as dt2,
interval '1 month' as interval
),
/* Реализуем рекурсивный запрос в котором на каждом шаге увеливается целочисленный счетчик, а каждая следующая дата получается из первоначальной путем прибавления интервала, умноженного на счетчик.
Останавливается генерация, когда вновь полученная дата выходит за границу периода*/
pr AS(
select
1 as i,
(select dt1 from dates) as dt
union
select
i+1 as i,
( (select dt1 from dates) + ( select interval from dates)*i)::timestamp as dt
from pr
where ( ((select dt1 from dates) + (select interval from dates)*i)::timestamp) <=(select dt2 from dates)
)
select dt as gs from pr;
Останавливается генерация, когда вновь полученная дата выходит за границу периода*/
pr AS(
select
1 as i,
(select dt1 from dates) as dt
union
select
i+1 as i,
( (select dt1 from dates) + ( select interval from dates)*i)::timestamp as dt
from pr
where ( ((select dt1 from dates) + (select interval from dates)*i)::timestamp) <=(select dt2 from dates)
)
select dt as gs from pr;| gs |
|---|
| 31.01.2018 |
| 28.02.2018 |
| 31.03.2018 |
| 30.04.2018 |
| 31.05.2018 |
Данный запрос корректно работает с любыми входными временными отрезками и интервалами.
8,5 Дата/время Типы PostgreSQL поддерживает полный набор типов даты и времени,приведенный в таблице 8.9.
Формат даты для типа данных даты в PostgreSQL-yyyy-mm-dd.Этот формат используется как для хранения данных,так и для вставки данных.
PostgreSQL принимает ваш локальный часовой пояс для любого типа,содержащего только дату или время.Все даты и время с учетом временной зоны хранятся внутри системы в UTC.Перед отображением клиенту они преобразуются в местное время в зоне,указанной параметром конфигурации TimeZone.
Стили даты/времени могут быть выбраны пользователем с помощью команды SET datestyle,параметра DateStyle в конфигурационном файле postgresql.conf или переменной окружения PGDATESTYLE на сервере или клиенте.Функция форматирования to_char также доступна как более гибкий способ форматирования вывода даты/времени.
- 8.5.1 Дата/время входа
- 8.5.2.Дата/время выхода
- 8.5.3.Часовые пояса
- 8.5.4.Интервальный вход
- 8.5.5.Интервальный выход
PostgreSQL поддерживает полный набор типов даты и времени SQL, показанный в Таблице 8.9 . Операции, доступные для этих типов данных, описаны в Разделе 9.9 .
Даты считаются по григорианскому календарю, даже за годы до того, как этот календарь был введен ( дополнительную информацию см. В разделе B.6 ).
Таблица 8.9. Типы даты / времени
| Name | Storage Size | Description | Low Value | High Value | Resolution |
|---|---|---|---|---|---|
timestamp [ (p) ] [ without time zone ] | 8 bytes | как дата,так и время (без часового пояса) | 4713 BC | 294276 AD | 1 microsecond |
timestamp [ (p) ] with time zone | 8 bytes | как дата,так и время,с часовым поясом | 4713 BC | 294276 AD | 1 microsecond |
date | 4 bytes | дата (без времени суток) | 4713 BC | 5874897 AD | 1 day |
time [ (p) ] [ without time zone ] | 8 bytes | время суток (без даты) | 00:00:00 | 24:00:00 | 1 microsecond |
time [ (p) ] with time zone | 12 bytes | время суток (без даты),с часовым поясом | 00:00:00+1559 | 24:00:00-1559 | 1 microsecond |
interval [ fields ] [ (p) ] | 16 bytes | time interval | -178000000 years | 178000000 years | 1 microsecond |
Note
Стандарт SQL требует, чтобы запись просто
timestampбыла эквивалентнаtimestamp without time zone, и PostgreSQL уважает это поведение.timestamptz— это сокращение отtimestamp with time zone; это расширение PostgreSQL.
time , timestamp и interval принимают необязательное значение точности p , которое указывает количество дробных цифр, сохраняемых в поле секунд. По умолчанию нет явных ограничений на точность. Допустимый диапазон
У типа 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 и p , fields должны включать SECOND , поскольку точность применяется только к секундам.
Тип time with time zone определяется стандартом SQL, но определение демонстрирует свойства, которые приводят к сомнительной полезности.
В большинстве случаев комбинация date , time , timestamp without time zone и timestamp with time zone должна обеспечивать полный диапазон функций даты и времени, необходимых для любого приложения.
Ввод даты и времени принимается практически в любом приемлемом формате, включая ISO 8601, SQL-совместимый, традиционный POSTGRES и другие. Для некоторых форматов порядок дня, месяца и года при вводе даты неоднозначен, и поддерживается указание ожидаемого порядка этих полей. Установите для параметра DateStyle значение MDY MDY чтобы выбрать интерпретацию месяц-день-год, DMY , чтобы выбрать интерпретацию день-месяц-год, или YMD , чтобы выбрать интерпретацию год-месяц-день.
PostgreSQL более гибок в обработке ввода даты/времени, чем требует стандарт SQL. См. Приложение B для точных правил синтаксического анализа ввода даты/времени и для распознаваемых текстовых полей, включая месяцы, дни недели и часовые пояса.
Помните, что любой вводимый литерал даты или времени должен быть заключен в одинарные кавычки, как текстовые строки. Обратитесь к Разделу 4.1.2.7 для получения дополнительной информации. SQL требует следующего синтаксиса
type [ (p) ] 'value'
где p — необязательная спецификация точности, дающая количество цифр дробной части в поле секунд. Точность может быть указана для типов time , timestamp и interval в диапазоне от 0 до 6. Если в спецификации константы не указана точность, по умолчанию используется точность буквального значения (но не более 6 цифр).
8.5.1.1. Dates
В таблице 8.10 показаны некоторые возможные входные данные для типа date .
Таблица 8.10. Ввод даты
| Example | Description |
|---|---|
| 1999-01-08 | ISO 8601;8 января в любом режиме (рекомендуемый формат) |
| 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 | Julian date |
| 8 января 99 г.до н.э. | 99 год до н.э. |
8.5.1.2. Times
Типы time [ (p) ] without time zone суток: время [(p)] без часового пояса и 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. Ввод времени
| Example | Description |
|---|---|
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. | ISO 8601,с часовым поясом в виде смещения UTC |
04:05:06-08:00 | ISO 8601,с часовым поясом в виде смещения UTC |
04:05-08:00 | ISO 8601,с часовым поясом в виде смещения UTC |
040506-08 | ISO 8601,с часовым поясом в виде смещения UTC |
040506+0730 | ISO 8601,с дробным часовым поясом в качестве смещения UTC |
040506+07:30:00 | Смещение UTC,заданное в секундах (не допускается в ISO 8601) |
04:05:06 PST | часовой пояс по аббревиатуре |
2003-04-12 04:05:06 America/New_York | часовой пояс,указанный по полному имени |
789-8Таблица 8.12. Ввод часового пояса
| Example | Description |
|---|---|
PST | Сокращение (для тихоокеанского стандартного времени) |
America/New_York | Имя полного часового пояса |
PST8PDT | спецификация часового пояса в стиле POSIX |
-8:00:00 | Смещение UTC для PST |
-8:00 | Смещение UTC для PST (расширенный формат ISO 8601) |
-800 | Смещение UTC для PST (базовый формат ISO 8601) |
-8 | Смещение UTC для PST (базовый формат ISO 8601) |
zulu | Военная аббревиатура для UTC |
z | Краткая форма zulu (также в ISO 8601) |
Обратитесь к Разделу 8.
5.3 для получения дополнительной информации о том, как указать часовые пояса.
8.5.1.3.Штампы времени
Допустимый ввод для типов отметок времени состоит из конкатенации даты и времени, за которыми следует необязательный часовой пояс, за которым следует необязательный AD или BC . (В качестве альтернативы, AD / BC может стоять перед часовым поясом, но это не является предпочтительным порядком.) Таким образом:
1999-01-08 04:05:06
and:
1999-01-08 04:05:06 -8:00
являются действительными значениями,которые следуют стандарту ISO 8601.Кроме того,общий формат:
January 8 04:05:06 1999 PST
is supported.
Стандарт 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 (см. Раздел 9.9.4 ).
Преобразования между 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. Специальные вводы даты / времени
| Input String | Valid Types | Description |
|---|---|---|
epoch | date, timestamp | 1970-01-01 00:00:00+00 (нулевое системное время Unix) |
infinity | date, timestamp | позднее всех остальных штампов времени |
-infinity | date, timestamp | раньше всех остальных штампов времени |
now | date, time, timestamp | время начала текущей операции |
today | date, timestamp | полночь ( 00:00 ) сегодня |
tomorrow | date, timestamp | полночь ( 00:00 ) завтра |
yesterday | date, timestamp | полночь ( 00:00 ) вчера |
allballs | time | 00:00:00. 00 UTC |
Следующие SQL-совместимые функции также могут использоваться для получения текущего значения времени для соответствующего типа данных: CURRENT_DATE , CURRENT_TIME , CURRENT_TIMESTAMP , LOCALTIME , LOCALTIMESTAMP . (См. Раздел 9.9.5 .) Обратите внимание, что это функции SQL и не распознаются в строках ввода данных.
© 1996–2022 The PostgreSQL Global Development Group
Licensed under the PostgreSQL License.
https://www.postgresql.org/docs/15/datatype-datetime.html
PostgreSQL 15.0
-
8.6.Булевский тип
PostgreSQL предоставляет стандартный тип boolean;см.
таблицу 8.19. -
8.3.Типы персонажей
Table 8.4.
-
Caution
В то время как входные строки now,today,tomorrow и yesterday прекрасно подходят для использования интерактивных SQL-команд,они могут иметь неожиданное поведение при сохранении выполненных операций.
-
8.
7.Перечисленные типыПеречислимые типы-это данные,которые состоят из статического,упорядоченного набора значений.
таблицу 8.19.
7.Перечисленные типы- 1
- …
- 163
- 164
- 165
- 166
- 167
- …
- 763
- Next
EDB Docs — EDB Postgres Advanced Server v15
В следующем обсуждении типов даты/времени предполагается, что для параметра конфигурации edb_redwood_date установлено значение TRUE всякий раз, когда создается или изменяется таблица.
EDB Postgres Advanced Server поддерживает типы даты/времени, указанные в таблице.
Когда DATE появляется как тип данных столбца в командах языка определения данных (DDL) CREATE TABLE или ALTER TABLE , он преобразуется в TIMESTAMP на момент сохранения определения таблицы в базе данных. Таким образом, вместе с датой в столбце сохраняется и временная составляющая.
ДАТА может отображаться как тип данных:
- Переменная в разделе декларации SPL
- Тип данных формального параметра в процедуре SPL или функции SPL
- Тип возвращаемого значения функции SPL
В этих случаях всегда переводится на TIMESTAMP и, таким образом, может обрабатывать компонент времени, если он присутствует.
TIMESTAMP принимает необязательное значение точности p , которое указывает количество дробных цифр, сохраняемых в поле секунд.
Допустимый диапазон p — от 0 до 6. Значение по умолчанию — 6 .
Когда значения TIMESTAMP хранятся как числа с плавающей запятой двойной точности (по умолчанию), эффективный предел точности может быть меньше 6. TIMESTAMP 9Значения 0004 хранятся в секундах до или после полуночи 01.01.2000. Микросекундная точность достигается для дат в пределах нескольких лет от 01 января 2000 г., но точность ухудшается для более отдаленных дат. Когда значения TIMESTAMP хранятся как 8-байтовые целые числа (опция времени компиляции), микросекундная точность доступна для всего диапазона значений. Однако 8-байтовые целые метки времени имеют более ограниченный диапазон дат, чем показано в таблице: от 4713 г. до н.э. до 294276 г. н.э.
TIMESTAMP (p) С ЧАСОВЫМ ПОЯСОМ аналогичен TIMESTAMP (p) , но также включает часовой пояс.
Типы ИНТЕРВАЛ
ИНТЕРВАЛ значения определяют период времени.
Значения типа INTERVAL состоят из полей, описывающих значение данных. В следующей таблице перечислены поля, разрешенные для типа INTERVAL .
Поля должны быть представлены в порядке убывания, от ГОД до МЕСЯЦЕВ и от ДНЕЙ до ЧАСОВ , МИНУТ , а затем СЕКУНД .
EDB Postgres Advanced Server поддерживает два типа INTERVAL , совместимых с базами данных Oracle.
Первым вариантом, поддерживаемым EDB Postgres Advanced Server, является ИНТЕРВАЛ ДЕНЬ-СЕКУНД [(p)] . INTERVAL DAY TO SECOND [(p)] сохраняет временной интервал в днях, часах, минутах и секундах.
p определяет точность второго поля .
EDB Postgres Advanced Server интерпретирует это значение как 1 день, 2 часа, 34 минуты, 5 секунд и 678 тысячных долей секунды:
ИНТЕРВАЛ '1 2:34:5,678' ДЕНЬ ДО СЕКУНДЫ(3)
EDB Postgres Advanced Server интерпретирует это значение как 1 день и 23 часа:
INTERVAL '1 23' DAY TO HOUR
EDB Postgres Advanced Server интерпретирует это значение как 2 часа 34 минуты:
INTERVAL '2:34 ' ЧАС В МИНУТУ
EDB Postgres Advanced Server интерпретирует это значение как 2 часа, 34 минуты, 56 секунд и 13 тысячных долей секунды.
Доля секунды округляется до 13 из-за указанной точности.
INTERVAL '2:34:56.129' HOUR TO SECOND(2)
Второй вариант, поддерживаемый EDB Postgres Advanced Server и совместимый с базами данных Oracle, — это INTERVAL YEAR TO MONTH . Этот вариант хранит временной интервал в годах и месяцах.
EDB Postgres Advanced Server интерпретирует это значение как 12 лет и 3 месяца:
ИНТЕРВАЛ '12-3' ГОД В МЕСЯЦ
EDB Postgres Advanced Server интерпретирует это значение как 12 лет и 3 месяца:
ИНТЕРВАЛ '456 ' YEAR(2)
EDB Postgres Advanced Server интерпретирует это значение как 25 лет:
INTERVAL '300' MONTH
Ввод даты/времени
Ввод даты и времени принимается в формате, совместимом с ISO 8601 SQL, оракул по умолчанию dd-MON-yy , а также ряд других форматов при условии отсутствия двусмысленности в отношении того, какой компонент является годом, месяцем и днем.
Однако мы настоятельно рекомендуем использовать функцию TO_DATE , чтобы избежать неоднозначности.
Заключите любой литерал даты или времени в одинарные кавычки, как текстовые строки. Также допускается следующий стандартный синтаксис SQL:
тип "значение"
тип либо DATE , либо TIMESTAMP .
значение — текстовая строка даты/времени.
Даты
В следующем блоке показаны некоторые возможные форматы ввода дат, все из которых соответствуют 8 января 1999 г.:
Пример 8 января 1999 г. 1999-01-08 1999-январь-08 08 января 1999 г. 08 января 1999 г. 08 января 99 г. Янв-08-99 1999
Значения даты можно присвоить столбцу или переменной DATE или TIMESTAMP . Поля часов, минут и секунд устанавливаются равными нулю, если вы не добавляете значение даты со значением времени.
Times
Некоторые примеры компонента времени даты или метки времени показаны в таблице.
Отметки времени
Допустимый ввод для отметок времени состоит из конкатенации даты и времени. Вы можете отформатировать часть времени, относящуюся к дате, в соответствии с любым из примеров, показанных в разделе Даты. Временная часть метки времени может быть отформатирована в соответствии с любым из примеров, показанных в таблице в Times.
В этом примере показана отметка времени, соответствующая стандартному формату Oracle:
08-JAN-99 04:05:06
В этом примере показана метка времени, соответствующая стандарту ISO 8601:
Формат вывода по умолчанию для типов даты/времени:
- (
дд-мон-гг), называемый стилем дат Redwood , совместимым с базами данных Oracle - (
гггг-мм-дд), именуемый форматом ISO 8601
Используемый формат зависит от интерфейса приложения с базой данных. Приложения, использующие JDBC, такие как SQL Interactive, всегда представляют дату в формате ISO 8601.
Другие приложения, такие как PSQL, представляют дату в формате Redwood.
В следующей таблице приведены примеры выходных форматов для двух стилей: Redwood и ISO 8601.
Internals
EDB Postgres Advanced Server использует даты по юлианскому календарю для всех расчетов даты и времени. Юлианские даты правильно предсказывают или рассчитывают любую дату после 4713 г. до н.э., исходя из предположения, что продолжительность года составляет 365,2425 дней.
PostgreSQL PL/Java — Сопоставление между типами даты/времени PostgreSQL и Java
Устаревшие сопоставления JDBC
Первые сопоставления, которые будут указаны в JDBC, использовали специфичные для JDBC классы java.sql.Date , java.sql.Time и java.sql.Timestamp , все из которых основаны на java.util .Date (но только как деталь реализации; их всегда следует рассматривать как собственные типы, а не как экземпляры java.util.Date ).
PL/Java и возвращаемые значения могут быть объявлены в Java, чтобы иметь эти типы, объекты этих типов могут быть переданы в методы PreparedStatement.setObject , ResultSet.updateObject и SQLOutput.writeObject , а также в методы, специфичные для этих типов. Методы JDBC getObject и readObject , которые не принимают параметр Class , будут возвращать объекты этих типов при получении значений даты или времени PostgreSQL.
Недостатки
Эти классы никогда не были хорошим представлением значений даты/времени PostgreSQL, потому что они основаны на java.util.Date , что подразумевает знание часового пояса, даже если они используются для представления значений PostgreSQL без часового пояса. совсем. Для всех этих преобразований, кроме одного, PL/Java должен выполнять вычисления часового пояса, за одним исключением, которое не интуитивно понятно, временная метка с часовым поясом .
Преобразования незонированных значений связаны со скрытой зависимостью от текущей настройки сеанса PostgreSQL, равной 9.0003 TimeZone , который может варьироваться от сеанса к сеансу в зависимости от предпочтений подключающегося клиента.
Существуют известные давние проблемы с преобразованиями PL/Java в эти типы и из них, подробно описанные в выпуске №200. Хотя ожидается, что эти конкретные проблемы будут исправлены в будущем выпуске PL/Java, сопоставления Java 8/JDBC 4.2, описанные далее, являются настоятельно рекомендуемой альтернативой устаревшим сопоставлениям, позволяющим полностью избежать этих проблем.
Сопоставление даты/времени Java 8/JDBC 4.2
Java 8 представила значительно улучшенный набор классов даты/времени в пакете java.time , указанном в JSR 310. JDBC 4.2 (версия в Java 8) позволяет использовать их в качестве альтернативных сопоставлений классов Java для типов SQL date , время (с часовым поясом и без него) и отметка времени (с часовым поясом/без него).
Эти новые типы гораздо лучше подходят для соответствующих типов PostgreSQL, чем исходный JDBC java.sql Date / Time / Отметка времени класса.
Чтобы избежать критических изменений, JDBC 4.2 не изменяет то, что любой из ранее существовавших API JDBC делает по умолчанию. Методы getDate , getTime и getTimestamp в ResultSet по-прежнему возвращают те же типы java.sql , как и getObject в форме, не определяющей класс. Вместо этого обновление использует методы общего назначения ResultSet.getObject , которые принимают 9Параметр 0003 Class (добавлен в JDBC 4.1), а также метод SQLInput.readObject с параметром Class (отсутствует в 4.1, но добавлен в 4.2), поэтому вызывающий объект может запросить java .time класс, передав права Class :
| Тип PostgreSQL | Передать на getObject / readObject |
|---|---|
дата | java.time.LocalDate.class |
время без часового пояса | java.time.LocalTime.class |
время с часовым поясом | java.time.OffsetTime.class |
метка времени без часового пояса | java.time.LocalDateTime.class |
метка времени с часовым поясом | java.time.OffsetDateTime.class |
time Типы java.time также могут использоваться в качестве параметров и возвращаемых типов функций PL/Java без особых усилий (сгенерированные объявления функций сделают правильные преобразования ) и передается методам установки подготовленных операторов, доступных для записи наборов результатов (для триггеров или составных возвращающих функций) и SQLOutput 9.
0004 для УДТ.
Преобразования в эти типы и обратно никогда не включают часовой пояс сеанса PostgreSQL, который может варьироваться от сеанса к сеансу. В любом коде, разработанном для PL/Java и Java 8 или новее, настоятельно рекомендуется использовать эти типы для манипуляций с датой/временем, так как они лучше подходят для типов PostgreSQL.
PostgreSQL принимает 24:00:00.000000 как допустимое время, в то время как день для LocalTime или OffsetTime достигает максимума в предыдущей наносекунде. Это по-прежнему различимое значение (поскольку разрешение PostgreSQL составляет только микросекунды), поэтому значение PostgreSQL 24 сопоставляется с ним в двух направлениях.
Сопоставление времени и метки времени с часовым поясом
Когда время с часовым поясом сопоставляется с java.time.OffsetTime , значение Java будет иметь смещение зоны, равное смещению, присвоенному значению в PostgreSQL, и, следовательно, в обратном направлении.
Когда временная метка с часовым поясом сопоставляется с java.time.OffsetDateTime , значение Java всегда будет иметь нулевое смещение зоны (UTC). Когда OffsetDateTime , созданный на Java, сопоставляется с PostgreSQL 9Временная метка 0003 с часовым поясом , если ее смещение не равно нулю, используется значение, скорректированное по UTC.
Эти различия в поведении точно отражают то, как PostgreSQL по-разному обрабатывает эти два типа.
Бесконечные даты и метки времени
PostgreSQL допускает дату и метку времени (с часовым поясом или без него) значения бесконечность и -бесконечность .
В соответствующих классах Java такого понятия нет (исходные классы JDBC или JDBC 4.2/JSR 310), но PL/Java будет повторять сопоставление этих значений PostgreSQL с определенными значениями классов Java и будет отображать объекты Java с помощью эти точные значения обратно в PostgreSQL бесконечность или -бесконечность на обратном пути.