9.8. Функции форматирования данных #
Функции форматирования в Postgres Pro предоставляют богатый набор инструментов для преобразования самых разных типов данных (дата/время, целое, числа с плавающей и фиксированной точкой) в форматированные строки и обратно. Все они перечислены в Таблице 9.26. Все эти функции следует одному соглашению: в первом аргументе передаётся значение, которое нужно отформатировать, а во втором — шаблон, определяющий формат ввода или вывода.
Таблица 9.26. Функции форматирования
Функция Описание Пример(ы) |
---|
Преобразует время в строку согласно заданному формату.
|
Преобразует интервал в строку согласно заданному формату.
|
Преобразует число в строку согласно заданному формату; поддерживаются типы
|
Преобразует строку в дату согласно заданному формату.
|
Преобразует строку в число согласно заданному формату.
|
Преобразует строку в значение времени согласно заданному формату. (См. также
|
Подсказка
Функции to_timestamp
и to_date
предназначены для работы с входными форматами, которые нельзя преобразовать простым приведением. Для большинства стандартных форматов даты/времени работает простое приведение исходной строки к требуемому типу и использовать его гораздо легче. Так же и функцию to_number
нет необходимости использовать для стандартных представлений чисел.
Шаблон вывода to_char
может содержать ряд кодов, которые распознаются при форматировании и заменяются соответствующими данными в зависимости от заданного значения. Любой текст, который не является кодом, просто копируется в неизменном виде. Подобным образом в строке шаблона ввода (для других функций) шаблонные коды определяют, какие значения должны поступать из входной строки. Если в строке шаблона есть символы, не относящиеся к шаблонным кодам, соответствующие символы во входной строке просто пропускаются (вне зависимости от того, совпадают ли они с символами в строке шаблона).
Все коды форматирования даты и времени перечислены в Таблице 9.27.
Таблица 9.27. Коды форматирования даты/времени
Код | Описание |
---|---|
HH | час дня (01–12) |
HH12 | час дня (01–12) |
HH24 | час дня (00–23) |
MI | минута (00–59) |
SS | секунда (00–59) |
MS | миллисекунда (000–999) |
US | микросекунда (000000–999999) |
FF1 | десятая доля секунды (0–9) |
FF2 | сотая доля секунды (00–99) |
FF3 | миллисекунда (000–999) |
FF4 | десятитысячная доля секунды (0000–9999) |
FF5 | стотысячная доля секунды (00000–99999) |
FF6 | микросекунда (000000–999999) |
SSSS , SSSSS | секунды после полуночи (0–86399) |
AM , am , PM или pm | обозначение времени до/после полудня (без точек) |
A.M. , a.m. , P.M. или p.m. | обозначение времени до/после полудня (с точками) |
Y,YYY | год (4 или более цифр) с разделителем |
YYYY | год (4 или более цифр) |
YYY | последние 3 цифры года |
YY | последние 2 цифры года |
Y | последняя цифра года |
IYYY | недельный год по ISO 8601 (4 или более цифр) |
IYY | последние 3 цифры недельного года по ISO 8601 |
IY | последние 2 цифры недельного года по ISO 8601 |
I | последняя цифра недельного года по ISO 8601 |
BC , bc , AD или ad | обозначение эры (без точек) |
B.C. , b.c. , A.D. или a.d. | обозначение эры (с точками) |
MONTH | полное название месяца в верхнем регистре (дополненное пробелами до 9 символов) |
Month | полное название месяца с большой буквы (дополненное пробелами до 9 символов) |
month | полное название месяца в нижнем регистре (дополненное пробелами до 9 символов) |
MON | сокращённое название месяца в верхнем регистре (3 буквы в английском; в других языках длина может меняться) |
Mon | сокращённое название месяца с большой буквы (3 буквы в английском; в других языках длина может меняться) |
mon | сокращённое название месяца в нижнем регистре (3 буквы в английском; в других языках длина может меняться) |
MM | номер месяца (01–12) |
DAY | полное название дня недели в верхнем регистре (дополненное пробелами до 9 символов) |
Day | полное название дня недели с большой буквы (дополненное пробелами до 9 символов) |
day | полное название дня недели в нижнем регистре (дополненное пробелами до 9 символов) |
DY | сокращённое название дня недели в верхнем регистре (3 буквы в английском; в других языках может меняться) |
Dy | сокращённое название дня недели с большой буквы (3 буквы в английском; в других языках длина может меняться) |
dy | сокращённое название дня недели в нижнем регистре (3 буквы в английском; в других языках длина может меняться) |
DDD | номер дня в году (001–366) |
IDDD | номер дня в году по ISO 8601 (001–371; первый день года — понедельник первой недели по ISO) |
DD | день месяца (01–31) |
D | номер дня недели, считая с воскресенья (1 ) до субботы (7 ) |
ID | номер дня недели по ISO 8601, считая с понедельника (1 ) до воскресенья (7 ) |
W | неделя месяца (1–5) (первая неделя начинается в первый день месяца) |
WW | номер недели в году (1–53) (первая неделя начинается в первый день года) |
IW | номер недели в году по ISO 8601 (01–53; первый четверг года относится к неделе 1) |
CC | век (2 цифры) (двадцать первый век начался 2001-01-01) |
J | юлианская дата (целое число дней от 24 ноября 4714 г. до н. э. 00:00 по местному времени; см. Раздел B.7) |
Q | квартал |
RM | номер месяца римскими цифрами в верхнем регистре (I–XII; I=январь) |
rm | номер месяца римскими цифрами в нижнем регистре (i–xii; i=январь) |
TZ | аббревиатура часового пояса в верхнем регистре |
tz | аббревиатура часового пояса в нижнем регистре |
TZH | часы часового пояса |
TZM | минуты часового пояса |
OF | смещение часового пояса от UTC (HH или HH : MM ) |
К любым кодам форматирования можно добавить модификаторы, изменяющие их поведение. Например, шаблон форматирования FMMonth
включает код Month
с модификатором FM
. Модификаторы, предназначенные для форматирования даты/времени, перечислены в Таблице 9.28.
Таблица 9.28. Модификаторы кодов для форматирования даты/времени
Модификатор | Описание | Пример |
---|---|---|
Приставка FM | режим заполнения (подавляет ведущие нули и дополнение пробелами) | FMMonth |
Окончание TH | окончание порядкового числительного в верхнем регистре | DDTH , например 12TH |
Окончание th | окончание порядкового числительного в нижнем регистре | DDth , например 12th |
Приставка FX | глобальный параметр фиксированного формата (см. замечания) | FX Month DD Day |
Приставка TM | режим перевода (используются локализованные названия дней и месяцев, исходя из lc_time) | TMMonth |
Окончание SP | режим числа прописью (не реализован) | DDSP |
Замечания по использованию форматов даты/времени:
FM
подавляет дополняющие пробелы и нули справа, которые в противном случае будут добавлены, чтобы результат имел фиксированную ширину. В Postgres Pro модификаторFM
действует только на следующий код, тогда как в OracleFM
её действие распространяется на все последующие коды, пока не будет отключено последующим модификаторомFM
.TM
подавляет замыкающие пробелы вне зависимости от указанияFM
.Функции
to_timestamp
иto_date
игнорируют регистр букв во входной строке; поэтому, например, для шаблоновMON
,Mon
иmon
подойдут одни и те же строки. Если используется приставкаTM
, смена регистра производится в соответствии с правилом сортировки, установленным для входной строки (см. Раздел 22.2).to_timestamp
иto_date
пропускают повторяющиеся пробелы в начале входной строки и вокруг значений даты и времени, если только не используется приставкаFX
. Например,to_timestamp(' 2000 JUN', 'YYYY MON')
иto_timestamp('2000 - JUN', 'YYYY-MON')
будут работать, ноto_timestamp('2000 JUN', 'FXYYYY MON')
выдаст ошибку, так какto_timestamp
ожидает только один пробел. ПриставкаFX
должна быть первой в шаблоне.Разделитель (пробел или отличный от цифры/буквы символ) в строке шаблона функций
to_timestamp
иto_date
соответствует любому разделителю во входной строке или пропускается, если только не добавлена приставкаFX
. Например,to_timestamp('2000JUN', 'YYYY///MON')
иto_timestamp('2000/JUN', 'YYYY MON')
будут работать, ноto_timestamp('2000//JUN', 'YYYY/MON')
выдаст ошибку, так как количество разделителей во входной строке превышает количество разделителей в шаблоне.Если добавляется приставка
FX
, разделитель в строке шаблона соответствует ровно одному символу во входной строке. Но заметьте, что символ во входной строке не обязательно должен совпадать с символом разделителя в шаблоне. Например,to_timestamp('2000/JUN', 'FXYYYY MON')
будет работать, аto_timestamp('2000/JUN', 'FXYYYY MON')
выдаст ошибку, потому что второй пробел в строке шаблона забирает буквуJ
из входной строки.Коду шаблона
TZH
может соответствовать число со знаком. Без приставкиFX
знаки минуса могут быть неоднозначными и восприниматься как разделители. Эта неоднозначность разрешается следующим образом: если число разделителей передTZH
в строке шаблона меньше числа разделителей перед знаком минуса во входной строке, знак минус воспринимается как относящийся кTZH
. В противном случае знак минуса воспринимается как разделитель значений. Например, вto_timestamp('2000 -10', 'YYYY TZH')
в полеTZH
попадает-10
, а вto_timestamp('2000 -10', 'YYYY TZH')
вTZH
попадает значение10
.Шаблоны для
to_char
могут содержать обычный текст; он будет выведен в неизменном виде. Чтобы принудительно вывести текст буквально, даже если он содержит шаблонные коды, подстроку с ним можно заключить в кавычки. Например, в строке'"Hello Year "YYYY'
, кодYYYY
будет заменён годом, а букваY
в словеYear
останется неизменной. В функцияхto_date
,to_number
иto_timestamp
при обработке подстрок в кавычках и буквального текста некоторой длины пропускается такое же число символов во входной строке; например, при обработке подстроки"XX"
будут пропущены два символа (любые, не обязательноXX
).Подсказка
До Postgres Pro 12 во входной строке можно было пропускать произвольный текст, используя в шаблоне символы, отличные от цифр и букв. Например, раньше работало
to_timestamp('2000y6m1d', 'yyyy-MM-DD')
. Теперь для этой цели можно использовать только буквы. Например, шаблоныto_timestamp('2000y6m1d', 'yyyytMMtDDt')
иto_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"')
пропускаютy
,m
иd
.Если вам нужно получить на выходе двойные кавычки, вы должны предварить их символом обратной косой черты, например:
'\"YYYY Month\"'
. В остальном этот символ вне кавычек воспринимается как обычный. Внутри строки в кавычках он указывает, что следующий символ должен восприниматься буквально, каким бы он ни был (но это имеет смысл, только если следующий символ — кавычки или обратная косая черта).Если в функциях
to_timestamp
иto_date
формат года определяется менее, чем 4 цифрами, например, какYYY
, и в переданном значении года тоже меньше 4 цифр, год пересчитывается в максимально близкий к году 2020, т. е.95
воспринимается как 1995.Функции
to_timestamp
иto_date
воспринимают отрицательные значения годов как относящиеся к годам до н. э. Если же указать отрицательное значение и добавить явный признакBC
(до н. э.), год будет относиться к н. э. Нулевое значение года воспринимается как 1 год до н. э.В функциях
to_timestamp
иto_date
с преобразованиемYYYY
связано ограничение, когда обрабатываемый год записывается более чем 4 цифрами. ПослеYYYY
необходимо будет добавить нецифровой символ или соответствующий код, иначе год всегда будет восприниматься как 4 цифры. Например, вto_date('200001130', 'YYYYMMDD')
(с годом 20000) год будет интерпретирован как состоящий из 4 цифр; чтобы исправить ситуацию, нужно добавить нецифровой разделитель после года, как вto_date('20000-1130', 'YYYY-MMDD')
, или код как вto_date('20000Nov30', 'YYYYMonDD')
.Функции
to_timestamp
иto_date
принимают полеCC
(век), но игнорируют его, если в шаблоне есть полеYYY
,YYYY
илиY,YYY
. ЕслиCC
используется сYY
илиY
, результатом будет год в данном столетии. Если присутствует только код столетия, без года, подразумевается первый год этого века.Функции
to_timestamp
иto_date
принимают названия и номера дней недели (DAY
,D
и связанные типы полей), но игнорируют их при вычислении результата. То же самое происходит с полями квартала (Q
).Функциям
to_timestamp
иto_date
можно передать даты по недельному календарю ISO 8601 (отличающиеся от григорианских) одним из двух способов:Год, номер недели и дня недели: например,
to_date('2006-42-4', 'IYYY-IW-ID')
возвращает дату2006-10-19
. Если день недели опускается, он считается равным 1 (понедельнику).Год и день года: например,
to_date('2006-291', 'IYYY-IDDD')
также возвращает2006-10-19
.
Попытка ввести дату из смеси полей григорианского и недельного календаря ISO 8601 бессмысленна, поэтому это будет считаться ошибкой. В контексте ISO 8601 понятия «номер месяца» и «день месяца» не существуют, а в григорианском календаре нет понятия номера недели по ISO.
Внимание
Тогда как
to_date
не примет смесь полей григорианского и недельного календаря ISO,to_char
способна на это, так как форматы вродеYYYY-MM-DD (IYYY-IDDD)
могут быть полезны. Но избегайте форматов типаIYYY-MM-DD
; в противном случае с датами в начале года возможны сюрпризы. (За дополнительными сведениями обратитесь к Подразделу 9.9.1.)Функция
to_timestamp
воспринимает поля миллисекунд (MS
) или микросекунд (US
) как дробную часть число секунд. Например,to_timestamp('12.3', 'SS.MS')
— это не 3 миллисекунды, а 300, так как это значение воспринимается как 12 + 0.3 секунды. Это значит, что для форматаSS.MS
входные значения12.3
,12.30
и12.300
задают одно и то же число миллисекунд. Чтобы получить три миллисекунды, время нужно записать в виде12.003
, тогда оно будет воспринято как 12 + 0.003 = 12.003 сек.Ещё более сложный пример:
to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US')
будет преобразовано в 15 часов, 12 минут и 2 секунды + 20 миллисекунд + 1230 микросекунд = 2.021230 seconds.Нумерация дней недели в
to_char(..., 'ID')
соответствует функцииextract(isodow from ...)
, но нумерацияto_char(..., 'D')
не соответствует нумерации, принятой вextract(dow from ...)
.Функция
to_char(interval)
обрабатывает форматыHH
иHH12
в рамках 12 часов, то есть 0 и 36 часов будут выводиться как12
, тогда какHH24
выводит число часов полностью, и для значенийinterval
результат может превышать 23.
Коды форматирования числовых значений перечислены в Таблице 9.29.
Таблица 9.29. Коды форматирования чисел
Код | Описание |
---|---|
9 | позиция цифры (может отсутствовать, если цифра незначащая) |
0 | позиция цифры (присутствует всегда, даже если цифра незначащая) |
. (точка) | десятичная точка |
, (запятая) | разделитель групп (тысяч) |
PR | отрицательное значение в угловых скобках |
S | знак, добавляемый к числу (с учётом локали) |
L | символ денежной единицы (с учётом локали) |
D | разделитель целой и дробной части числа (с учётом локали) |
G | разделитель групп (с учётом локали) |
MI | знак минус в заданной позиции (если число < 0) |
PL | знак плюс в заданной позиции (если число > 0) |
SG | знак плюс или минус в заданной позиции |
RN | число римскими цифрами (в диапазоне от 1 до 3999) |
TH или th | окончание порядкового числительного |
V | сдвиг на заданное количество цифр (см. замечания) |
EEEE | экспоненциальная запись числа |
Замечания по использованию форматов чисел:
0
обозначает позицию цифры, которая будет выводиться всегда, даже если это незначащий ноль слева или справа.9
также обозначает позицию цифры, но если это незначащий ноль слева, он заменяется пробелом, а если справа и задан режим заполнения, он удаляется. (Для функцииto_number()
эти два символа равнозначны.)Если в формате числа предусмотрено меньше знаков после запятой, чем в форматируемом числе, функция
to_char()
округлит число до указанного количества знаков.Символы шаблона
S
,L
,D
иG
представляют знак, символ денежной единицы, десятичную точку и разделитель тысяч, как их определяет текущая локаль (см. lc_monetary и lc_numeric). Символы точка и запятая представляют те же символы, обозначающие десятичную точку и разделитель тысяч, но не зависят от локали.Если в шаблоне
to_char()
отсутствует явное указание положения знака, для него резервируется одна позиция рядом с числом (слева от него). Если левее нескольких9
помещёнS
, знак также будет приписан слева к числу.Знак числа, полученный кодами
SG
,PL
илиMI
, не присоединяется к числу; например,to_char(-12, 'MI9999')
выдаёт'- 12'
, тогда какto_char(-12, 'S9999')
—' -12'
. (В OracleMI
не может идти перед9
, наоборот9
нужно указать передMI
.)TH
не преобразует значения меньше 0 и не поддерживает дробные числа.PL
,SG
иTH
— расширения Postgres Pro.В
to_number
при использовании шаблонных кодов, не обозначающих данные, таких какL
иTH
, пропускается соответствующее количество входных символов. При этом не имеет значения, совпадают ли они с символами шаблона, если только это не символы данных (то есть цифры, знак числа, десятичная точка или запятая). Например, для подстрокиTH
будут пропущены два символа, не представляющие данные.V
cto_char
умножает вводимое значение на10^
, гдеn
n
— число цифр, следующих заV
.V
сto_number
подобным образом делит значение. Функцииto_char
иto_number
не поддерживаютV
с дробными числами (например,99.9V99
не допускается).Код
EEEE
(научная запись) не может сочетаться с любыми другими вариантами форматирования или модификаторами, за исключением цифр и десятичной точки, и должен располагаться в конце строки шаблона (например,9.99EEEE
— допустимый шаблон).
Для изменения поведения кодов к ним могут быть применены определённые модификаторы. Например, FM99.99
обрабатывается как код 99.99
с модификатором FM
. Все модификаторы для форматирования чисел перечислены в Таблице 9.30.
Таблица 9.30. Модификаторы шаблонов для форматирования чисел
Модификатор | Описание | Пример |
---|---|---|
Приставка FM | режим заполнения (подавляет завершающие нули и дополнение пробелами) | FM99.99 |
Окончание TH | окончание порядкового числительного в верхнем регистре | 999TH |
Окончание th | окончание порядкового числительного в нижнем регистре | 999th |
В Таблице 9.31 приведены некоторые примеры использования функции to_char
.
Таблица 9.31. Примеры to_char
Выражение | Результат |
---|---|
to_char(current_timestamp, 'Day, DD HH12:MI:SS') | 'Tuesday , 06 05:39:18' |
to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS') | 'Tuesday, 6 05:39:18' |
to_char(current_timestamp AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') | '2022-12-06T05:39:18Z' , расширенный формат ISO 8601 |
to_char(-0.1, '99.99') | ' -.10' |
to_char(-0.1, 'FM9.99') | '-.1' |
to_char(-0.1, 'FM90.99') | '-0.1' |
to_char(0.1, '0.9') | ' 0.1' |
to_char(12, '9990999.9') | ' 0012.0' |
to_char(12, 'FM9990999.9') | '0012.' |
to_char(485, '999') | ' 485' |
to_char(-485, '999') | '-485' |
to_char(485, '9 9 9') | ' 4 8 5' |
to_char(1485, '9,999') | ' 1,485' |
to_char(1485, '9G999') | ' 1 485' |
to_char(148.5, '999.999') | ' 148.500' |
to_char(148.5, 'FM999.999') | '148.5' |
to_char(148.5, 'FM999.990') | '148.500' |
to_char(148.5, '999D999') | ' 148,500' |
to_char(3148.5, '9G999D999') | ' 3 148,500' |
to_char(-485, '999S') | '485-' |
to_char(-485, '999MI') | '485-' |
to_char(485, '999MI') | '485 ' |
to_char(485, 'FM999MI') | '485' |
to_char(485, 'PL999') | '+485' |
to_char(485, 'SG999') | '+485' |
to_char(-485, 'SG999') | '-485' |
to_char(-485, '9SG99') | '4-85' |
to_char(-485, '999PR') | '<485>' |
to_char(485, 'L999') | 'DM 485' |
to_char(485, 'RN') | ' CDLXXXV' |
to_char(485, 'FMRN') | 'CDLXXXV' |
to_char(5.2, 'FMRN') | 'V' |
to_char(482, '999th') | ' 482nd' |
to_char(485, '"Good number:"999') | 'Good number: 485' |
to_char(485.8, '"Pre:"999" Post:" .999') | 'Pre: 485 Post: .800' |
to_char(12, '99V999') | ' 12000' |
to_char(12.4, '99V999') | ' 12400' |
to_char(12.45, '99V9') | ' 125' |
to_char(0.0004859, '9.99EEEE') | ' 4.86e-04' |