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

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

Таблица 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 ( числовой_тип, 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) в таблице Функции даты/времени.) to_timestamp('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05 00:00:00-05

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

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

В Таблице 26 показаны имеющиеся коды шаблонов для форматирования значений даты и времени.

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

Модификаторы, изменяющие поведение, можно применять к любому коду шаблона. Например, FMMonth — это код Month с модификатором FM. В Таблице 27 приведены коды модификаторов для форматирования даты/времени.

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

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

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

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

• TM подавляет конечные пробелы независимо от указания FM.

• Функции to_timestamp и to_date игнорируют регистр букв во входных данных; поэтому, например, для кодов MON, Mon и mon подойдут одни и те же строки. При использовании модификатора TM смена регистра производится согласно правилам сортировки, заданным в функции для входных данных (см. раздел Поддержка правил сортировки).

• Функции 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).

  Совет
  Чтобы пропустить во входной строке произвольный текст, можно использовать в шаблоне буквы. Например, шаблоны to_timestamp('2000y6m1d', 'yyyytMMtDDt') и to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"') пропускают буквы y, m и d.

• Если вы хотите получить в выводе кавычку, следует поставить перед ней обратный слэш, например '\"YYYY Month\"'. В остальном символ обратного слэша вне кавычек не имеет специального значения. Внутри строки в кавычках следующий за обратным слэшем символ воспринимается буквально, каким бы он ни был (но это не имеет особого смысла, если следующий символ не является кавычкой или обратным слэшем).

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

• В функциях to_timestamp and 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; в начале года это может привести к неожиданным результатам. (Дополнительную информацию см. в подразделе EXTRACT, date_part.)

• В функции 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 секунд.

• Нумерация дня недели в функции to_char(..., ’ID’) соответствует функции extract(isodow from ...), но в функции to_char(..., ’D’) эта нумерация не соответствует таковой в функции extract(dow from ...).

• Функция to_char(interval) форматирует HH и HH12 в рамках 12 часов, например, ноль и 36 часов выводятся как 12, в то время как HH24 выводит значение часа полностью, так что в значении interval результат может быть выше 23.

В Таблице 28 показаны имеющиеся коды шаблонов для форматирования числовых значений.

Таблица 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 не преобразует значения меньше нуля и дробные числа.

• PL, SG и TH являются расширениями QHB.

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

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

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

Некоторые модификаторы, изменяющие поведение, можно применять к любому коду шаблона. Например, FM99.99 — это код 99.99 с модификатором FM. В Таблице 29 приведены коды модификаторов для форматирования чисел.

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

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

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

Таблица 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'