4. Типы данных РЕД Базы Данных

4.1. Перечень типов данных

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

Типы данных, используемые в СУБД РЕД База Данных, в алфавитном порядке представлены в таблице 4.1. Далее в этой главе они рассматриваются более подробно. Перечисленные типы данных могут применяться не только при описании доменов, но также и при описании характеристик столбцов таблиц базы данных, входных и выходных параметров хранимых процедур, внутренних переменных хранимых процедур и триггеров (подробнее об этом см. в главе 10 и в главе 18).

Таблица 4.1 Типы данных, используемые в РЕД Базе Данных

Тип данных

Размер (байт)

Описание

BIGINT

8

Числовой тип данных. Хранит целые числа в диапазоне от \(-2^{63}\) до \(+2^{63}-1\), или от –9,223,372,036,854,775,808 до +9,223,372,036,854,775,807.

BOOLEAN

1

Логический тип данных. Может принимать значения TRUE, FALSE и UNKNOWN.

BINARY(n)

n

Бинарный тип данных фиксированной длины. Является псевдонимом типа CHAR(n) CHARACTER SET OCTETS. Диапазон от 1 до 32 767 байт

BLOB

Переменный

Большой двоичный объект (Binary Large OBject). Позволяет хранить произвольные данные: форматированные тексты, графику, звуки, видеофильмы. Размер сегмента BLOB ограничивается 64К. Максимальный размер поля BLOB 4 Гб. Для размера страницы 4096 максимальный размер BLOB поля несколько ниже 2 Гб.

CHAR(n), CHARACTER(n)

n символов

Символьный тип данных фиксированной длины. Число n задает максимальное количество символов. Конечные пробелы в базе данных не хранятся, а восстанавливаются до указанного размера при отображении такого столбца. Восстановление пробельных символов до максимальной длины происходит на клиенте, а не на сервере, при передаче данных по локальной сети пробелы не передаются, что позволяет уменьшить сетевой трафик. Максимальный размер столбца 32767 байтов. Максимальное количество хранимых символов зависит от используемого для столбца набора символов. Некоторые наборы символов для хранения каждого символа используют более одного байта (см. 33). Если количество символов n не указано, принимается 1. Для этого типа данных допустимо сокращение CHAR.

DATE

4

Даты в диапазоне от 1 января 1 г. до 31 декабря 9999 г.

DECIMAL(n, m)

2, 4, 8 или 16

Числовой тип данных. Число с фиксированной точкой (целое или дробное число), n — общее количество знаков в числе, включая дробные знаки (диапазон значений от 1 до 38), m — количество знаков после десятичной точки (значения от 0 до 38). Основное требование к параметрам: m <= n. Если значения для n и/или m не указаны, то предполагается (9, 0). Для этого типа данных допустимо сокращение DEC.

DECFLOAT (16|34)

8 или 16

SQL:2016 совместимый тип данных точно хранящий десятичные числа с плавающей запятой, основанный на стандарте IEEE 754-2008. Количество значащих цифр (точность) - 16 или 34. Если точность не указана, по умолчанию используется 34.

DOUBLE PRECISION

8

Числовой тип данных. Число с плавающей точкой. Находится в диапазоне от \(2.225\times 10^{-308}\) до \(1.797\times 10^{308}\). Позволяет хранить до 15 значащих цифр.

FLOAT

4

Числовой тип данных. Число с плавающей точкой. Диапазон хранимых чисел от \(1.175\times 10^{-38}\) до \(3.402\times 10^{38}\). Позволяет хранить до 7 значащих цифр.

INTEGER

4

Числовой тип данных. Целое число в диапазоне от \(-2^{31}\) до \(+2^{31} - 1\), или от –2,147,483,648 до +2,147,483,647. Для этого типа данных допустимо сокращение INT.

INT128

16

Числовой тип данных. Целое число в диапазоне от \(-2^{127}\) до \(+2^{127} - 1\)

NATIONAL CHARACTER (n)

n символов

Символьный тип данных фиксированной длины. Отличается от типа данных CHARACTER только тем, что для него предопределен набор символов ISO8859_1. Другие наборы символов для этого типа данных задавать нельзя. Если количество символов n не указано, принимается 1. Допустимы следующие сокращения NATIONAL CHAR, NCHAR.

NATIONAL CHARACTER VARYING (n)

n символов

Символьный тип данных переменной длины. Отличается от типа данных VARYING CHARACTER только тем, что для него предопределен набор символов ISO8859_1. Другие наборы символов для этого типа данных задавать нельзя. Количество символов n обязательно должно быть указано. Допустимы следующие сокращения: NATIONAL CHAR VARYING, NCHAR VARYING.

NUMERIC(n, m)

2, 4, 8 или 16

Числовой тип данных. Дробное или целое число с фиксированной точкой, n — общее количество знаков в числе от 1 до 38, m — количество знаков после десятичной точки (число от 0 до 38). Общее требование: m <= n. Если n и/или m не указаны, то предполагается (9, 0). В диалекте 3 полностью соответствует типу данных DECIMAL. Сокращение названия для этого типа данных не используется.

SMALLINT

2

Целочисленный тип данных. Целое число в диапазоне от \(-2^{15}\) до \(+2^{15} - 1\), или от –32,768 до +32,767.

TIME [WITHOUT TIME ZONE]

4

Задает время в часах, минутах, секундах и десятитысячных долях секунды без учёта часового пояса. Диапазон: от 00:00:00.0000 до 23:59:59.9999.

TIME WITH TIME ZONE

6

Задает время в часах, минутах, секундах и десятитысячных долях секунды с учётом часового пояса. Диапазон: от 00:00:00.0000 до 23:59:59.9999.

TIMESTAMP [WITHOUT TIME ZONE]

8

Комбинация (объединение) даты и времени без учёта часового пояса. Диапазон: 01-01-0001 AD до 31-12-9999 AD.

TIMESTAMP WITH TIME ZONE

10

Комбинация (объединение) даты и времени с учётом часового пояса. Диапазон: 01-01-0001 AD до 31-12-9999 AD.

VARBINARY(n), BINARY VARYING(n)

n

Бинарный тип данных переменной длины. Является псевдонимом типа VARCHAR(n) CHARACTER SET OCTETS. Диапазон от 1 до 32765 байтов.

VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)

n символов

Символьный тип данных переменной длины. Максимальный размер 32765 байтов. Количество хранимых символов зависит от используемого для столбца набора символов (см. 33), где для каждого набора символов указано и количество байтов, необходимых для хранения одного символа).

Если количество символов n не указано, то предполагается 1024.

Типы данных можно объединить в группы, или категории — числовые данные, строковые, данные даты и времени, двоичные данные и логические данные.

Любой тип данных может быть также массивом произвольной размерности.

4.2. SMALLINT

Тип данных SMALLINT представляет собой 16-битное целое.

Числа типа SMALLINT находятся в диапазоне \(-2^{15} .. 2^{15} - 1\), или \(-32 768 .. 32 767\).

Числа SMALLINT не могут быть заданы в шестнадцатеричном виде явно. Но РЕД База Данных будет прозрачно преобразовывать шестнадцатеричное число в SMALLINT, если это необходимо, при условии что оно попадает в допустимый диапазон положительных и отрицательных значений для SMALLINT.

Операции сложения и вычитания для типа данных SMALLINT выполняются обычным образом. Следует только быть особенно внимательными при выполнении операций умножения и деления. В этих операциях результат будет иметь количество дробных знаков, равное сумме дробных знаков обоих операндов. То есть результатом операции деления двух целых чисел:

select cast(1 as SMALLINT)/cast(3 as SMALLINT) from rdb$database;

будет 0, потому что сумма дробных знаков операндов равна нулю, а целая часть в результате выполнения операции деления будет равна нулю. Результат является целочисленным.

Для числовых типов данных могут использоваться агрегатные функции, определенные в SQL — MIN, MAX, SUM, AVG и множество других встроенных в SQL функций. Описание встроенных функций см. 36.

4.3. INTEGER

Тип данных INTEGER представляет собой 32-битное целое. Сокращённый вариант записи типа данных INT. Числа типа INTEGER находятся в диапазоне \(-2^{31} .. 2^{31} - 1\), или \(-2 147 483 648 .. 2 147 483 647\).

Числа типа INTEGER могут быть заданы в шестнадцатеричном виде с 1 — 8 шестнадцатеричными цифрами.

Операции сложения и вычитания для типа данных INTEGER выполняются обычным образом. Следует только быть особенно внимательными при выполнении операций умножения и деления. В этих операциях результат будет иметь количество дробных знаков, равное сумме дробных знаков обоих операндов. То есть результатом операции деления двух целых чисел:

select cast(1 as INTEGER)/cast(3 as INTEGER) from rdb$database;

будет 0, потому что сумма дробных знаков операндов равна нулю, а целая часть в результате выполнения операции деления будет равна нулю. Результат является целочисленным.

Для числовых типов данных могут использоваться агрегатные функции, определенные в SQL — MIN, MAX, SUM, AVG и множество других встроенных в SQL функций. Описание встроенных функций см. в 36.

4.4. BIGINT

Тип данных BIGINT представляет собой 64-битное целое, совместимый со стандартом SQL-99. Он доступен только в 3-м диалекте. При использовании клиентом диалекта 1, передаваемое сервером значение генератора усекается до 32-х битного целого (INTEGER).

Числа типа BIGINT находятся в диапазоне \(-2^{63} .. 2^{63} - 1\), или \(-9 223 372 036 854 775 808 .. 9 223 372 036 854 775 807\).

Числа типа BIGINT могут быть заданы в шестнадцатеричном виде с 9 — 16 шестнадцатеричными цифрами.

Операции сложения и вычитания для типа данных BIGINT выполняются обычным образом. Следует только быть особенно внимательными при выполнении операций умножения и деления. В этих операциях результат будет иметь количество дробных знаков, равное сумме дробных знаков обоих операндов. То есть результатом операции деления двух целых чисел:

select cast(1 as BIGINT)/cast(3 as BIGINT) from rdb$database;

будет 0, потому что сумма дробных знаков операндов равна нулю, а целая часть в результате выполнения операции деления будет равна нулю. Результат является целочисленным.

Для числовых типов данных могут использоваться агрегатные функции, определенные в SQL — MIN, MAX, SUM, AVG и множество других встроенных в SQL функций. Описание встроенных функций см. в 36.

4.5. INT128

Тип данных INT128 представляет собой 128-битное целое. Числа типа INT128 находятся в диапазоне \(-2^{127} .. 2^{127} - 1\).

Числа типа INT128 могут быть заданы в шестнадцатеричном виде с 17 — 32 шестнадцатеричными цифрами.

Операции сложения и вычитания для типа данных INT128 выполняются обычным образом. Следует только быть особенно внимательными при выполнении операций умножения и деления. В этих операциях результат будет иметь количество дробных знаков, равное сумме дробных знаков обоих операндов. То есть результатом операции деления двух целых чисел:

select cast(1 as INT128)/cast(3 as INT128) from rdb$database;

будет 0, потому что сумма дробных знаков операндов равна нулю, а целая часть в результате выполнения операции деления будет равна нулю. Результат является целочисленным.

Для числовых типов данных могут использоваться агрегатные функции, определенные в SQL — MIN, MAX, SUM, AVG и множество других встроенных в SQL функций. Описание встроенных функций см. в 36.

4.6. Шестнадцатеричный формат для целых чисел

Целочисленные значения могут быть записаны в шестнадцатеричной системе счисления. Шестнадцатеричные числовые константы указываются в формате:

<0x><шестнадцатеричное число>

где '0' и 'X' (или 'x') являются литералом, за которым следует набор байт, используя 0-9, a-f или A-F.

Числа состоящие из 1-8 шестнадцатеричных цифр будут интерпретированы как INTEGER, состоящие из 9-16 цифр — как BIGINT, состоящие из 17-32 цифр — как INT128.

SELECT 0x6FAA0D3 FROM rdb$database;             -- 117088467
SELECT 0x4F9 FROM rdb$database;                 -- 1273
SELECT 0x6E44F9A8 FROM rdb$database;            -- 1850014120
SELECT -0x9E44F9A8 FROM rdb$database;           -- -2655320488  (BIGINT)
SELECT 0x09E44F9A8 FROM rdb$database;           -- 2655320488  (BIGINT)
SELECT 0x28ED678A4C987 FROM rdb$database;       -- 720001751632263
SELECT -0xFFFFFFFFFFFFFFFF FROM rdb$database;   -- -18446744073709551615

Шестнадцатеричные числа в диапазоне 0x0..0x7FFF_FFFF являются положительными числами типа INTEGER со значениями 0..2147483647.

Шестнадцатеричные числа в диапазоне 0x8000_0000..0x7FFF_FFFF_FFFF_FFFF являются положительными числами типа BIGINT со значениями 2147483648..9223372036854775807.

Шестнадцатеричные числа в диапазоне от 0x8000_0000_0000_0000 до 0x7FFF_FFFF_FFFF_FFFF_FFFF_FFFF_FFFF_FFFF являются положительными числами типа INT128 со значениями 9223372036854775808..170141183460469231731687303715884105727.

Для представления отрицательных значений нужно поставить знак минус перед беззнаковым шестнадцатиричным литералом.

Числа с типом SMALLINT не могут быть записаны в шестнадцатеричном виде, строго говоря, так как даже 0x1 оценивается как INTEGER. Тем не менее, если вы записываете положительное целое число в пределах 16-разрядного диапазона от 0x0000 (десятичный ноль) до 0x7FFF (десятичное 32767), то оно будет преобразовано в SMALLINT прозрачно.

4.7. Восьмеричный формат для целых чисел

Целочисленные значения могут быть записаны в восьмеричной системе счисления. Восьмеричные числовые константы указываются в формате:

<0o><восьмеричное число>

где '0' и 'O' (или 'o') являются литералами, за которыми следует набор байт, использующих 0-7.

Числа состоящие из 1-11 восьмеричных цифр будут интерпретированы как INTEGER, состоящие из 12-21 цифр — как BIGINT, состоящие из 22-43 цифр — как INT128.

SELECT  0o12345670 FROM rdb$database;             -- 2739128
SELECT -0o12345670 FROM rdb$database;            -- -2739128

Восьмеричные числа в диапазоне 0o0..0o1_7777_777_777 являются положительными INTEGER числами.

Восьмеричные числа в диапазоне 0o20_000_000_000..777_777_777_777_777_777_777 являются положительными BIGINT числами.

Восьмеричные числа в диапазоне от 0o1_000_000_000_000_000_000_000 до 0o1_777_777_777_777_777_777_777_777_777_777_777_777_777_777 являются положительными INT128 числами.

Для представления отрицательных значений нужно поставить знак минус перед беззнаковым восьмеричным литералом.

4.8. Двоичный формат для целых чисел

Целочисленные значения могут быть записаны в двоичной системе счисления. Двоичные числовые константы указываются в формате:

<0b><двоичное число>

где '0' и 'B' (или 'b') являются литералами, за которыми следует набор байт, использующих 0 или 1.

Числа состоящие из 1-31 двоичных цифр будут интерпретированы как INTEGER, состоящие из 32-63 цифр — как BIGINT, состоящие из 64-43 127 — как INT128.

Для представления отрицательных значений нужно поставить знак минус перед беззнаковым двоичным литералом.

SELECT  0b11010100 FROM rdb$database;             -- 212
SELECT -0b11010100 FROM rdb$database;             -- -212

4.9. FLOAT

Тип данных FLOAT представляет собой 32-битное число с точностью 7 цифр после запятой (для обеспечения надёжности хранения полагайтесь на 6 цифр).

Диапазон хранимых чисел от \(1.175\times 10^{-38}\) до \(3.402\times 10^{38}\).

Тип данных FLOAT хранится в двоичном формате IEEE 754, который включает в себя знак, показатель степени и мантиссу.

Точность типа FLOAT является динамической, что соответствует физическому формату хранения, который составляет 4 байта.

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

При проверке данных столбцов рекомендуется вместо точного равенства использовать выражения проверки вхождения в диапазон, например BETWEEN.

Для чисел FLOAT все операции выполняются таким же образом, как принято во всех языках программирования. Следует помнить, что тип данных FLOAT имеет 7 значащих цифр. Если, например, к очень большому числу FLOAT прибавить очень маленькое число (или вычесть из него такое число), то результат не будет отличаться от первого, большего, числа.

В следующем выражении

select cast(cast(1.23E+20 as FLOAT) - cast(1.23E-24 as FLOAT) as float) from rdb$database;

где первый операнд является очень большим по величине числом, а второй — очень маленьким, результатом будет это же первое, большее, число 1.23E+20.

Для числовых типов данных могут использоваться агрегатные функции, определенные в SQL — MIN, MAX, SUM, AVG и множество других встроенных в SQL функций. Описание встроенных функций см. в 36.

4.10. DOUBLE PRECISION

Тип данных DOUBLE PRECISION представляет собой 64-битное число с точностью 15 цифр после запятой.

Диапазон хранимых чисел от \(2.225\times 10^{-308}\) до \(1.797\times 10^{308}\).

Тип данных DOUBLE PRECISION хранится в двоичном формате IEEE 754, который включает в себя знак, показатель степени и мантиссу.

Точность типа DOUBLE PRECISION является динамической, что соответствует физическому формату хранения, который составляет 8 байт.

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

При проверке данных столбцов рекомендуется вместо точного равенства использовать выражения проверки вхождения в диапазон, например BETWEEN.

Для чисел DOUBLE PRECISION все операции выполняются таким же образом, как принято во всех языках программирования. Следует помнить, что тип данных DOUBLE PRECISION имеет 15 значащих цифр. Если, например, к очень большому числу DOUBLE PRECISION прибавить очень маленькое число (или вычесть из него такое число), то результат не будет отличаться от первого, большего, числа.

В следующем выражении

select cast(1.23E+20 as DOUBLE PRECISION) - cast(1.23E-24 as DOUBLE PRECISION) from rdb$database;

где первый операнд является очень большим по величине числом, а второй — очень маленьким, результатом будет это же первое, большее, число 1.23E+20.

Для числовых типов данных могут использоваться агрегатные функции, определенные в SQL — MIN, MAX, SUM, AVG и множество других встроенных в SQL функций. Описание встроенных функций см. в 36.

4.11. DECFLOAT

DECFLOAT является числовым типом из стандарта SQL:2016, который точно хранит числа с плавающей запятой, в отличие от FLOAT или DOUBLE PRECISION, которые обеспечивают двоичное приближение предполагаемой точности. РЕД База Данных в соответствии со стандартом IEEE 754-1985 (IEEE 754-2008) реализует типы DECIMAL64 и DECIMAL128, что обеспечивает точность DECFLOAT 16 и 34 значащих цифр, и занимает 8 и 16 байт памяти соответственно.

Если точность не указана, то по умолчанию используется точность 34 значащих цифры.

Все промежуточные вычисления осуществляются с использованием 34-значными значениями.

Таблица 4.2 Диапазон значений

Тип

Максимальная точность

Минимальный показатель степени

Максимальный показатель степени

Наименьшее значение

Наибольшее значение

DECFLOAT(16)

16

-383

+384

1E-398

9.9..9E+384

DECFLOAT(34)

34

-6143

+6144

1E-6176

9.9..9E+6144

Все математические, агрегатные и статистические агрегатные функции поддерживают работу со значениями типа DECFLOAT. Кроме этого созданы 4 функции специально для поддержки типа DECFLOAT: COMPARE_DECFLOAT, NORMALIZE_DECFLOAT, QUANTIZE, TOTALORDER.

Режимы округления

Округление происходит, когда точность результата выражения больше точности, поддерживаемой типом данных. DECFLOAT поддерживает все режимы округления, которые требуются в большинстве приложений. Поддерживаются следующие режимы округления совместимые со стандартом IEEE:

Таблица 4.3 Режим округления

Режим округления

Описание

CEILING

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

UP

Округление по направлению от нуля (усечение с приращением). Отбрасываемые значения игнорируются.

HALF_UP

Округление к ближайшему значению. Если результат равноудаленный, выполняется округление в большую сторону. Если отбрасываемые значения больше чем или равны половине (0,5) единицы в следующей левой позиции, последняя неотбрасываемая цифра инкрементируется на единицу (округляется в большую сторону). В противном случае отбрасываемые значения игнорируются. Этот режим выбран по умолчанию.

HALF_EVEN

Округление к ближайшему значению. Если результат равноудаленный, выполняется округление так, чтобы последняя цифра была четной. Если отбрасываемые значения больше половины (0,5) единицы в следующей левой позиции, последняя неотбрасываемая цифра инкрементируется на единицу (округляется в большую сторону). Если они меньше половины, результат не корректируется (то есть отбрасываемые знаки игнорируются). В противном случае, когда отбрасываемые значения точно равны половине, последняя неотбрасываемая цифра не меняется, если она является четной и инкрементируется на единицу (округляется в большую сторону) в противном случае (чтобы получить четную цифру). Этот режим округления называется также банковским округлением и дает ощущение справедливого округления.

HALF_DOWN

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

DOWN

Округление по направлению к нулю (усечение). Отбрасываемые значения игнорируются.

FLOOR

Округление снизу. Если все отбрасываемые цифры равны нулю или знак положителен, последняя неотбрасываемая цифра не меняется. В противном случае (знак отрицателен) последняя неотбрасываемая цифра инкрементируется на единицу.

REROUND

Округление к большему значению, если округляется 0 или 5, в противном случае округление происходит к меньшему значению.

Режим округления можно изменить для текущей сессии используя оператор SET DECFLOAT ROUND:

SET DECFLOAT ROUND <режим>

По умолчанию используется режим округления HALF_UP.

Таблица 4.4 Пример округления в разных режимах

Режим округления

12.341

12.345

12.349

12.355

12.405

-12.345

CEILING

12.35

12.35

12.35

12.36

12.41

-12.34

UP

12.35

12.35

12.35

12.36

12.41

-12.35

HALF_UP

12.34

12.35

12.35

12.36

12.41

-12.35

HALF_EVEN

12.34

12.34

12.35

12.36

12.40

-12.34

HALF_DOWN

12.34

12.34

12.35

12.35

12.40

-12.34

DOWN

12.34

12.34

12.34

12.35

12.40

-12.34

FLOOR

12.34

12.34

12.34

12.35

12.40

-12.35

REROUND

12.34

12.34

12.34

12.36

12.41

-12.34

Семантика сравнения

Замыкающие нули в значениях десятичных чисел с плавающей запятой сохраняются. Например, \(1.0\) и \(1.00\) — это два различных представления. Это порождает различные семантики сравнения для типа данных DECFLOAT:

  • Сравнение числовых значений

    Замыкающие нули игнорируются в сравнениях. Например, \(1.0\) равно \(1.00\). По умолчанию такой тип сравнения используется для индексирования, сортировки, разбивки таблицы, оценки предикатов и других функций — короче говоря, везде, где сравнение выполняется неявно или в предикатах.

    create table stockPrice (stock DECFLOAT(16));
insert into stockPrice values (4.2);
insert into stockPrice values (4.2000);
insert into stockPrice values (4.6125);
insert into stockPrice values (4.20);

select * from stockPrice where stock = 4.2;
-------------------------------------------
4.2, 4.2000, 4.20

select * from stockPrice where stock > 4.20;
-------------------------------------------
4.6125

select * from stockPrice order by stock;
-------------------------------------------
4.2, 4.20, 4.2000, 4.6125

    Порядок, в котором возвращаются арифметически одинаковые значения, имеющие различное количество замыкающих нулей, не определен. Следовательно, ORDER BY по столбцу DECFLOAT со значениями \(1.0\) и \(1.00\) возвращает два значения в произвольном порядке. Аналогично, DISTINCT возвращает либо \(1.0\), либо \(1.00\).

  • Сравнение TotalOrder

    Замыкающие нули учитываются при сравнении. Например, \(1.0 > 1.00\). Каждое значение DECFLOAT имеет порядок в семантике сравнения TotalOrder.

    Согласно семантике TotalOrder, порядок различных значений определяется так, как показано в следующем примере:

    -nan < -snan < -inf < -0.1 < -0.10 < -0 < 0 < 0.10 < 0.1 < inf < snan < nan

    Запросить сравнение TotalOrder в предикатах можно при помощи встроенной функции TOTALORDER().

    create table stockPrice (stock DECFLOAT(16));
insert into stockPrice values (4.2);
insert into stockPrice values (4.2000);
insert into stockPrice values (4.6125);
insert into stockPrice values (4.20);

select * from stockPrice where TOTALORDER(stock, 4.2000) = 0;
-------------------------------------------
4.2000

select * from stockPrice where TOTALORDER(stock, 4.20) = 1;
-------------------------------------------
4.2, 4.6125

Обработка ошибок

В процессе вычисления выражений могут возникнуть различные ситуации, которые могут вызвать исключение или проигнорированы. Установить какие ситуации приведут к возбуждению исключения можно с помощью оператора SET DECFLOAT TRAPS TO.

SET DECFLOAT TRAPS TO <ситуация>[, <ситуация>...]
<ситуация> ::= Division_by_zero
             | Inexact
             | Invalid_operation
             | Overflow
             | Underflow

По умолчанию исключения генерируется для следующих ситуаций: Division_by_zero, Invalid_operation, Overflow.

Примечание

Данный SQL оператор работает вне механизма управления транзакциями, изменения выполненные им вступаю в силу немедленно. Его использование разрешено в том числе и PSQL коде.

Поддержка в клиентских приложениях

Библиотека fbclient версии 4.0 имеет нативную поддержку типа DECFLOAT, в отличии от старых версий клиентской библиотеки. Для того чтобы старые приложения умели работать с типом DECFLOAT можно настроить отображение значений DECFLOAT на другие доступные типы данных.

SET DECFLOAT BIND <тип для привязки>
<тип для привязки> ::= NATIVE
                     | CHAR[ACTER]
                     | DOUBLE PRECISION
                     | BIGINT[, <точность>]

Допустимые типы для привязки:

  • NATIVE — используется IEEE754 двоичное представление. Идеальная поддержка для дальнейшей обработки, но с плохой точностью

  • CHAR[ACTER] — ASCII строка. Идеальная точность, но плохая поддержка для дальнейшей обработки.

  • DOUBLE PRECISION — используется 8 байтное представление с плавающей точкой, тоже самое что и для полей типа DOUBLE PRECISION. Идеальная поддержка для дальнейшей обработки, но с плохой точностью.

  • BIGINT — используется целое с указанным масштабом, тоже самое что поле NUMERIC(18, <точность>). Хорошая поддержка дальнейшей обработки и требуемая точность, но диапазон значений очень ограничен.

Примечание

Привязка к ASCII строке будет иметь тип CHAR(23) для DECFLOAT(16) и CHAR(42) для DECFLOAT(34).

Примечание

Строковое представление зависит от значения DECFLOAT: если оно является экспоненциальным, а требования к точности позволяют отображать значение без использования научной записи, используется полностью выписанный формат.

Примечание

Данный SQL оператор работает вне механизма управления транзакциями, изменения выполненные им вступаю в силу немедленно. Его использование разрешено в том числе и PSQL коде.

Литералы констант DECFLOAT

Длина литералов типа DECFLOAT ограничена 1024 символами. Для более длинных значений вам придётся использовать научную нотацию. Например значение 0.0<1020 нулей>11 не может быть записано как литерал, вместо него вы можете использовать аналогичную научную нотацию: 1.1E-1022. Аналогично 10<1022 нулей>0 может быть записано как 1.0E1024.

4.12. NUMERIC

NUMERIC – тип данных с фиксированной точкой, которое ограничивает хранимое число объявленной точностью (количеством чисел после запятой). Формат объявления данных:

NUMERIC(<точность>, <масштаб>)

1 <= <точность> <= 38
0 <= <масштаб> <= 38

Точность указывает, по меньшей мере, количество цифр для хранения. Масштаб задаёт количество знаков после разделителя.

Масштаб должен быть меньше или равен точности.

Например, NUMERIC(4, 2) описывает число, состоящее в общей сложности из четырёх цифр, включая 2 цифры после запятой; итого 2 цифры до запятой, 2 после. При записи в столбец с этим типом данных значений 3,1415 в столбце NUMERIC(4, 2) будет сохранено значение 3,14.

При сохранении в базу данных число типа NUMERIC умножается на \(10^{<масштаб>}\), превращаясь в целое. При чтении данных происходит обратное преобразование числа.

Способ физического хранения данных в СУБД зависит от нескольких факторов: декларируемой точности, диалекта базы данных.

Таблица 4.5 Способ физического хранения чисел NUMERIC

Точность

Диалект 1

Диалект 3

1 - 4

SMALLINT

SMALLINT

5 - 9

INTEGER

INTEGER

10 - 18

DOUBLE PRECISION

BIGINT

19 - 38

INT128

INT128

Предупреждение

Всегда надо помнить, что формат хранения данных зависит от точности. Например, вы задали тип столбца NUMERIC(2,2), предполагая, что диапазон значений в нем будет -0.99...0.99. Однако в действительности диапазон значений в столбце будет -327.68..327.67, что объясняется хранением типа данных NUMERIC(2,2) в формате SMALLINT. Фактически типы данных NUMERIC(4,2), NUMERIC(3,2) и NUMERIC(2,2) являются одинаковыми. Т.е. для реального хранения данных в столбце с типом данных NUMERIC(2,2) в диапазоне -0.99...0.99 для него надо создавать ограничение.

Операции сложения и вычитания для типа данных NUMERIC выполняются обычным образом. Следует только быть особенно внимательными при выполнении операций умножения и деления. В этих операциях результат будет иметь количество дробных знаков, равное сумме дробных знаков обоих операндов. То есть результатом операции деления двух чисел:

1.00 / 3

будет 0.33. Тот же результат можно получить, если записать операцию деления в следующем виде:

1.0 / 3.0

Для получения числа с нужной точностью можно также выполнить явное преобразование с использованием функции CAST одного или обоих операндов. Например:

CAST(1 AS NUMERIC(3,2)) / 3

Результатом будет то же число 0.33.

Для числовых типов данных могут использоваться агрегатные функции, определенные в SQL — MIN, MAX, SUM, AVG и множество других встроенных в SQL функций. Описание встроенных функций см. в 36.

4.13. DECIMAL

DECIMAL – тип данных с фиксированной точкой, которое ограничивает хранимое число объявленной точностью (количеством чисел после запятой). Формат объявления данных:

DECIMAL(<точность>, <масштаб>)

1 <= <точность> <= 38
0 <= <масштаб> <= 38

Точность указывает, по меньшей мере, количество цифр для хранения. Масштаб задаёт количество знаков после разделителя.

Масштаб должен быть меньше или равен точности.

Например, DECIMAL(4, 2) описывает число, состоящее в общей сложности из четырёх цифр, включая 2 цифры после запятой; итого 2 цифры до запятой, 2 после. При записи в столбец с этим типом данных значений 3,1415 в столбце DECIMAL(4, 2) будет сохранено значение 3,14.

При сохранении в базу данных число типа DECIMAL умножается на \(10^{<масштаб>}\), превращаясь в целое. При чтении данных происходит обратное преобразование числа.

Способ физического хранения данных в СУБД зависит от нескольких факторов: декларируемой точности, диалекта базы данных.

Таблица 4.6 Способ физического хранения чисел DECIMAL

Точность

Диалект 1

Диалект 3

1 - 4

INTEGER

INTEGER

5 - 9

INTEGER

INTEGER

10 - 18

DOUBLE PRECISION

BIGINT

19 - 38

INT128

INT128

Операции сложения и вычитания для типа данных DECIMAL выполняются обычным образом. Следует только быть особенно внимательными при выполнении операций умножения и деления. В этих операциях результат будет иметь количество дробных знаков, равное сумме дробных знаков обоих операндов. То есть результатом операции деления двух чисел

1.00 / 3

будет 0.33. Тот же результат можно получить, если записать операцию деления в следующем виде:

1.0 / 3.0

Для получения числа с нужной точностью можно также выполнить явное преобразование с использованием функции CAST одного или обоих операндов. Например:

CAST(1 AS DECIMAL(3,2)) / 3

Результатом будет то же число 0.33.

Для числовых типов данных могут использоваться агрегатные функции, определенные в SQL — MIN, MAX, SUM, AVG и множество других встроенных в SQL функций. Описание встроенных функций см. в 36.

4.14. DATE

В 3-м диалекте тип данных DATE хранит только одну дату без времени. В 1-м диалекте тип DATE эквивалентен типу TIMESTAMP и хранит дату вместе со временем.

Допустимый диапазон хранения от 1 января 1 г. н.э. до 31 декабря 9999 года.

Примечание

В случае необходимости сохранять в 1 диалекте только значения даты, без времени, при записи в таблицу добавляйте время к значению даты в виде литерала 00:00:00.0000.

Примечание

В диалекте 1 тип DATE может быть объявлен как TIMESTAMP. Такое объявление является рекомендуемым для новых баз данных в 1-м диалекте.

С типом данных даты возможны арифметические операции сложения и вычитания:

Таблица 4.7 Арифметические операции с типом данных даты

Операнд 1

Оператор

Операнд 2

Результат

DATE

+

TIME

TIMESTAMP

DATE

+

n

DATE, увеличенная на n целых дней (дробная часть игнорируется)

DATE

-

DATE

Количество дней в интервале DECIMAL (9,0)

DATE

-

n

DATE, уменьшенная на n целых дней (дробная часть игнорируется)

В SQL РЕД Базы Данных существует три предварительно определенных литерала (TODAY, TOMORROW, YESTERDAY) и контекстная переменная CURRENT_DATE типа DATE. Подробнее о них можно найти в 37.

Для работы с типом данных DATE могут использоваться встроенные функции:

  • DATEADD позволяет добавить заданное число лет, месяцев, недель, часов, минут, секунд, миллисекунд к заданному значению даты.

  • DATEDIFF возвращает количество лет, месяцев, недель, дней, часов, минут, секунд или миллисекунд между двумя значениями даты.

  • EXTRACT извлекает составляющие даты из типа данных DATE.

  • FIRST_DAY возвращает первый день года, месяца или недели для заданной даты.

  • LAST_DAY возвращает последний день года, месяца или недели для заданной даты.

Подробное описание функций см. 36.

4.15. TIME

Этот тип данных доступен только в 3-м диалекте. Позволяет хранить время дня в диапазоне от 00:00:00.0000 до 23:59:59.9999. По умолчанию тип TIME не содержит информацию о часовом поясе. Для того чтобы тип TIME включал информацию о часовом поясе необходимо использовать его с модификатором WITH TIME ZONE.

TIME [{WITH | WITHOUT} TIME ZONE]

При преобразовании из/в TIME WITH TIME ZONE учитывайте, что тип TIME WITHOUT TIME ZONE определен для использования в часовом поясе сеанса.

Часовой пояс сеанса как следует из названия может быть разным для каждого соединения с базой данных. Он может быть установлен с помощью DPB isc_dpb_session_time_zone, а если нет, то он будет считан из параметра DefaultTimeZone конфигурации firebird.conf. Если параметр DefaultTimeZone не установлен, то часовой пояс сеанса будет тем же, что используется операционной системой в которой запущен процесс СУБД.

Часовой пояс сеанса может быть изменён с помощью оператора SET TIME ZONE или сброшен в исходное значение с помощью SET TIME ZONE LOCAL.

Часовой пояс может быть задан строкой с регионом часового пояса (например, America/ Sao_Paulo), или в виде смещения часов:минут относительно GMT (например, -03:00). Список региональных часовых поясов и их идентификаторов см. в 39. Правила преобразования региональных часовых поясов в смещение в минутах можно получить с помощью процедуры RDB$TIME_ZONE_UTIL.TRANSITIONS.

TIME WITH TIMEZONE считается равным другому TIME WITH TIMEZONE, если их преобразование в UTC равно, например time '10:00 -02' = time '09:00 -03' , поскольку оба времени эквивалентны time '12:00 GMT'. Это также справедливо в контексте ограничения UNIQUE и для сортировки.

TIME WITH TIME ZONE хранится так же как TIME WITHOUT TIME ZONE плюс 2 байта для идентификации часового пояса или смещения. TIME часть хранится в UTC (и переводится в сохранённый часовой пояс).

С типом данных времени возможны арифметические операции сложения и вычитания:

Таблица 4.8 Арифметические операции с типом данных времени

Операнд 1

Оператор

Операнд 2

Результат

TIME

+

DATE

TIMESTAMP

TIME

+

n

TIME, увеличенная на n секунд (дробная часть учитывается)

TIME

-

TIME

Количество секунд в интервале DECIMAL (9,4)

TIME

-

n

TIME, уменьшенная на n секунд (дробная часть учитывается)

В SQL РЕД Базы Данных существует контекстная переменная CURRENT_TIME типа TIME WITH TIME ZONE и контекстная переменная LOCALTIME типа TIME WITHOUT TIME ZONE. Подробнее о них можно найти в 37.

Для работы с типом данных TIME могут использоваться встроенные функции:

  • AT преобразует время в указанный часовой пояс.

  • DATEADD позволяет добавить заданное число часов, минут, секунд, миллисекунд к заданному значению времени.

  • DATEDIFF возвращает количество часов, минут, секунд или миллисекунд между двумя значениями времени.

  • EXTRACT извлекает составляющие времени из типа данных TIME.

Подробное описание функций см. в 36.

4.16. TIMESTAMP

Этот тип данных хранит временную метку (дату вместе со временем) в диапазоне от 01.01.0001 00:00:00.0000 до 31.12.9999 23:59:59.9999. По умолчанию тип TIMESTAMP не содержит информацию о часовом поясе. Для того чтобы тип TIMESTAMP включал информацию о часовом поясе необходимо использовать его с модификатором WITH TIME ZONE.

TIMESTAMP [{WITH | WITHOUT} TIME ZONE]

При преобразовании из/в TIMESTAMP WITH TIME ZONE учитывайте что тип TIMESTAMP WITHOUT TIME ZONE определен для использовании в часовом поясе сеанса (о часовом поясе сеанса см. раздел 4.15).

TIMESTAMP WITH TIMEZONE считается равным другому TIMESTAMP WITH TIMEZONE, если их преобразование в UTC равно, например time 10:00 -02 = time 09:00 -03 , поскольку оба времени эквивалентны time 12:00 GMT . Это также справедливо в контексте ограничения UNIQUE и для сортировки.

TIMESTAMP WITH TIME ZONE хранится так же как TIMESTAMP WITHOUT TIME ZONE плюс 2 байта для идентификации часового пояса или смещения. TIMESTAMP часть хранится в UTC (и переводится в сохранённый часовой пояс).

С типом данных TIMESTAMP возможны арифметические операции сложения и вычитания:

Таблица 4.9 Арифметические операции с типом данных TIMESTAMP

Операнд 1

Оператор

Операнд 2

Результат

TIMESTAMP

+

n

TIMESTAMP, где дни увеличены на целую часть числа n, плюс дробная часть числа n (если указана) как количество секунд в дне (с точностью до десятитысячных долей секунды).

TIMESTAMP

-

TIMESTAMP

Количество дней и части дня в интервале DECIMAL(18, 9)

TIMESTAMP

-

n

TIMESTAMP, где дни уменьшены на целую часть числа n, плюс дробная часть числа n (если указана) как количество секунд в дне (с точностью до десятитысячных долей секунды).

В SQL РЕД Базы Данных существует предварительно определенный литерал NOW, контекстная переменная CURRENT_TIMESTAMP типа TIMESTAMP WITH TIME ZONE и контекстная переменная LOCALTIMESTAMP типа TIMESTAMP WITHOUT TIME ZONE. Подробнее о них можно найти в 37.

Для работы с типом данных DATE могут использоваться встроенные функции:

  • AT преобразует временную метку в указанный часовой пояс.

  • DATEADD позволяет добавить заданное число лет, месяцев, недель, часов, минут, секунд, миллисекунд к заданному значению даты/времени.

  • DATEDIFF возвращает количество лет, месяцев, недель, дней, часов, минут, секунд или миллисекунд между двумя значениями даты/времени.

  • EXTRACT извлекает составляющие даты и времени из типа данных TIMESTAMP.

  • FIRST_DAY возвращает первый день года, месяца или недели для заданной даты.

  • LAST_DAY возвращает последний день года, месяца или недели для заданной даты.

Подробное описание функций см. в 36.

4.17. CHAR

CHAR является строковым типом данных фиксированной длины. Если введённое количество символом меньше объявленной длины, то поле дополнится концевыми пробелами. В общем случае символ заполнитель может и не являться пробелом, он зависит от набора символов, так например, для набора символов OCTETS — это ноль.

Полное название типа данных CHARACTER, но при работе нет необходимости использовать полные наименования; инструменты по работе с базой прекрасно понимают и короткие имена символьных типов данных.

CHAR [(<длина>)] [CHARACTER SET <набор символов>] [COLLATE <сортировка>]

В случае если не указана длина, то считается, что она равна единице.

Данный тип символьных данных можно использовать для хранения в справочниках кодов, длина которых стандартна и определённой «ширины». Примером такого может служить почтовый индекс в России – 6 символов.

4.18. VARCHAR

Является базовым строковым типом для хранения текстов переменной длины, поэтому реальный размер хранимой структуры равен фактическому размеру данных плюс 2 байта, в которых задана длина поля.

Если длина не указана, то считается, что она равна 1024.

Все символы, которые передаются с клиентского приложения в базу данных, считаются как значимые, включая начальные и конечные пробельные символы.

Полное название CHARACTER VARYING. Имеется и сокращённый вариант записи CHAR VARYING.

VARCHAR [(<длина>)] [CHARACTER SET <набор символов>] [COLLATE <сортировка>]

4.19. NCHAR

Представляет собой символьный тип данных фиксированной длины с предопределённым набором символов ISO8859_1.

NCHAR [(<длина>)]

Синонимом является написание NATIONAL CHAR.

Аналогичный тип данных доступен для строкового типа переменной длины: NATIONAL CHARACTER VARYING.

4.20. Операции и функции для строковых данных

Для строковых типов данных определена операция конкатенации — соединения двух строк в одну. Для обозначения этой операции применяются два подряд идущих символа вертикальной черты ||. Это простая операция, ее результатом является строка, которая представляет собой соединение двух операндов, двух строк. Операция всегда возвращает тип данных CHAR (а не VARCHAR), независимо от того, какой именно строковый тип данных имеют исходные строки. Это означает, что в результате конкатенации сохраняются конечные пробелы. Размер (количество символов) результирующей строки равен сумме размеров исходных строк конкатенации. Например, можно записать следующую операцию:

'Руководство ' || 'по SQL'

Результатом будет одна строка: "Руководство по SQL".

Для строковых типов данных применимо множество встроенных функций SQL:

  • ASCII_CHAR возвращает ASCII символ соответствующий номеру, переданному в качестве аргумента.

  • ASCII_VAL возвращает ASCII код символа, переданного в качестве аргумента.

  • BIT_LENGTH возвращает длину входной строки в битах.

  • CHARACTER_LENGTH возвращает длину (в символах) строки.

  • HASH возвращает хэш-значение входной строки.

  • LEFT возвращает левую часть строки.

  • LOWER возвращает строку в нижнем регистре.

  • LPAD добавляет к строке слева указанную подстроку.

  • OCTET_LENGTH возвращает количество байт занимаемое строкой.

  • OVERLAY заменяет указанное количество символов на заданное значение.

  • POSITION отыскивает позицию подстроки в строке.

  • REPLACE отыскивает в строке заданную подстроку и заменяет ее на другую.

  • REVERSE переписывает символы строки в обратном порядке.

  • RIGHT возвращает конечную (правую) часть входной строки.

  • RPAD добавляет к строке справа указанную подстроку.

  • SUBSTRING возвращает подстроку входной строки.

  • TRIM удаляет начальные и /или концевые пробелы (или текст согласно настройкам) из входной строки.

  • UPPER возвращает входную строку в верхнем регистре.

Подробное описание функций см. 36.

Поскольку для строк определена операция сравнения, к строковым данным могут также применяться и агрегатные функции MIN и MAX. Сравнение строковых данных осуществляется в соответствии с используемым для столбца набором символов (CHARACTER SET) и порядком сортировки (COLLATION ORDER).

4.21. Строковые литералы

Строковые литералы могут содержать произвольные символы, допустимые для применяемого набора символов. Весь литерал заключается в апострофы. Апостроф внутри символьного литерала должен повторяться два раза, чтобы отличить его от признака завершения литерала. Максимальная длина строкового литерала составляет 65535 байт.

Примечание

Необходимо быть осторожным с длиной строки, если значение должно быть записано в столбец типа VARCHAR. Максимальная длина строки для типа VARCHAR составляет 32765 байт (32767 для типа CHAR). Если значение должно быть записано в столбец типа BLOB, то максимальная длина строкового литерала составляет 65535 байт.

Пример строковой константы:

'Mrs. Hunt''s husband'

Вместо двойного (экранированного) апострофа можно использовать другой символ или пару символов.

Ключевое слово q или Q предшествующее строке в кавычках сообщает парсеру, что некоторые левые и правые пары одинаковых символов являются разделителями для встроенного строкового литерала.

<альтернативный строковый литерал> ::=
       { q | Q } '<левый разделитель> [ { <символ> }... ] <правый разделитель>'

Когда <левый разделитель> является одним из символов '(', '{', '['' или '<'', то <правый разделитель> должен быть использован в паре с соответствующим "партнёром" , а именно ')', '}', ']' или '>'. В других случаях <левый разделитель> совпадает с <правый разделитель>.

Внутри строки, т.е. <символ>–элементах, одиночные (не экранированные) кавычки могут быть использованы. Каждая кавычка будет частью результирующей строки.

SELECT Q'{abc{def}ghi}' FROM rdb$database;          -- abc{def}ghi
SELECT q'!That's a string!' FROM rdb$database;      -- That's a string

4.22. Наборы символов

Полный список доступных наборов символов и нестандартных порядков сортировки доступен в 33.

Набор символов и порядок сортировки задаются:

  • при создании базы данных

  • при описании текстового объекта базы данных

  • при соединении клиентской программы к базе данных

В случае отсутствия явного указания набора символов при описании текстового объекта базы данных будет использоваться набор символов по умолчанию, заданный при создании базы данных. При отсутствии явного указания набора символов, а также отсутствия набора символов по умолчанию в базе данных, поле получает набор символов CHARACTER SET NONE.

В случае различия набора символов клиентского соединения и текстового поля базы данных, при выдаче результата для строковых столбцов происходит автоматическая перекодировка как при передаче данных с клиента на сервер, так и в обратном направлении с сервера на клиента. То есть, совершенно нормальной является ситуация, когда база создана в кодировке WIN1251, а в настройках клиента в параметрах соединения стоит KOI8R или UTF8.

UNICODE

При возникновении необходимости использования восточноевропейских текстов в строковых полях базы данных или для более экзотических алфавитов, рекомендуется работать с набором символов UTF8. При этом следует иметь в виду, что на один символ в данном наборе приходится до 4 байт. Следовательно, максимальный размер символов в символьных полях составит 32676/4 (8192) байта на символ. При этом следует обратить внимание, что фактически значение параметра «байт на символ» зависит от диапазона, к которому принадлежит символ: английские буквы занимают 1 байт, русские буквы кодировки WIN1251 — 2 байта, остальные символы — могут занимать до 4-х байт.

Набор символов UTF8 поддерживает последнюю версию стандарта Unicode, до 4 байт на символ, поэтому для интернациональных баз рекомендуется использовать именно эту реализацию поддержки Unicode в Firebird.

Если база данных будет содержать строки только с русским алфавитом, то для неё рекомендуется к использованию кодировка WIN1251. При её использовании на один символ расходуется 1 байт, соответственно максимальный размер текстовых полей для данной кодировки будет 32767 символов. Для стандартных операций сортировки при работе с WIN1251 не требуется задавать порядок сортировки (COLLATE).

Набор символов NONE и OCTETS

Набор символов NONE относится к специальным наборам символов. Его можно охарактеризовать тем, что каждый байт является частью строки, но в системе хранится без указаний, к какому фактическому набору символов они относятся. Разбираться с такими данными должно клиентское приложение, на него возлагается ответственность в правильной трактовке символов из таких полей.

Также к специальным наборам символов относится OCTETS. В этом случае данные рассматриваются как байты, которые могут в принципе не интерпретироваться как символы. OCTETS позволяет хранить бинарные данные и/или результаты работы некоторых функций Firebird. Правильное отображение данных пользователю, хранящихся в полях с CHARACTER SET OCTETS, также становится заботой клиентской стороны. При работе с подобными данными следует также помнить, что СУБД не контролирует их содержимое и возможно возникновение исключения при работе кода, когда идёт попытка отображения бинарных данных в желаемой кодировке.

Порядок сортировки

Каждый текстовый набор символов (CHARACTER SET) имеет последовательность сортировки (COLLATE) по умолчанию, задающий порядок сортировки и способы сравнения. Если необходимо нестандартное поведение строк при указанных выше действиях, то в описании строкового столбца может быть указан параметр COLLATE, который его опишет. Помимо описания объявления столбца, выражение COLLATE может быть добавлено в предложениях SELECT в секции WHERE, когда происходят операции сравнения больше — меньше, в секции ORDER BY при сортировке по символьному полю, а также при операциях группировки для указания специальной последовательности сортировки при выводе в предложении GROUP BY.

Регистронезависимый поиск

Для регистронезависимого поиска можно воспользоваться функцией UPPER:

WHERE UPPER(name) = UPPER(:flt_name)

Для строк с набором символов WIN1251 можно для этих же целей воспользоваться предложением COLLATE PXW_CYRL.

WHERE FIRST_NAME COLLATE PXW_CYRL >= :FLT_NAME

ORDER BY NAME COLLATE PXW_CYRL

Порядок сортировки для UTF-8

Ниже приведена таблица возможных последовательностей сортировки для набора символов UTF8.

Таблица 4.10 Последовательности сортировки для UTF8

COLLATION

Комментарии

UCS_BASIC

Сортировка работает в соответствии с положением символа в таблице (бинарная). Пример: A, B, a, b, a...

UNICODE

Сортировка работает в соответствии с алгоритмом UCA (Unicode Collation Algorithm) (алфавитная). Пример: a, A, a, b, B...

UTF-8

Сортировка происходит без учёта регистра символа.

UNICODE_CI_AI

Сортировка происходит без учёта регистра символа, в алфавитном порядке.

Индексирование символьных типов

При построении индекса по строковым полям необходимо учитывать ограничение на длину ключа индекса. Максимальная используемая длина ключа индекса равна 1/4 размера страницы, т.е. от 1024 до 4096 байтов. Максимальная длина индексируемой строки на 9 байтов меньше, чем максимальная длина ключа. В таблице приведены данные для максимальной длины индексируемой строки (в символах) в зависимости от размера страницы и набора символов, её можно вычислить по следующей формуле:

max_char_length = FLOOR((page_size / 4 – 9) / N)

где N — число байтов на представление символа.

Размер страницы

Максимальная длина индексируемой строки для набора символов, байт/символ

1

2

3

4

6

4096

1015

507

338

253

169

8192

2039

1019

679

509

339

16384

4087

2043

1362

1021

682

32768

9183

4087

2721

2039

1356

Примечание

В кодировках, нечувствительных к регистру ("_CI"), один символ в *индексе* будет занимать не 4, а 6 байт

Последовательность сортировки (COLLATE) тоже может повлиять на максимальную длину индексируемой строки.

4.23. BOOLEAN

РЕД База Данных предоставляет стандартный SQL тип BOOLEAN. Значений у этого типа может быть несколько: TRUE (истина), FALSE (ложь) и третье состояние UNKNOWN (неизвестно) представляется SQL значением NULL. Спецификация не делает различия между значением NULL этого типа и значением истинности UNKNOWN, которое является результатом SQL предиката, поискового условия или выражения логического типа. Эти значения взаимозаменяемы и обозначают одно и то же.

Значения типа BOOLEAN могут быть проверены в неявных значениях истинности. Например, field1 OR field2 или NOT field1 являются допустимыми выражениями.

Предикаты могут использовать оператор IS [NOT] для проверки соответствия. Например, field1 IS FALSE или field1 IS NOT TRUE.

Операторы эквивалентности (=, !=, <> и др.) допустимы во всех сравнениях. Значение TRUE больше чем FALSE.

Тип данных BOOLEAN не преобразуется неявно ни к одному типу, но возможно явное преобразование к строке с помощью функции CAST.

Пример

Пример использования типа BOOLEAN:

create table CHECKBOOL (ID integer, BOOLVAL boolean);
insert into CHECKBOOL values (1, 1 != 4);
insert into CHECKBOOL values (2, FALSE);
insert into CHECKBOOL values (3, NULL - 1);

SELECT * FROM CHECKBOOL;

ID BOOLVAL
============ =======
1 <true>
2 <false>
3 <null>

SELECT * FROM CHECKBOOL WHERE BOOLVAL = TRUE;

ID BOOLVAL
============ =======
1 <true>

SELECT * FROM CHECKBOOL WHERE NOT BOOLVAL;

ID BOOLVAL
============ =======
2 <false>

SELECT * FROM CHECKBOOL WHERE BOOLVAL IS UNKNOWN;

ID BOOLVAL
============ =======
3 <null>

4.24. BINARY

BINARY является типом данных с фиксированной длиной для хранения бинарных данных. Если переданное количество байт меньше объявленной длины, то значение будет дополнено нулями. В случае если не указана длина, то считается, что она равна единице.

BINARY [(<длина>)]

Этот тип является псевдонимом типа CHAR [(<длина>)] CHARACTER SET OCTETS и обратно совместим с ним.

Данный тип хорошо подходит для хранения уникального идентификатора полученного с помощью функции GEN_UUID.

4.25. VARBINARY

VARBINARY является типом для хранения бинарных данных переменной длины. Реальный размер хранимой структуры равен фактическому размеру данных плюс 2 байта, в которых задана длина поля.

Полное название BINARY VARYING.

VARBINARY (<длина>) | BINARY VARYING (<длина>)

Этот тип является псевдонимом типа VARCHAR (<длина>) CHARACTER SET OCTETS и обратно совместим с ним.

4.26. BLOB

Тип данных BLOB называется большим двоичным объектом (Binary Large OBject). Этот тип данных позволяет хранить любые очень большие по объему данные — форматированные тексты, графику, звуки, видео.

Листинг 4.1 Синтаксис объявления типа BLOB

 BLOB [SUB_TYPE <имя подтипа>]
 [SEGMENT SIZE <размер сегмента>]
 [CHARACTER SET <набор символов>]

Также можно использовать сокращенный синтаксис:

Листинг 4.2 Сокращенный синтаксис объявления типа BLOB

 BLOB (<размер сегмента>) |
 BLOB (<размер сегмента>, <имя подтипа>) |
 BLOB (, <имя подтипа>)

Тип данных BLOB характеризуется с точки зрения хранения в базе данных, в первую очередь, размером сегмента. Максимальный размер сегмента не может превышать 64Кб – 1, то есть числа 65535. Объем данных, которые могут храниться в этом типе данных, зависит от размера страницы базы данных:

  • При размере страницы 4096 байтов размер BLOB не может превышать 4 ГБ,

  • При размере страницы 8192 байтов — 32 ГБ,

  • При размере страницы 16384 байтов — 256 ГБ.

При объявлении столбца или домена типа BLOB можно указать его подтип (предложение SUB_TYPE), а также размер сегмента, используемый при хранении данных (предложение SEGMENT SIZE). Значение подтипа может быть целым числом в диапазоне от –32768 до +32767.

Подтипы (положительные числа или 0) могут использоваться в случае, когда в базе данных описаны стандартные BLOB-фильтры. Фильтры — это программы, которые выполняют преобразования между данными BLOB разных подтипов на серверной и клиентской стороне. Такие преобразования связаны, как правило, с упаковкой и, соответственно, распаковкой данных.

В РЕД Базе Данных существуют семь заранее предопределенных подтипов. Они представлены в таблице 4.11. Их не следует использовать для каких-то своих внутренних целей.

Таблица 4.11 Предопределенные подтипы типа BLOB

Подтип BLOB

Имя подтипа

Назначение

0

BINARY

Если подтип не указан, то данные считаются нетипизированными и значение подтипа принимается равным 0. Этот подтип указывает, что данные имеют форму бинарного файла или потока (изображение, звук, видео, файлы текстового процессора, PDF и т.д.).

1

TEXT

Это специализированный подтип, который используется для хранения текстовых данных большого объёма. Для текстового подтипа BLOB может быть указан набор символов и порядок сортировки COLLATE, аналогично символьному полю.

2

BLR

Данные двоичного представления языка (BLR — binary language representation)

3

ACL

Список управления доступом

4

RANGES

Резервируется для будущих использований

5

SUMMARY

Закодированные описания для метаданных таблиц

6

FORMAT

Форматированные данные

7

TRANSACTION_DESCRIPTION

Описание транзакций ко многим базам данных. Эти транзакции завершаются в неопределенном порядке

8

EXTERNAL_FILE_DESCRIPTION

Описание внешних файлов

Для пользовательских подтипов рекомендуется выбирать только отрицательные числа, поскольку положительные могут использоваться системой, в том числе и при дальнейших расширениях. На клиентских программах лежит ответственность за то, что в поля BLOB заданного подтипа записываются данные соответствующего вида.

Хранение BLOB

Поля BLOB не хранятся непосредственно в самой записи вместе с другими данными строки. Запись содержит только ссылку (идентификатор, указатель) на страницу базы данных, где располагаются данные BLOB. Сами данные помещаются в сегменты. Размер сегмента задает в байтах размер полей в базе данных, которые будут использованы для хранения данных типа BLOB. По умолчанию принимается 80. Максимально возможное значение 65535. За одно обращение к базе данных система всегда считывает один сегмент. Если в поле BLOB хранятся данные, занимающие менее 32765 байтов, то хранение и работа с этим полем осуществляется так же, как и с полем, имеющим тип данных VARCHAR.

По умолчанию, для каждого BLOB создаётся обычная запись, хранящаяся на какой-то выделенной для этого странице данных (data page). Если весь BLOB на эту страницу поместится, его называют BLOB уровня 0. Номер этой специальной записи хранится в записи таблицы и занимает 8 байт.

Если BLOB не помещается на одну страницу данных (data page), то его содержимое размещается на отдельных страницах, целиком выделенных для него (blob page), а в записи о BLOB помещают номера этих страниц. Это BLOB уровня 1.

Если массив номеров страниц с данными BLOB не помещается на страницу данных (data page), то его (массив) размещают на отдельных страницах (blob page), а в запись о BLOB помещают уже номера этих страниц. Это BLOB уровня 2.

Уровни выше 2 не поддерживаются.

4.27. Массивы

Поддержка массивов в СУБД Firebird является расширением традиционной реляционной модели. Поддержка в СУБД такого инструмента позволяет проще решать некоторые задачи по обработке однотипных данных. Массивы в Firebird реализованы на базе полей типа BLOB. Массивы могут быть одномерными и многомерными.

CREATE TABLE SAMPLE_ARR (
       ID INTEGER NOT NULL PRIMARY KEY,
       ARR_INT INTEGER [4]);

Так будет создана таблица с полем типа массива из четырёх целых. Индексы данного массива от 1 до 4. Для определения верхней и нижней границы значений индекса следует воспользоваться следующим синтаксисом:

[ <нижняя>: <верхняя>]

Добавление новой размерности в синтаксисе идёт через запятую. Пример создания таблицы с массивом размерности два, в котором нижняя граница значений начинается с нуля:

CREATE TABLE SAMPLE_ARR2 (
       ID INTEGER NOT NULL PRIMARY KEY,
       ARR_INT INTEGER [0:3, 0:3]);

СУБД не предоставляет большого набора инструментов для работы с содержимым массивов. База данных employee.fdb, которая находится в дистрибутиве РЕД Базы Данных, содержит пример хранимой процедуры, показывающей возможности работы с массивами. Ниже приведён её текст:

ALTER PROCEDURE SHOW_LANGS (
     CODE VARCHAR(5),
     GRADE SMALLINT,
     CTY VARCHAR(15))
RETURNS (
     LANGUAGES VARCHAR(15))
AS
     DECLARE VARIABLE I INTEGER;
BEGIN
     i = 1;
     WHILE (i <= 5) DO
     BEGIN
           SELECT language_req[:i] FROM job
           WHERE ((job_code = :code) AND (job_grade = :grade) AND (job_country = :cty)
                   AND (language_req IS NOT NULL))
           INTO :LANGUAGES;
           IF (LANGUAGES = ' ') THEN  /* Prints 'NULL' instead of blanks */
              LANGUAGES = 'NULL';
           I = I +1;
           SUSPEND;
     END
END

Если приведённых выше возможностей достаточно для ваших задач, то вы вполне можете применять массивы для своих проектов. В настоящее время совершенствования механизмов обработки массивов средствами СУБД не производится.

4.28. SQL_NULL

Тип данных SQL_NULL содержит не данные, а только состояние: NULL или NOT NULL. Также этот тип данных не может быть использован при объявлении полей таблицы, переменных или PSQL параметров. Этот тип данных добавлен для улучшения поддержки нетипизированных параметров в предикате IS NULL. Такая проблема возникает при использовании «отключаемых фильтров» при написании запросов следующего типа:

WHERE col = :param OR :param IS NULL

Приведем пример. Разработчики приложений хотят поддерживать запросы с дополнительными фильтрами, такими, как эти:

SELECT
     AU.MAKE, AU.MODEL, AU.WEIGHT, AU.PRICE, AU.IN_STOCK
FROM AUTOMOBILES AU
WHERE (AU.MAKE = :MAKE OR :MAKE IS NULL)
    AND (AU.MODEL = :MODEL OR :MODEL IS NULL)
    AND (AU.PRICE <= :MAXPRICE OR :MAXPRICE IS NULL)

Идея состоит в том, что конечный пользователь может дополнительно ввести варианты для параметров :MAKE, :MODEL и :MAXPRICE. Там, где сделан выбор, должен быть применен соответствующий фильтр. Везде, где значение параметра не установлено (NULL), никакой фильтрации по этому атрибуту не должно быть. Если все параметры не установлены, то должны быть показана вся таблица AUTOMOBILES.

Именованные параметры, такие как :MAKE, :MODEL и :MAXPRICE, существует только на уровне приложений. Прежде чем запрос передается серверу для подготовки, он должен быть преобразован в такую форму:

SELECT
   AU.MAKE, AU.MODEL, AU.WEIGHT, AU.PRICE, AU.IN_STOCK
FROM AUTOMOBILES AU
WHERE  (AU.MAKE = ? OR ? IS NULL)
   AND (AU.MODEL = ? OR ? IS NULL)
   AND (AU.PRICE <= ? OR ? IS NULL)

Вместо трех именованных параметров, каждый из которых используется два раза, мы теперь имеем шесть позиционных параметров. РЕД База Данных не может определить тип данных параметра ? IS NULL. Эта проблема может быть решена путем приведения типов, например, WHERE (AU.MAKE = ? OR CAST(? AS TYPE OF COLUMN AU.MAKE) IS NULL).... Но это довольно громоздко. И еще одна проблема: там, где параметр для фильтра не NULL, его значение будет передано серверу два раза. А это небольшие, но потери производительности.

Для решения этих проблем и был введён тип данных SQL_NULL.

4.29. Явное преобразование типов данных. Функция CAST

Хотя в некоторых случаях сервер РЕД Базы Данных осуществляет неявное преобразование данных в подходящий тип, тем не менее, во избежание неверных результатов или других возможных ошибок всегда следует осуществлять явное преобразование типов данных при выполнении операций, включая операции сравнения, над данными различных типов.

В тех случаях, когда требуется выполнить явное преобразование одного типа в другой, используют функцию CAST.

Функция CAST позволяет преобразовывать данные из одного типа данных в другой, допустимый для исходного значения. Синтаксис функции:

Листинг 4.3 Синтаксис функции преобразования типов данных CAST

CAST ( {<значение> | NULL} AS <тип данных> [CHARACTER SET <набор символов>] )

<тип данных> ::= {
           <тип данных SQL>
         | [TYPE OF] <имя домена>
         | TYPE OF COLUMN <имя таблицы/представления>.<имя столбца> }

При преобразовании к домену ([TYPE OF] <имя домена>) учитываются объявленные для него ограничения, например, NOT NULL или описанные в CHECK и если <значение> не пройдёт проверку, то преобразование не удастся. В случае если дополнительно указывается TYPE OF (преобразование к базовому типу), при преобразовании игнорируются любые ограничения домена. При использовании TYPE OF с типом (VAR)CHAR набор символов и сортировка сохраняются.

При преобразовании к типу столбца (TYPE OF COLUMN) допускается использовать указание столбца таблицы или представления. Используется только сам тип столбца; в случае строковых типов это также включает набор символов, но не сортировку. Ограничения и значения по умолчанию исходного столбца не применяются.

При преобразовании любого типа в строковый тип данных CHAR или VARCHAR можно также указать и набор символов, в который переводится строка.

Преобразование константы NULL в любой тип данных всегда дает NULL.

Таблица 4.12 Допустимые преобразования для функции CAST

Из типа

В тип

Числовые типы (SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE PRECISION, DECFLOAT, NUMERIC, DECIMAL)

Числовые типы, [VAR]CHAR, BLOB

[VAR]CHAR, BLOB

[VAR]CHAR, BLOB, BOOLEAN, Числовые типы, DATE, TIME, TIMESTAMP

DATE, TIME

[VAR]CHAR, BLOB, TIMESTAMP

TIMESTAMP

[VAR]CHAR, BLOB, TIME, DATE

BOOLEAN

[VAR]CHAR, BLOB

Для преобразования строковых типов данных в тип BOOLEAN необходимо чтобы строковый аргумент был одним из предопределённых литералов логического типа (true или false ).

Примечание

При преобразовании типов следует помнить о возможной частичной потери данных, например, при преобразовании типа данных TIMESTAMP в DATE.

Преобразование в типы данных даты и времени

Для преобразования строковых типов данных в типы DATE, TIME или TIMESTAMP с помощью функции CAST необходимо, чтобы строковый аргумент был либо одним из предопределённых литералов даты и времени, либо строковое представление даты в одном из разрешённых форматов.

<литерал даты> ::=
      [ГГГГ<p>]ММ<p>ДД |
      ММ<p>ДД[<p>ГГГГ] |
      ДД<p>ММ[<p>ГГГГ] |
      ММ<p>ДД[<p>ГГ] |
      ДД<p>ММ[<p>ГГ]

<литерал времени> := ЧЧ[:мм[:СС[.НННН]]]

<литерал даты-времени> ::= <литерал даты> <литерал времени>

<часовой пояс> ::=
      <региональный часовой пояс> |
      [+/-] <разница часов с GMT> : <разница минут с GMT>

<p> ::= <пробел> | . | : | , | - | /
Таблица 4.13 Описание формата даты и времени

Аргумент

Описание

ГГГГ

Год из четырёх цифр

ГГ

Последние две цифры года (00-99)

ММ

Номер месяца в 1 или 2 цифры (1-12 или 01-12). В качестве месяца допустимо также указывать трёхбуквенное сокращение (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) или полное наименование месяца на английском языке, регистр не имеет значение

ДД

День месяца в 1 или 2 цифры (1-31 или 01-31)

ЧЧ

Час в 1 или 2 цифры (0-23 или 00-23)

мм

Минуты 1 или 2 цифры (0-59 или 00-59)

СС

Секунды в 1 или 2 цифры (0-59 или 00-59)

НННН

Десятитысячные доли секунды от 1 до 4 цифр (0-9999)

Правила:

  • В формате ГГГГ<p>ММ<p>ДД, год обязательно должен содержать 4 цифры;

  • Для дат в формате с завершающим годом, если в качестве разделителя дат используется точка ".", то дата интерпретируется в форме День-Месяц-Год, для остальных разделителей она интерпретируется в форме Месяц-День-Год;

  • Если год не указан, то в качестве года берётся текущий год;

  • Если указаны только две цифры года, то для получения столетия Firebird использует алгоритм скользящего окна. Задача заключается в интерпретации двухсимвольного значения года как ближайшего к текущему году в интервале предшествующих и последующих 50 лет;

  • Если не указан один из элементов времени, то оно принимается равным 0.

Примечание

Настоятельно рекомендуем в литералах дат использовать только формы с полным указанием года в виде 4 цифр во избежание путаницы.

Примеры преобразований в типы данных даты-времени:

CAST('04.12.2014' AS DATE)      -- ДД.ММ.ГГГГ
CAST('12-04-2014' AS DATE)      -- ММ-ДД-ГГГГ
CAST('12/04/2014' AS DATE)      -- ММ/ДД/ГГГГ
CAST('04.12.14' AS DATE)        -- ДД.ММ.ГГ
CAST('04.12' AS DATE)           -- ДД.ММ  в качестве года берётся текущий
CAST('12/4' AS DATE)            -- ММ/ДД  в качестве года берётся текущий
CAST('2014/12/04' AS DATE)      -- ГГГГ/ММ/ДД
CAST('2014.12.04' AS DATE)      -- ГГГГ.ММ.ДД
CAST('2014-12-04' AS DATE)      -- ГГГГ-ММ-ДД
CAST('11:37' AS TIME)           -- ЧЧ:мм
CAST('11:37:12' AS TIME)        -- ЧЧ:мм:сс
CAST('11:31:12.1234' AS TIME)   -- ЧЧ:мм:сс.нннн
CAST('11:31:12.1234 +03:30' AS TIME WITH TIME ZONE)          -- ЧЧ:мм:сс.нннн +чч:мм
CAST('11:31:12.1234 Europe/Moscow' AS TIME WITH TIME ZONE)   -- ЧЧ:мм:сс.нннн тз
CAST('11:31 Europe/Moscow' AS TIME WITH TIME ZONE)           -- ЧЧ:мм тз
CAST('04.12.2014 11:37' AS TIMESTAMP)            -- ДД.ММ.ГГГГ ЧЧ:мм
CAST('12/04/2014 11:37:12' AS TIMESTAMP)         -- ММ/ДД/ГГГГ ЧЧ:мм:сс
CAST('04.12.2014 11:31:12.1234' AS TIMESTAMP)    -- ДД.ММ.ГГГГ ЧЧ:мм:сс.нннн
CAST('2014-12-04 11:31:12.1234 +03:00' AS TIMESTAMP WITH TIME ZONE)                   -- ГГГГ-ММ-ДД ЧЧ:мм:сс.нннн +чч:мм
CAST('04.12.2014 11:31:12.1234 Europe/Moscow' AS TIMESTAMP WITH TIME ZONE)            -- ДД.ММ.ГГГГ ЧЧ:мм:сс.нннн тз

Помимо функции CAST можно использовать декларации типа константы (чтобы объявить именно дату, а не строку):

{ TIMESTAMP | DATE | TIME } '<литерал даты-времени>'

Разница константы и преобразования типа в том, что константа определяется на уровне парсинга запроса, а преобразование выполняется в рантайме. Разницу очень хорошо видно в статистике выполнения:

  • a = '01.01.2000'

  • a = cast('01.01.2000' as date)

  • a = date '01.01.2000'

При большом числе проверяемых записей последний вариант сильно быстрее.

UPDATE SALES
SET ORDER_STATUS = 'shipped'
WHERE ORDER_DATE > DATE '1-Jan-1993';

INSERT INTO EMPLOYEE (EMP_NO, DEPT_NO, HIRE_DATE)
VALUES (973, 8804, DATE '05.12.2023' + 2 + TIME '16:00');

INSERT INTO EMPLOYEE (EMP_NO, DEPT_NO, HIRE_DATE)
VALUES (34, 884, TIMESTAMP '01-22-2100');

Примечание

Обратите внимание, что эти сокращённые выражения вычисляются сразу же во время синтаксического анализа, то есть, как будто оператор уже подготовлен к выполнению. Таким образом, даже если запрос выполняется несколько раз, значение, например, для TIMESTAMP 'NOW' не изменится, независимо от того, сколько времени проходит. Если вам нужно получать нарастающее значение времени (т.е. оно должно быть оценено при каждом вызове), используйте полный синтаксис CAST.

NEW.CHANGE_DATE = CAST(NOW AS TIMESTAMP);
Таблица 4.14 Литералы с предопределёнными значениями даты и времени

Литерал

Значение

Тип данных для

диалекта 1

Тип данных для диалекта 3

'NOW'

Текущая дата и время

DATE

TIMESTAMP

'TODAY'

Текущая дата

DATE (c нулевым временем)

DATE (только дата)

'TOMORROW'

Завтрашняя дата

DATE (c нулевым временем)

DATE (только дата)

'YESTERDAY'

Вчерашняя дата

DATE (c нулевым временем)

DATE (только дата)

Преобразование из типов данных даты и времени в строку

Синтаксис для преобразования из типов данных даты и времени в строковые типы:

Листинг 4.4 Синтаксис функции преобразования из типов данных даты и времени в строку

CAST (<значение> AS <тип данных> FORMAT <шаблон>)

<значение> ::=
    DATE
  | TIME [{WITH | WITHOUT} TIME ZONE]
  | TIMESTAMP [{WITH | WITHOUT} TIME ZONE]

<тип данных> ::=
    VARCHAR
  | CHAR

<шаблон> ::= ["строковое значение"] <формат>
Таблица 4.15 Описание формата даты и времени

Формат

Описание

YEAR

Год (1 - 9999)

YYYY

YYY

YY

Y

Последние 4 цифры года (0001 - 9999)

Последние 3 цифры года (000 - 999)

Последние 2 цифры года (00 - 99)

Последняя цифра года (0 - 9)

Q

Квартал года (1 - 4)

MM

Месяц (01 - 12)

MON

Сокращённое название месяца (Apr)

MONTH

Название месяца (APRIL)

RM

Римское представление месяца (I - XII)

WW

Неделя года (01 - 53)

W

Неделя месяца (1 - 5)

D

День недели (1 - 7)

DAY

Название дня недели (MONDAY)

DD

День месяца (01 - 31)

DDD

День года (001 - 366)

DY

Сокращённое название дня недели (Mon)

J

Юлианский день (кол-во дней с 1 января 4712г. до н.э.)

HH

HH12

Часы (01 - 12) без интервала (для указания интервала используйте A.M., P.M.)

HH24

Часы (00 - 23)

MI

Минуты (00 - 59)

SS

Секунды (00 - 59)

SSSSS

Секунды после полуночи (0 - 86399)

FF1

FF2

FF3

FF4

FF5

FF6

FF7

FF8

FF9

Дробные секунды с указанной точностью

A.M.

P.M.

Интервал для времени в формате HH12 (не имеет значения, какой из них используется, период будет вставлен в зависимости от времени)

TZH

Часовой пояс в часах (-14 - 14)

TZM

Часовой пояс в минутах (00 - 59)

TZR

Название часового пояса

В качестве разделителей можно использовать следующие символы: '.', '/', ',', ';', ':', '<пробел>', '-'.

Форматы можно указывать без разделителей:

SELECT CAST(CURRENT_TIMESTAMP AS VARCHAR(50)
                   FORMAT 'YEARMMDD HH24MISS') FROM RDB$DATABASE;   -- 20230719 161757

Однако, при указании форматов без разделителей нужно быть внимательнее, например, 'DDDDD' будет интерпретирован как 'DDD' и 'DD'.

Формат не чувствителен к регистру.

Пример преобразования времени в строку:

SELECT CAST(CURRENT_TIMESTAMP AS VARCHAR(20)
                   FORMAT 'HH24:MI:SS') FROM RDB$DATABASE;   -- 15:13:50

В формате можно указывать строковое значение:

SELECT CAST(CURRENT_TIMESTAMP AS VARCHAR(20)
            FORMAT '"Today is" DAY') FROM RDB$DATABASE;   -- Today is TUESDAY

Экранируйте символ двойных кавычек (\"), чтобы добавить его в строку вывода. Для печати \ используйте \\.

Пример преобразования времени в Юлианский день:

SELECT CAST(CURRENT_TIMESTAMP AS VARCHAR(45)
             FORMAT 'DD.MM.YEAR HH24:MI:SS "is" J "Julian day"') FROM RDB$DATABASE;    -- 14.6.2023 15:41:29 is 2460110 Julian day

Преобразование из строки в типы данных даты и времени

Синтаксис для преобразования из строки в типы данных даты и времени:

Листинг 4.5 Синтаксис функции преобразования из строки в типы данных даты и времени

CAST (<значение> AS <тип данных> FORMAT <шаблон>)

<значение> ::=
    VARCHAR
  | CHAR

<тип данных> ::=
      DATE
    | TIME [{WITH | WITHOUT} TIME ZONE]
    | TIMESTAMP [{WITH | WITHOUT} TIME ZONE]

<шаблон> ::= ["строковое значение"] <формат>
Таблица 4.16 Описание формата даты и времени

Формат

Описание

YEAR

Год (1 - 9999)

YYYY

YYY

YY

Y

Последние 4 цифры года (0001 - 9999)

Последние 3 цифры года (000 - 999)

Последние 2 цифры года (00 - 99)

Последняя цифра года (0 - 9)

RR

Округление года. При использовании RR значение округляется по следующему правилу:

Указанное значение от 00 до 49:

  • Если последние две цифры текущего года от 00 до 49, то возвращаемый год будет иметь те же первые две цифры, что и текущий год;

  • Если последние две цифры текущего года от 50 до 99, то первые 2 цифры возвращаемого года будут на 1 больше первых 2 цифр текущего года.

Указанное значение от 50 до 99:

  • Если последние две цифры текущего года от 00 до 49, то первые 2 цифры возвращаемого года будут на 1 меньше первых 2 цифр текущего года.

  • Если последние две цифры текущего года от 50 до 99, то возвращаемый год будет иметь те же первые две цифры, что и текущий год.

RRRR

Последние 4 цифры года (0001 - 9999)

MM

Месяц (1 - 12)

MON

Сокращённое название месяца (Apr)

MONTH

Название месяца (APRIL)

RM

Римское представление месяца (I - XII)

DD

День месяца (1 - 31)

J

Юлианский день (кол-во дней с 1 января 4712г. до н.э.)

HH

HH12

Часы (1 - 12) без интервала (для указания интервала используйте A.M., P.M.)

HH24

Часы (0 - 23)

MI

Минуты (0 - 59)

SS

Секунды (0 - 59)

SSSSS

Секунды после полуночи (0 - 86399)

FF1

FF2

FF3

FF4

Дробные секунды с указанной точностью

A.M.

P.M.

Интервал для времени в формате HH12 (не имеет значения, какой из них используется, период будет взят из входной строки)

TZH

Часовой пояс в часах (-14 - 14)

TZM

Часовой пояс в минутах (0 - 59)

TZR

Название часового пояса

В качестве разделителей можно использовать следующие символы: '.', '/', ',', ';', ':', '<пробел>', '-'.

Год, месяц и день будут взяты из текущей даты, если они не используются в шаблоне.

Пример преобразования:

SELECT CAST('2000.12.08 12:35:30.5000' AS TIMESTAMP
             FORMAT 'YEAR-MM-DD HH24:MI:SS.FF4') FROM RDB$DATABASE;
                                                          -- 2000-12-08 12:35:30.5000

4.30. Неявное преобразование типов данных

В РЕД Базе Данных возможно неявное преобразование данных одних типов в другой без применение функции CAST. Например, в выражении отбора можно записать:

WHERE DOC_DATE < '31.08.2014'

и преобразование строки в дату произойдёт неявно.

При конкатенации множества элементов разных типов, все не строковые данные будут неявно преобразованы к строке, если это возможно.

select 30|| ' days hath September,April,June and November' CONCAT$ FROM rdb$database;

CONCAT$
=======================================================
30 days hath September,April,June and November

Однако, при выполнении арифметических операций (сложение, вычитание и др.) в 3 диалекте требуется указывать функцию CAST для явной трансляции одного типа в другой. В 1 диалекте все символьные данные будут неявно преобразованы в число, если это возможно. Например:

2 + '1'

корректно выполнится. В 3-м диалекте подобное выражение вызовет ошибку, в нем потребуется запись следующего вида:

2 + CAST('1' AS SMALLINT)