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

Функции для форматирования 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'