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

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

Функция to_timestamp может иметь также один аргумент; в этом случае она принимает число double precision и преобразует его из времени Unix (число секунд с 1970-01-01 00:00:00+00) в timestamp with time zone (время с часовым поясом). (Значения времени Unix типа Integer просто неявно приводятся к типу double precision.)

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

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

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

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

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

• С преобразованием YYYY из строки в тип timestamp или date связано ограничение, когда обрабатываемый год записывается более чем 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-24.

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

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

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

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

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