9.8. Функции форматирования данных

Функции форматирования в PostgreSQL предоставляют богатый набор инструментов для преобразования самых разных типов данных (дата/время, целое, числа с плавающей и фиксированной точкой) в форматированные строки и обратно. Все они перечислены в Таблице 9.23. Все эти функции следует одному соглашению: в первом аргументе передаётся значение, которое нужно отформатировать, а во втором — шаблон, определяющий формат ввода или вывода.

Таблица 9.23. Функции форматирования

ФункцияТип результатаОписаниеПример
to_char(timestamp, text)textпреобразует время в текстto_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)textпреобразует интервал в текстto_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)textпреобразует целое в текстto_char(125, '999')
to_char(double precision, text)textпреобразует плавающее одинарной/двойной точности в текстto_char(125.8::real, '999D9')
to_char(numeric, text)textпреобразует числовое значение в текстto_char(-125.8, '999D99S')
to_date(text, text)dateпреобразует текст в датуto_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)numericпреобразует текст в числоto_number('12,454.8-', '99G999D9S')
to_timestamp(text, text)timestamp with time zoneпреобразует строку во времяto_timestamp('05 Dec 2000', 'DD Mon YYYY')

Примечание

Также имеется функция to_timestamp с одним аргументом; см. Таблицу 9.30.

Шаблон вывода to_char может содержать ряд кодов, которые распознаются при форматировании и заменяются соответствующими данными. Любой текст, который не является кодом, копируется в результат в неизменном виде. Подобным образом, в строке шаблона ввода (для других функций) коды шаблона определяют, какие значения содержит передаваемая текстовая строка.

Все коды форматирования даты и времени перечислены в Таблице 9.24.

Таблица 9.24. Коды форматирования даты/времени

КодОписание
HHчас (01-12)
HH12час (01-12)
HH24час (00-23)
MIминута (00-59)
SSсекунда (00-59)
MSмиллисекунда (000-999)
USмикросекунда (000000-999999)
SSSSчисло секунд с начала суток (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; 1 день — понедельник первой недели по 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квартал (игнорируется функциями to_date и to_timestamp)
RMномер месяца римскими цифрами в верхнем регистре (I-XII; I=январь)
rmномер месяца римскими цифрами в нижнем регистре (i-xii; i=январь)
TZсокращённое название часового пояса в верхнем регистре (поддерживается только в to_char)
tzсокращённое название часового пояса в нижнем регистре (поддерживается только в to_char)
OFсмещение часового пояса от UTC (поддерживается только в to_char)

К любым кодам форматирования можно добавить модификаторы, изменяющие их поведение. Например, шаблон форматирования FMMonth включает код Month с модификатором FM. Модификаторы, предназначенные для форматирования даты/времени, перечислены в Таблице 9.25.

Таблица 9.25. Модификаторы кодов для форматирования даты/времени

МодификаторОписаниеПример
Приставка FMрежим заполнения (подавляет ведущие нули и дополнение пробелами)FMMonth
Окончание THокончание порядкового числительного в верхнем регистреDDTH, например 12TH
Окончание thокончание порядкового числительного в нижнем регистреDDth, например 12th
Приставка FXглобальный параметр фиксированного формата (см. замечания)FX Month DD Day
Приставка TMрежим перевода (выводятся локализованные названия дней и месяцев, исходя из lc_time)TMMonth
Окончание SPрежим числа прописью (не реализован)DDSP

Замечания по использованию форматов даты/времени:

  • FM подавляет дополняющие пробелы и нули справа, которые в противном случае будут добавлены, чтобы результат имел фиксированную ширину. В PostgreSQL модификатор FM действует только на следующий код, тогда как в Oracle FM её действие распространяется на все последующие коды, пока не будет отключено последующим модификатором FM.

  • TM не затрагивает замыкающие пробелы. Функции to_timestamp и to_date игнорируют указание TM.

  • to_timestamp и to_date пропускают повторяющиеся пробелы во входной строке, если только не используется параметр FX. Например, to_timestamp('2000    JUN', 'YYYY MON') будет работать, но to_timestamp('2000    JUN', 'FXYYYY MON') вернёт ошибку, так как to_timestamp в данном случае ожидает только один разделяющий пробел. Приставка FX должна быть первой в шаблоне.

  • to_timestamp и to_date предназначены для обработки входных форматов, для которых недостаточно простого приведения. Эти функции интерпретируют вводимые данные с послаблениями, проверяя только грубые ошибки. Хотя они выдают корректные данные, результат может отличаться от ожидаемого. В частности, входные аргументы этих функций не ограничиваются обычными диапазонами, так что to_date('20096040','YYYYMMDD') выдаёт 2014-01-17, а не ошибку. С приведением такого не происходит.

  • Шаблоны для функций to_char могут содержать обычный текст; он будет выведен в неизменном виде. Чтобы вывести текст принудительно, например, если в нём оказываются поддерживаемые коды, его можно заключить в кавычки. Например, в строке '"Hello Year "YYYY', код YYYY будет заменён номером года, а буква Y в слове Year останется неизменной. В функциях to_date, to_number и to_timestamp при обработке подстроки в кавычках просто пропускаются символы входной строки по числу символов в подстроке, например для "XX" будут пропущены два символа.

  • Если вы хотите получить в результате кавычки, перед ними нужно добавить обратную косую черту, например так: '\"YYYY Month\"'.

  • Если формат года определяется менее, чем 4 цифрами, например, как YYY, и в переданном значении года тоже меньше 4 цифр, год пересчитывается в максимально близкий к году 2020, т. е. 95 воспринимается как 1995.

  • Функции to_timestamp и to_date воспринимают отрицательные значения годов как относящиеся к годам до н. э. Если же указать отрицательное значение и добавить явный признак BC (до н. э.), год будет относиться к н. э. Нулевое значение года воспринимается как 1 год до н. э.

  • В функциях to_timestamp и to_date с преобразованием YYYY связано ограничение, когда обрабатываемый год записывается более чем 4 цифрами. После YYYY необходимо будет добавить нецифровой символ или соответствующий код, иначе год всегда будет восприниматься как 4 цифры. Например, в to_date('200001131', 'YYYYMMDD') (с годом 20000) год будет интерпретирован как состоящий из 4 цифр; чтобы исправить ситуацию, нужно добавить нецифровой разделитель после года, как в to_date('20000-1131', 'YYYY-MMDD'), или код как в to_date('20000Nov31', 'YYYYMonDD').

  • В преобразованиях из строки в тип timestamp или date, поле CC (век) игнорируется, если шаблон включает поля YYY, YYYY или Y,YYY. Когда CC используется с YY или Y, год вычисляется как год данного столетия. Если присутствует только код столетия, без года, подразумевается первый год этого века.

  • Даты по недельному календарю ISO 8601 (отличающиеся от григорианских) можно передать функциям to_timestamp и to_date одним из двух способов:

    • Год, номер недели и дня недели: например, 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.)

  • При преобразовании из текстовой строки в 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 выводит значение полностью и для интервалов выводимое значение может превышать 23.

Коды форматирования числовых значений перечислены в Таблице 9.26.

Таблица 9.26. Коды форматирования чисел

КодОписание
9позиция цифры (может отсутствовать, если цифра незначащая)
0позиция цифры (присутствует всегда, даже если цифра незначащая)
. (точка)десятичная точка
, (запятая)разделитель групп (тысяч)
PRотрицательное значение в угловых скобках
Sзнак, добавляемый к числу (с учётом локали)
Lсимвол денежной единицы (с учётом локали)
Dразделитель целой и дробной части числа (с учётом локали)
Gразделитель групп (с учётом локали)
MIзнак минус в заданной позиции (если число < 0)
PLзнак плюс в заданной позиции (если число > 0)
SGзнак плюс или минус в заданной позиции
RNчисло римскими цифрами (в диапазоне от 1 до 3999)
TH или thокончание порядкового числительного
Vсдвиг на заданное количество цифр (см. замечания)
EEEEэкспоненциальная запись числа

Замечания по использованию форматов чисел:

  • 0 обозначает позицию цифры, которая будет выводиться всегда, даже если это незначащий ноль слева или справа. 9 также обозначает позицию цифры, но если это незначащий ноль слева, он заменяется пробелом, а если справа и задан режим заполнения, он удаляется. (Для функции to_number() эти два символа равнозначны.)

  • Символы шаблона 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'. (В Oracle MI не может идти перед 9, наоборот 9 нужно указать перед MI.)

  • TH не преобразует значения меньше 0 и не поддерживает дробные числа.

  • PL, SG и TH — расширения PostgreSQL.

  • V c to_char умножает вводимое значение на 10^n, где n — число цифр, следующих за V. V с to_number подобным образом делит значение. Функции to_char и to_number не поддерживают V с дробными числами (например, 99.9V99 не допускается).

  • Код EEEE (научная запись) не может сочетаться с любыми другими вариантами форматирования или модификаторами, за исключением цифр и десятичной точки, и должен располагаться в конце строки шаблона (например, 9.99EEEE — допустимый шаблон).

Для изменения поведения кодов к ним могут быть применены определённые модификаторы. Например, FM99.99 обрабатывается как код 99.99 с модификатором FM. Все модификаторы для форматирования чисел перечислены в Таблице 9.27.

Таблица 9.27. Модификаторы шаблонов для форматирования чисел

МодификаторОписаниеПример
Приставка FMрежим заполнения (подавляет завершающие нули и дополнение пробелами)FM99.99
Окончание THокончание порядкового числительного в верхнем регистре999TH
Окончание thокончание порядкового числительного в нижнем регистре999th

В Таблице 9.28 приведены некоторые примеры использования функции to_char.

Таблица 9.28. Примеры 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(-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'