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

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

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

Функция

Описание

Пример(ы)

to_char ( timestamp, text ) → text

to_char ( timestamp with time zone, text ) → text

Преобразует время в строку согласно заданному формату.

to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')05:31:12

to_char ( interval, text ) → text

Преобразует интервал в строку согласно заданному формату.

to_char(interval '15h 2m 12s', 'HH24:MI:SS')15:02:12

to_char ( numeric_type, text ) → text

Преобразует число в строку согласно заданному формату; поддерживаются типы integer, bigint, numeric, real, double precision.

to_char(125, '999')125

to_char(125.8::real, '999D9')125.8

to_char(-125.8, '999D99S')125.80-

to_date ( text, text ) → date

Преобразует строку в дату согласно заданному формату.

to_date('05 Dec 2000', 'DD Mon YYYY')2000-12-05

to_number ( text, text ) → numeric

Преобразует строку в число согласно заданному формату.

to_number('12,454.8-', '99G999D9S')-12454.8

to_timestamp ( text, text ) → timestamp with time zone

Преобразует строку в значение времени согласно заданному формату. (См. также to_timestamp(double precision) в Таблице 9.32.)

to_timestamp('05 Dec 2000', 'DD Mon YYYY')2000-12-05 00:00:00-05


Подсказка

Функции to_timestamp и to_date предназначены для работы с входными форматами, которые нельзя преобразовать простым приведением. Для большинства стандартных форматов даты/времени работает простое приведение исходной строки к требуемому типу и использовать его гораздо легче. Так же и функцию to_number нет необходимости использовать для стандартных представлений чисел.

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

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

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

КодОписание
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 г. до н. э.)
Qквартал
RMномер месяца римскими цифрами в верхнем регистре (I–XII; I=январь)
rmномер месяца римскими цифрами в нижнем регистре (i–xii; i=январь)
TZсокращённое название часового пояса в верхнем регистре (поддерживается только в to_char)
tzсокращённое название часового пояса в нижнем регистре (поддерживается только в to_char)
TZHчасы часового пояса
TZMминуты часового пояса
OFсмещение часового пояса от UTC (поддерживается только в to_char)

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

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

МодификаторОписаниеПример
Приставка 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 подавляет замыкающие пробелы вне зависимости от указания FM.

  • Функции to_timestamp и to_date игнорируют регистр букв во входной строке; поэтому, например, для шаблонов MON, Mon и mon подойдут одни и те же строки. Если используется приставка TM, смена регистра производится в соответствии с правилом сортировки, установленным для входной строки (см. Раздел 23.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).

    Подсказка

    До PostgreSQL 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('200001131', 'YYYYMMDD') (с годом 20000) год будет интерпретирован как состоящий из 4 цифр; чтобы исправить ситуацию, нужно добавить нецифровой разделитель после года, как в to_date('20000-1131', 'YYYY-MMDD'), или код как в to_date('20000Nov31', '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 and HH12 в рамках 12 часов, то есть 0 и 36 часов будут выводиться как 12, тогда как HH24 выводит число часов полностью, и для значений interval результат может превышать 23.

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

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

КодОписание
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.

  • В to_number при использовании шаблонных кодов, не обозначающих данные, таких как L и TH, пропускается соответствующее количество входных символов. При этом не имеет значения, совпадают ли они с символами шаблона, если только это не символы данных (то есть цифры, знак числа, десятичная точка или запятая). Например, для подстроки TH будут пропущены два символа, не представляющие данные.

  • 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.29.

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

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

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

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