FirebirdSQL logo
 Структура языка SQLОбщие элементы языка 

DECFLOAT

DECFLOAT является числовым типом из стандарта SQL:2016, который точно хранитчисла с плавающей запятой. В отличие от DECFLOAT типы FLOAT или DOUBLE PRECISIONобеспечивают двоичное приближение предполагаемой точности.

Firebird в соответствии со стандартом IEEE 754-1985 (IEEE 754-2008) реализует типыDECIMAL64 (DECFLOAT(16)) и DECIMAL128 (DECFLOAT(34)).

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

16-значное и 34-значное

“16” и “34” относятся к максимальной точности десятичных цифр.

См. https://ru.wikipedia.org/wiki/IEEE_754-2008#Основные_и_взаимозаменяемые_форматы для получения подробного описания.

Синтаксис
DECFLOAT[(precision)]

precision ::= 16 | 34
Table 1. Диапазон значений DECFLOAT
Тип Максимальная точность Минимальная экспонента Максимальная экспонента Наименьшее значение Наибольшее значение

DECFLOAT(16)

16

-383

+384

1E-398

9.9..9E+384

DECFLOAT(34)

34

-6143

+6144

1E-6176

9.9..9E+6144

Обратите внимание, что хотя наименьший показатель степени для DECFLOAT (16) равен -383, наименьшее значение имеет показатель степени -398, что на 15 цифр меньше.И аналогично для DECFLOAT (34), наименьший показатель степени равен -6143, но наименьшее значение имеет показатель степени -6176, что на 33 цифры меньше.Причина заключается в том, что точность была “принесена в жертву”, чтобы можно было хранить меньшее значение.

Это результат того, как хранится значение: как десятичное значение из 16 или 34 цифр и показатель степени.Например, 1.234567890123456e-383 фактически сохраняется как коэффициент 1234567890123456 и показатель степени -398,а 1E-398 сохраняется как коэффициент 1, показатель степени -398.

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

Example 1. Использование типа DECFLOAT при определении таблицы
CREATE TABLE StockPrice (
  id    INT NOT NULL PRIMARY KEY,
  stock DECFLOAT(16),
  ...
);
Example 2. Использование типа DECFLOAT в PSQL
DECLARE VARIABLE v DECFLOAT(34);
Поведение операций с DECFLOAT

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

docnext count = 57

Длина литералов DECFLOAT

Значение типа DECFLOAT можно задать числовым литералом в научной нотации, только еслимантисса состоит из 20 или более цифр, или абсолютный показатель степени больше 308. В противном случае такие литералыинтерпретируются как DOUBLE PRECISION. Точные числовые литералы с 40 или более цифрами — фактически 39 цифр, если они больше максимального значения INT128 также обрабатываются как DECFLOAT (34).

В качестве альтернативы можно использовать строковый литерал и явно привести к желаемому типу DECFLOAT.

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

Литералы, содержащие более 34 значащих цифр, округляются с использованием режима округления DECFLOAT установленного для сеанса.

DECFLOAT и функции
Использование обычных функций

Ряд стандартных скалярных функций можно использовать с выражениями и значениями типа DECFLOAT.Это относится к следующим математическим функциям:

ABS

CEILING

EXP

FLOOR

LN

LOG

LOG10

POWER

SIGN

SQRT

Агрегатные функции SUM, AVG, MIN и MAX тоже работают с типом DECFLOAT.Все статистические агрегатные функции (такие как STDDEV или CORR, но не ограничено ими) могут работать с данными типа DECFLOAT.

Специальные функции для DECFLOAT

Firebird поддерживает 4 функции, которые созданы специально для поддержки типа DECFLOAT:

COMPARE_DECFLOAT

сравнивает два значения DECFLOAT как равные, разные или неупорядоченные

NORMALIZE_DECFLOAT

принимает единственный аргумент DECFLOAT и возвращает его в простейшей форме

QUANTIZE

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

TOTALORDER

выполняет точное сравнение двух значений DECFLOAT

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

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

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

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

Example 1. Сравнение числовых значений
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);

commit;

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.2000, 4.20, 4.6125.
-- Первые три значения возвращаются в неопределенном порядке.
Сравнение TotalOrder

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

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

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

Обратите внимание на то, что отрицательный нуль меньше положительного нуля при сравнении TotalOrder

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

Example 2. Сравнение TotalOrder

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

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);

commit;

select * from stockPrice where TOTALORDER(stock, 4.2000) = 0;
-- Возвращает только значение 4.2000

select * from stockPrice where TOTALORDER(stock, 4.20) = 1;
-- Возвращает два значения 4.2 и 4.6125, которое больше 4.20

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

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

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

Примеры:
SET BIND OF DECFLOAT TO LEGACY;
-- значения столбцов типа DECFLOAT будут преобразованы в тип DOUBLE PRECISION

-- другой вариант
SET BIND OF DECFLOAT TO DOUBLE PRECISION;

SET BIND OF DECFLOAT(16) TO CHAR;
-- значения столбцов типа DECFLOAT(16) будут преобразованы в тип CHAR(23)

SET BIND OF DECFLOAT(34) TO CHAR;
-- значения столбцов типа DECFLOAT(34) будут преобразованы в тип CHAR(42)

SET BIND OF DECFLOAT TO NUMERIC(18, 4);
-- значения столбцов типа DECFLOAT будут преобразованы в тип NUMERIC(18, 4)

SET BIND OF DECFLOAT TO NATIVE;
-- возвращает значения столбцов типа DECFLOAT в нативном типе

Различные привязки полезны, если вы планируете использовать значения DECFLOAT со старым клиентом, не поддерживающим собственный формат.Можно выбирать между строками (идеальная точность, но плохая поддержка для дальнейшей обработки), значения с плавающей запятой (идеальная поддержка для дальнейшей обработки, но с плохой точностью) или масштабированные целые числа (хорошая поддержка дальнейшей обработки и требуемая точность, но диапазон значений очень ограничен). Когда используется инструмент, подобный универсальному GUI-клиенту, выбор привязки к CHAR подходит в большинстве случаев.

Типы данных с фиксированной точкой

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

Firebird предлагает два типа данных с фиксированной точкой: NUMERIC и DECIMAL.В соответствии со стандартом оба типа ограничивают хранимое число объявленным масштабом (количеством чисел после запятой).При этом подход к тому, как ограничивается точность для типов разный: для столбцов NUMERIC точность является такой, “какобъявлено”, в то время, как DECIMAL столбцы могут получать числа, чья точность, по меньшей мере, равна тому, что было объявлено.

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

Для данных с фиксированной точкой общим является форма декларации, например NUMERIC(p, s). Здесь важно понять, что в этой записи s — это масштаб, а не интуитивно предсказываемое “количество знаков после запятой”.Для “визуализации” механизма хранения данных запомните для себя процедуру:

  • При сохранении в базу данных число умножается на 10 (10s), превращаясь в целое;

  • При чтении данных происходит обратное преобразование числа.

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

Table 1. Способ физического хранения чисел с фиксированной точкой
Точность Тип данных Диалект 1 Диалект 3

1 - 4

NUMERIC

SMALLINT

SMALLINT

1 - 4

DECIMAL

INTEGER

INTEGER

5 - 9

NUMERIC и DECIMAL

INTEGER

INTEGER

10 - 18

NUMERIC и DECIMAL

DOUBLE PRECISION

BIGINT

19 - 38

NUMERIC и DECIMAL

INT128

INT128

NUMERIC

Формат объявления данных
  NUMERIC
| NUMERIC(precision)
| NUMERIC(precision, scale)
Table 1. Параметры типа NUMERIC
Параметр ^^ Описание

precision

Точность. Может быть в диапазоне от 1 до 38.По умолчанию 9.

scale

Масштаб. Может быть в диапазоне от 0 до precision.По умолчанию 0.

В зависимости от точности precision и масштаба scale СУБД хранит данные по-разному.

Приведём примеры того, как СУБД хранит данные в зависимости от формы их объявления:

NUMERIC(4)    stored as   SMALLINT (exact data)
NUMERIC(4,2)              SMALLINT (data * 102)
NUMERIC(10,4) (Dialect 1) DOUBLE PRECISION
              (Dialect 3) BIGINT (data * 104)
NUMERIC(38, 6)            INT128 (data * 106)
Caution

Всегда надо помнить, что формат хранения данных зависит от точности.Например, вы задали тип столбца 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 для него надо создавать ограничение.

DECIMAL

Формат объявления данных
  DECIMAL
| DECIMAL(precision)
| DECIMAL(precision, scale)
Table 1. Параметры типа DECIMAL
Параметр ^^ Описание

precision

Точность. Может быть в диапазоне от 1 до 38.По умолчанию 9.

scale

Масштаб. Может быть в диапазоне от 0 до precision.По умолчанию 0.

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

Приведём примеры того, как СУБД хранит данные в зависимости от формы их объявления:

DECIMAL(4)    stored as   INTEGER (exact data)
DECIMAL(4,2)              INTEGER (data * 102)
DECIMAL(10,4) (Dialect 1) DOUBLE PRECISION
              (Dialect 3) BIGINT (data * 104)
DECIMAL(38, 6)            INT128 (data * 106)

Точность арифметических операций

Функции MIN, MAX, SUM, AVG работают со всеми точными числовыми типами.SUM и AVG являются точными, если обрабатываемая запись имеет точный числовой тип, а масштабированная сумма соответствует 64 или 128 битам: в противном случае возникает исключение переполнения.SUM и AVG никогда не вычисляются с использованием арифметики с плавающей запятой, если тип данных столбца не является приблизительным числом.

Функции MIN и MAX для точного числового столбца возвращают точный числовой результат, имеющий ту же точность и масштаб, что и столбец.SUM и AVG для точного числового типа возвращает результат типа NUMERIC ({18 | 38}, S) или DECIMAL ({18 | 38}, S), где S - масштаб столбца.Стандарт SQL определяет масштаб результата в таких случаях, в то время как точность SUM или AVG для столбцов с фиксированной точкой определяется реализацией: мы определяем его как 18 или 38 (если точность аргумента 18 или 38).

Если два операнда OP1 и OP2 являются точными числами с масштабами S1 и S2 соответственно, то OP1 + OP2 и OP1 - OP2 являются точными числами с точностью 18 или 38 (если один из аргументов с точностью 38) и масштабом равному наибольшему из значений S1 и S2, тогда как для OP1 * OP2 и OP1 / OP2 являются точными числами с точностью 18 или 38 (если точность аргументов 18 или 38) и шкалой S1 + S2.Масштабы этих операций, кроме разделения, определяются стандартом SQL.Точность всех этих операций и масштаб при делении стандартом не регламентируются, а определяются реализацией: Firebird определяет точность как 18 или 38 (если точность аргументов 18 или 38), а масштаб деления как S1 + S2, такой же, что определён стандартом в для умножения.

Всякий раз, когда выполняется арифметические операции с точными числовыми типами, в случае потери точности будет сообщено об ошибке переполнения, а не возвращено неправильное значение. Например, если столбец DECIMAL (18,4) содержит наиболее отрицательное значение этого типа, -922337203685477.5808, попытка разделить этот столбец на -1 будет сообщать об ошибке переполнения, поскольку истинный результат превышает наибольшее положительное значение, которое может быть представлено в типе, а именно 922337203685477.5807.

Если один операнд является точным числом, а другой приблизительным числом, то результатом любого из четырёх диадических операторов будет типа DOUBLE PRECISION.(В стандарте говорится, что результат является приблизительным числом с точностью, по крайней мере, такой же как точность приблизительного числового операнда: Firebird удовлетворяет этому требованию, всегда используя DOUBLE PRECISION, поскольку этот тип является максимальным приблизительным числовым типом, который предоставлен в Firebird.)

Типы данных для работы с датой и временем

В СУБД Firebird для работы с данными, содержащими дату и время, используются типы данных DATE, TIME и TIMESTAMP.В 3-м диалекте присутствуют все три вышеназванных типа данных, а в 1-м для операций с датой и временем доступен только тип данных DATE, который не тождественен типу данных DATE 3-го диалекта, а является типом данных TIMESTAMP из 3-го диалекта.

Note

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

В типах TIMESTAMP и TIME Firebird хранит секунды с точностью до десятитысячных долей.Если вам необходима более низкая гранулярность, то точность может быть указана явно в виде тысячных, сотых или десятых долей секунды в базах данных в 3 диалекте и ODS 11 и выше.

Note
Несколько полезных сведений о точности секунд

Временная часть типов TIME или TIMESTAMP представляет собой 4-байтный целое (WORD) вмещающее значение времени с долями секунды, и хранящаяся как количество десятитысячных долей секунды прошедших с полуночи.Фактическая точность значений полученных из time(stamp) функций и переменных будет следующей:

  • CURRENT_TIME — по умолчанию имеет точность до секунды, точность до миллисекунд может быть указана следующим образом CURRENT_TIME (0 | 1 | 2 | 3)

  • CURRENT_TIMESTAMP — по умолчанию имеет точность до миллисекунды, точность от секунд до миллисекунд может быть указана следующим образомCURRENT_TIMESTAMP (0 | 1 | 2 | 3)

  • LOCALTIME — по умолчанию имеет точность до секунды, точность до миллисекунд может быть указана следующим образомLOCALTIME (0 | 1 | 2 | 3)

  • LOCALTIMESTAMP — по умолчанию имеет точность до миллисекунды, точность от секунд до миллисекунд может быть указана следующим образомLOCALTIMESTAMP (0 | 1 | 2 | 3)

  • Литерал 'NOW' имеет точность до миллисекунд;

  • Функции DATEADD и DATEDIFF поддерживают точность до десятых долей миллисекунд.

  • Функция EXTRACT возвращает значения с точностью до десятых долей миллисекунды для аргументов SECOND и MILLISECOND;
Хранение типов с часовыми поясами

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

Хранение в формате UTC позволяет Firebird индексировать и сравнивать два значения в разных часовых поясах.

При хранении в UTC есть некоторые предостережения:

  • Когда вы используете именованные зоны и правила часовых поясов для этой зоны меняются, время в формате UTC остаётся прежним, но местное время в названной зоне может измениться.

  • Для типа данных TIME WITH TIME ZONE при вычислении смещения часового пояса для именованной зоны для получения местного времени в зоне применяются правила, действующие на 1 января 2020 года, чтобы гарантировать стабильное значение.Это может привести к неожиданным или сбивающим с толку результатам.

DATE

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

Допустимый диапазон хранения от 01.01.0001 н.э. до 31.12.9999 н.э.

Tip

В случае необходимости сохранять в 1 диалекте только значения даты, без времени, при записи в таблицу добавляйте время к значению даты в виделитерала '00:00:00.0000'.
Example 1. Пример использования DATE
CRETE TABLE DataLog(
  id BIGINT NOT NULL,
  bydate DATE
);
...
AS
  DECLARE BYDATE DATE;
BEGIN
...

См. такжеEXTRACT,CURRENT_DATE,Литералы дат.

TIME

Синтаксис
TIME [{WITH | WITHOUT} TIME ZONE]

EXTENDED TIME WITH TIME ZONE

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

Important

EXTENDED TIME WITH TIME ZONE предназначен для использования только при общении с клиентами, он решает проблему представления правильного времени на клиентах, у которых отсутствует библиотека ICU.Нельзя использовать расширенные типы данных в таблицах, процедурах и т.д.Единственный способ использовать эти типы данных — это приведение типов данных, включая инструкцию SET BIND (дополнительную информацию смотри в SET BIND OF).
Example 1. Пример использования TIME
CRETE TABLE DataLog(
  id BIGINT NOT NULL,
  bytime TIME WITH TIME ZONE
);
...
AS
  DECLARE BYTIME TIME; -- без часового пояса
  DECLARE BYTIME2 TIME WITHOUT TIME ZONE; -- без часового пояса
  DECLARE BYTIME3 TIME WITH TIME ZONE; -- с информацией о часовом поясе
BEGIN
...

См. такжеEXTRACT,AT,LOCALTIME,CURRENT_TIME,[fblangref-datatypes-convert-literalformats].

TIMESTAMP

Синтаксис
TIMESTAMP [{WITH | WITHOUT} TIME ZONE]

EXTENDED TIMESTAMP WITH TIME ZONE

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

Important

EXTENDED TIMESTAMP WITH TIME ZONE предназначен для использования только при общении с клиентами, он решает проблему представления правильного времени на клиентах, у которых отсутствует библиотека ICU.Нельзя использовать расширенные типы данных в таблицах, процедурах и т.д.Единственный способ использовать эти типы данных — это приведение типов данных, включая инструкцию SET BIND (дополнительную информацию смотри в SET BIND OF).
Example 1. Пример использования TIME
CRETE TABLE DataLog(
  id BIGINT NOT NULL,
  bydate TIMESTAMP WITH TIME ZONE
);
...
AS
  DECLARE BYDATE TIMESTAMP; -- без часового пояса
  DECLARE BYDATE2 TIMESTAMP WITHOUT TIME ZONE; -- без часового пояса
  DECLARE BYDATE3 TIMESTAMP WITH TIME ZONE; -- с информацией о часовом поясе
BEGIN
...

См. такжеEXTRACT,AT,LOCALTIMESTAMP,CURRENT_TIMESTAMP,[fblangref-datatypes-convert-literalformats].

Часовой пояс сеанса

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

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

Получение часового пояса сеанса

Получить текущий часовой пояс сеанса можно с использованием функции RDB$GET_CONTEXT с аргументами 'SYSTEM' для пространства имён и 'SESSION_TIMEZONE' в качестве имени переменной.

Example 1. Получение часового пояса сеанса
set time zone '-02:00';
select rdb$get_context('SYSTEM', 'SESSION_TIMEZONE') from rdb$database;
-- returns -02:00

set time zone 'America/Sao_Paulo';
select rdb$get_context('SYSTEM', 'SESSION_TIMEZONE') from rdb$database;
-- returns America/Sao_Paulo

Формат часового пояса

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

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

Региональная семантика TIME WITH TIME ZONE

По определению региональные часовые пояса зависят от момента (дата и время — или timestamp), чтобы узнать его смещение UTC относительно GMT.Но Firebird также поддерживает региональные часовые пояса в значениях TIME WITH TIME ZONE.

При построении значения TIME WITH TIME ZONE из литерала или его преобразования, значение UTC должно быть вычислено и не может быть изменено, поэтому текущая дата может не использоваться.В этом случае используется фиксированная дата 2020-01-01.Таким образом, при сравнении TIME WITH TIME ZONE с различными часовыми поясами сравнение выполняется аналогично тому, как они представляют собой значения TIMESTAMP WITH TIME ZONE на заданную дату.

Однако при преобразовании между типами TIMESTAMP в TIME WITH TIME ZONE эта фиксированная дата не используется,в противном случае могут наблюдаться некоторые странные преобразования, когда текущая дата имеет другое смещение (из-за изменений летнего времени), чем в 2020-01-01.В этом случае при преобразовании TIME WITH TIME ZONE в TIMESTAMP WITH TIME ZONE сохраняется часть времени (если это возможно).Например, если текущая дата 2020-05-03, эффективное смещение в часовом поясе America/Los_Angeles равно -420, а его эффективное смещениев 2020-01-01 равно -480, но cast(time '10:00:00 America/Los_Angeles' as timestamp with time zone) даст в результате 2020-05-03 10:00:00.0000 America/Los_Angeles вместо корректировки временной части.

Но в дату, когда начинается летнее время, пропущен час, например, для часового пояса America/Los_Angeles в 2021-03-14 нет времени с 02:00:00 до 02:59:59.В этом случае преобразование выполняется как построение литерала, и час корректируется до следующего допустимого значения.Например, в 2021-03-14 cast(time '02:10:00 America/Los_Angeles' as timestamp with time zone) даст результат 2021-03-14 03:10:00.0000 America/Los_Angeles.

Литералы даты и времени

Для записи литералов даты и времени в Firebird используются сокращённые "C-style" выражения.Строковое представление даты и времени должно быть в одном из разрешённых форматов.

Синтаксис
<date_literal> ::= DATE <date>

<time_literal> ::= TIME <time>

<timestamp_literal> ::= TIMESTAMP <timestamp>

<date> ::=
  [YYYY<p>]MM<p>DD |
  MM<p>DD[<p>YYYY] |
  DD<p>MM[<p>YYYY] |
  MM<p>DD[<p>YY] |
  DD<p>MM[<p>YY]


<time> := HH[:mm[:SS[.NNNN]]] [<time zone>]

<timestamp> ::= <date> <time>

<time zone> ::=
    <time zone region> |
    [+/-] <hour displacement> [: <minute displacement>]

<p> ::= whitespace | . | : | , | - | /
Table 1. Описание формата даты и времени
Аргумент Описание

datetime

Строковое представление даты-времени.

date

Строковое представление даты.

time

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

YYYY

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

YY

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

MM

Месяц.Может содержать 1 или 2 цифры (1-12 или 01-12). В качестве месяца допустимо также указывать трёх буквенное сокращение или полное наименование месяца на английском языке, регистр не имеет значение.

DD

День.Может содержать 1 или 2 цифры (1-31 или 01-31).

HH

Час.Может содержать 1 или 2 цифры (0-23 или 00-23).

mm

Минуты.Может содержать 1 или 2 цифры (0-59 или 00-59).

SS

Секунды.Может содержать 1 или 2 цифры (0-59 или 00-59).

NNNN

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

p

Разделитель, любой из разрешённых символов, лидирующие и завершающие пробелы игнорируются.

time zone region

Один из часовых поясов связанных с регионом.

hour displacement

Смещение времени для часов относительно GMT.

minute displacement

Смещение времени для минут относительно GMT.

Правила:

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

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

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

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

  • Если в строковом представлении времени присутствует часовой пояс или смещение времени, то тип литерала будет WITH TIME ZONE, в противном случае WITHOUT TIME ZONE;

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

Tip

Настоятельно рекомендуем в литералах дат использовать только формы с полным указанием года в виде 4 цифр во избежание путаницы.
Example 1. Примеры литералов дат и времени
SELECT
  date '04.12.2014' AS d1, -- DD.MM.YYYY
  date '12-04-2014' AS d2, -- MM-DD-YYYY
  date '12/04/2014' AS d3, -- MM/DD/YYYY
  date '04.12.14'  AS d4,   -- DD.MM.YY
  -- DD.MM в качестве года берётся текущий
  date '04.12' AS d5,
  -- MM/DD в качестве года берётся текущий
  date '12/4' AS d6,
  date '2014/12/04'  AS d7, -- YYYY/MM/DD
  date '2014.12.04'  AS d8, -- YYYY.MM.DD
  date '2014-12-04'  AS d9, -- YYYY-MM-DD
  time '11:37' AS t1, -- HH:mm
  time '11:37:12' AS t2, -- HH:mm:ss
  time '11:31:12.1234' AS t3, -- HH:mm:ss.nnnn
  -- HH:mm:ss.nnnn +hh
  time '11:31:12.1234 +03' AS t4,
  -- HH:mm:ss.nnnn +hh:mm
  time '11:31:12.1234 +03:30' AS t5,
  -- HH:mm:ss.nnnn tz
  time '11:31:12.1234 Europe/Moscow' AS t5,
  -- HH:mm tz
  time '11:31 Europe/Moscow' AS t6,
  -- DD.MM.YYYY HH:mm
  timestamp '04.12.2014 11:37' AS dt1,
  -- MM/DD/YYYY HH:mm:ss
  timestamp '12/04/2014 11:37:12' AS dt2,
  -- DD.MM.YYYY HH:mm:ss.nnnn
  timestamp '04.12.2014 11:31:12.1234' AS dt3,
  -- YYYY-MM-DD HH:mm:ss.nnnn +hh:mm
  timestamp '2014-12-04 11:31:12.1234 +03:00' AS dt4,
  -- DD.MM.YYYY HH:mm:ss.nnnn tz
  timestamp '04.12.2014 11:31:12.1234 Europe/Moscow' AS dt5
FROM rdb$database
Note

Обратите внимание, что эти сокращённые выражения вычисляются сразу же во время синтаксического анализа (подготовки запроса или компиляции процедуры, функции или триггера). До Firebird 4.0 сокращённые выражения позволялись также для специальных строковых литералов 'NOW', 'TODAY', 'TOMORROW', 'YESTERDAY'. Использование таких выражений в компилируемом PSQL приводило к тому, что значение "замораживалось" на момент компиляции, и возвращалось не актуальное значение.Поэтому в Firebird 4.0 сокращённые выражения для таких строковых литералов запрещены, однако вы можете использовать их при приведении типа оператором CAST.
См. также:

[fblangref-datatypes-convert-literalformats].

Операции, использующие значения даты и времени

Благодаря способу хранения даты и времени с этими типами возможны арифметические операции вычитания из более поздней даты (времени) более раннюю.Дата представлена количеством дней с "нулевой даты" – 17 ноября 1858 г.Время представлено количеством секунд (с учётом десятитысячных долей), прошедших с полуночи.

Table 1. Арифметические операции для типов данных даты и времени
Операнд 1 Оператор Операнд 2 Результат

DATE

+

TIME

TIMESTAMP

DATE

+

TIME WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

DATE

+

Числовое значение n

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

TIME

+

DATE

TIMESTAMP

TIME WITH TIME ZONE

+

DATE

TIMESTAMP WITH TIME ZONE

TIME

+

Числовое значение n

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

TIME WITH TIME ZONE

+

Числовое значение n

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

TIMESTAMP

+

Числовое значение n

TIMESTAMP, где дата будет увеличиваться на количество дней и на часть дня, представленную числом n - поэтому “+ 2.75” сдвинет дату вперед на 2 дня и 18 часов.

TIMESTAMP WITH TIME ZONE

+

Числовое значение n

TIMESTAMP WITH TIME ZONE, где дата будет увеличиваться на количество дней и на часть дня, представленную числом n - поэтому “+ 2.75” сдвинет дату вперед на 2 дня и 18 часов.

DATE

-

DATE

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

DATE

-

Числовое значение n

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

TIME

-

TIME

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

TIME

-

n

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

TIME

-

TIME WITH TIME ZONE

Значение без часового пояса преобразуется в WITH TIME ZONE в часовом поясе текущего сеанса.Возвращается количество секунд в интервале между UTC значениями как DECIMAL(9, 4).То же правило действует при изменении порядка операндов.

TIME WITH TIME ZONE

-

TIME WITH TIME ZONE

Возвращается количество секунд в интервале между UTC значениями как DECIMAL(9, 4).

TIMESTAMP

-

TIMESTAMP

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

TIMESTAMP

-

TIMESTAMP WITH TIME ZONE

Значение без часового пояса преобразуется в WITH TIME ZONE в часовом поясе текущего сеанса.Количество дней и части дня в интервале между UTC значениями как DECIMAL (18, 9).То же правило действует при изменении порядка операндов.

TIMESTAMP

-

n

TIMESTAMP, где дата будет уменьшена на количество дней, и часть дня, представленную числом n - поэтому “- 2.25” сдвинет дату назад на 2 дня и 6 часов.

TIMESTAMP WITH TIME ZONE

-

n

TIMESTAMP WITH TIME ZONE, где дата будет уменьшена на количество дней, и часть дня, представленную числом n - поэтому “- 2.25” сдвинет дату назад на 2 дня и 6 часов.

Одно значение даты/времени может быть вычтено из другого если:

  • Оба значения имеют один и тот же тип даты/времени;

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

Note

В диалекте 1 тип DATE рассматривается как TIMESTAMP.
См. также:

[fblangref-scalarfuncs-dateadd], [fblangref-scalarfuncs-datediff].

Дополнительные функции для поддержки часовых поясов

Firebird 4 предоставляет ряд функций для получения информации о часовых поясах.

Виртуальная таблица RDB$TIME_ZONES

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

См. также RDB$TIME_ZONES в приложении “Системные таблицы”.

Пакет RDB$TIME_ZONE_UTIL

Пакет RDB$TIME_ZONE_UTIL пакет содержит процедуры и функции для работы с часовыми поясами.

Подробное описание пакета вы можете найти в секции RDB$TIME_ZONE_UTIL главы "Системные пакеты".

Обновление базы данных часовых поясов

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

Firebird хранит значения WITH TIME ZONE, переведённые во время UTC. Предположим, что значение создано с помощью одной базы данных часового пояса, и более позднее обновление этой базы данных изменяет информацию в диапазоне нашего сохранённого значения. Когда это значение будет прочитано, оно будет возвращено как отличное от значения, которое было сохранено изначально.

Firebird использует IANA базу данных часовых поясов через библиотеку ICU. Библиотека ICU, представленная в комплекте Firebird (Windows) или установленная в операционной системе POSIX, иногда может иметь устаревшую базу данных часовых поясов.

Обновленную базу данных можно найти на этой странице в FirebirdSQL GitHub.Имя файла le.zip обозначает прямой порядок байтов и является необходимым файлом для большинства компьютерных архитектур (совместимых с Intel/AMD x86 или x64), в то время как be.zip обозначает архитектуры с прямым порядком байтов и необходим в основном для компьютерных архитектур RISC.Содержимое zip-файла должно быть извлечено в подкаталог /tzdata установки Firebird, перезаписывая существующие файлы *.res.

Note

/tzdata — это каталог по умолчанию, в котором Firebird ищет базу данных часовых поясов.Его можно переопределить с помощью переменной среды ICU_TIMEZONE_FILES_DIR.

Символьные типы данных

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

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

Unicode

В настоящее время все современные средства разработки поддерживают Unicode.При возникновении необходимости использования восточноевропейских текстов в строковых полях базы данных или для более экзотических алфавитов, рекомендуется работать с набором символов UTF8.При этом следует иметь в виду, что на один символ в данном наборе приходится до 4 байт.Следовательно, максимальное количество символов в символьных полях составит 32765/4 = 8191.

Note

При этом следует обратить внимание, что фактически значение параметра “bytes per character” зависит от диапазона, к которому принадлежит символ: английские буквы занимают 1 байт, русские буквы — 2 байта, остальные символы — могут занимать до 4-х байт.

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

NCHAR

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

Синтаксис
{NCHAR | NATIONAL {CHAR | CHARACTER}} [(length)]

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

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

Набор символов клиента

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

Специальные наборы символов

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

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

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

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

Последовательность сортировки

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

Предложение COLLATE collation может применяться в других контекстах помимо определения столбца. Для операций сравнения больше/меньше его можно добавить в предложение WHERE оператора SELECT. Если вывод необходимо отсортировать в специальной алфавитной последовательности или без учета регистра и существует соответствующее сопоставление, то предложение COLLATE может быть использовано в предложении ORDER BY, когда строки сортируются по символьному полю, и в предложении GROUP BY в случае групповых операций.

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

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

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

...
WHERE UPPER(name) = UPPER(:flt_name)

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

...
WHERE FIRST_NAME COLLATE PXW_CYRL >= :FLT_NAME
...
ORDER BY NAME COLLATE PXW_CYRL
См. также:

CONTAINING.

Последовательности сортировки для UTF-8

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

Table 1. Последовательности сортировки для UTF8
COLLATION Комментарии

UCS_BASIC

Сортировка работает в соответствии с положением символа втаблице (бинарная).

UNICODE

Сортировка работает в соответствии с алгоритмом UCA(Unicode Collation Algorithm) (алфавитная).

UTF-8

По умолчанию используется двоичное сопоставление,идентичное UCS_BASIC, которое было добавлено длясовместимости с SQL стандартом.

UNICODE_CI

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

UNICODE_CI_AI

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

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

ORDER BY NAME COLLATE UNICODE_CI_AI

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

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

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

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

Table 1. Длина индексируемой строки и набор символов

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

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

1

2

3

4

6

4096

1015

507

338

253

169

8192

2039

1019

679

509

339

16384

4087

2043

1362

1021

681

32768

8183

4091

2727

2045

1363
Note

В кодировках, нечувствительных к регистру (“_CI”), один символ в индексе будет занимать не 4, а 6 байт, поэтому максимальная длина ключа для страницы, например для страницы 4096 байт составит 169 символов.

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

См. также

CREATE DATABASE, Порядок сортировки, SELECT, WHERE, GROUP BY, ORDER BY

BINARY

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

Синтаксис
BINARY [(<length>)]
Note

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

Данный тип хорошо подходит для хранения уникального идентификатора полученного с помощью функции [fblangref-scalarfuncs-gen-uuid].
См. также:

CHAR, CHARACTER SET OCTETS.

CHAR

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

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

Синтаксис
{CHAR | CHARACTER} [(length)]
  [CHARACTER SET <charset>] [COLLATE <collate>]

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

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

VARBINARY

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

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

Синтаксис
{VARBINARY  | BINARY VARYING} (<length>)
Note

Этот тип является псевдонимом типа VARCHAR (<length>) CHARACTER SET OCTETS и обратно совместим с ним.
Example 1. Использование типов BINARY и VARBINARY в PSQL
DECLARE VARIABLE VAR1 VARBINARY(10);
Example 2. Использование типов BINARY и VARBINARY при определении таблицы
CREATE TABLE INFO (
  GUID BINARY(16),
  ENCRYPT_KEY VARBINARY(100),
  ICON BINARY VARYING(32000));
См. также:

VARCHAR, CHARACTER SET OCTETS.

VARCHAR

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

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

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

Синтаксис
 {VARCHAR | {CHAR | CHARACTER} VARYING} (length)
  [CHARACTER SET <charset>] [COLLATE <collate>]

Логический тип данных

В Firebird 3.0 был введён полноценный логический тип данных.

BOOLEAN

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

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

Оператор IS

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

Note

  • Операторы эквивалентности (“=”, “!=”, “<>” и др.) допустимы во всех сравнениях.

Примеры BOOLEAN

INSERT и SELECT
CREATE TABLE TBOOL (ID INT, BVAL BOOLEAN);
COMMIT;

INSERT INTO TBOOL VALUES (1, TRUE);
INSERT INTO TBOOL VALUES (2, 2 = 4);
INSERT INTO TBOOL VALUES (3, NULL = 1);
COMMIT;

SELECT * FROM TBOOL
ID           BVAL
============ =======
1            <true>
2            <false>
3            <null>
Проверка TRUE значения
SELECT * FROM TBOOL WHERE BVAL
ID           BVAL
============ =======
1            <true>
Проверка FALSE значения
SELECT * FROM TBOOL WHERE BVAL IS FALSE
ID           BVAL
============ =======
2            <false>
Проверка UNKNOWN значения
SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN
ID           BVAL
============ =======
3            <null>
Логические выражения в SELECT списке
SELECT ID, BVAL, BVAL AND ID < 2
FROM TBOOL
ID           BVAL
============ ======= =======
1            <true> <true>
2            <false> <false>
3            <null> <false>
PSQL объявления с начальным значением
DECLARE VARIABLE VAR1 BOOLEAN = TRUE;
Сравнения с UNKNOWN
-- Допустимый синтаксис, но как и сравнение
-- с NULL, никогда не вернёт ни одной записи
SELECT * FROM TBOOL WHERE BVAL = UNKNOWN
SELECT * FROM TBOOL WHERE BVAL <> UNKNOWN

Использование Boolean с другими типами данных

Хотя BOOLEAN по своей сути не может быть преобразован в какой-либо другой тип данных, начиная с версии 3.0.1 строки 'true' и 'false' (без учёта регистра) будут неявно приводиться к BOOLEAN в выражениях значений, например

if (true > 'false') then ...

'false' преобразуется в BOOLEAN.Любая попытка использовать логические операторы AND, NOT, OR и IS потерпят неудачу.Например, NOT 'False' приведёт к ошибке.

A BOOLEAN может быть явно преобразован в строку и из нее с помощью CAST.Значение UNKNOWN не доступен при преобразовании к строке.

Note
Другие замечания

  • Тип данных BOOLEAN представлен в API типом FB_BOOLEAN и константами FB_TRUE и FB_FALSE.

  • Значение TRUE больше чем значение FALSE.

Бинарные типы данных

BLOB

BLOB (Binary Large Objects, большие двоичные объекты) представляют собой сложные структуры, предназначенные для хранения текстовых и двоичных данных неопределённой длины, зачастую очень большого объёма.

Синтаксис
BLOB [SUB_TYPE <subtype>]
  [SEGMENT SIZE <seg_length>]
  [CHARACTER SET <charset>]
  [COLLATE <collation name>]
Сокращённый синтаксис:
BLOB (<seg_length>)
BLOB (<seg_length>, <subtype>)
BLOB (, <subtype>)
Размер сегмента:

Указание размера сегмента BLOB является некоторым атавизмом, оно идёт с тех времён, когда приложения для работы с данными BLOB писались на C (Embedded SQL) при помощи GPRE.В настоящий момент размер сегмента при работе с данными BLOB определяется клиентской частью, причём размер сегмента может превышать размер страницы данных.

Подтипы BLOB

Подтип BLOB отражает природу данных, записанную в столбце.Firebird предоставляет два предопределённых подтипа для сохранения пользовательских данных:

Подтип 0 (BINARY)

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

Подтип 1 (TEXT)

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

Пользовательские подтипы

Кроме того, существует возможность добавления пользовательских подтипов данных, для них зарезервирован интервал от -1 до -32768.Пользовательские подтипы с положительными числами не поддерживаются, поскольку Firebird использует числа больше 2 для внутренних подтипов метаданных.

Особенности BLOB

Размер

Максимальный размер поля BLOB ограничен 4Гб и не зависит от варианта сервера, 32 битный или 64 битный (во внутренних структурах, связанных с BLOB присутствуют 4-х байтные счётчики). Для размера страницы 4096 максимальный размер BLOB поля несколько ниже 2 Гб.

Операторы и выражения

Текстовые BLOB любой длины и с любым набором символов (включая multi-byte) могут быть использованы практически с любыми встроенными функциями и операторами.

Полностью поддерживаются следующие операторы:

=

(присвоение)

=, <>, <, <=, >, >=

(сравнение)

||

(конкатенация)

BETWEEN,

IS [NOT] DISTINCT FROM,

IN,

ANY | SOME,

ALL

 

Частично поддерживаются следующие операторы:

  • возникает ошибка, в случае если второй аргумент больше или равен 32 Кб

    STARTING [WITH],

    LIKE,

    CONTAINING

     

  • Предложения агрегирования работают не с содержимым самого поля, а с идентификатором BLOB ID. Помимо этого, есть некоторые странности:

    SELECT DISTINCT

    ошибочно выдаёт несколько значений NULL, если они присутствуют

    ORDER BY

     — 

    GROUP BY

    объединяет одинаковые строки, если они находятся рядом, но не делает этого, если они располагаются вдали друг от друга
Хранение BLOB

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

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

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

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

См. также:

FILTER, DECLARE FILTER.

Массивы

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

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

Так будет создана таблица с полем типа массива из четырёх целых.Индексы данного массива от 1 до 4.

Указание явных границ для измерений

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

[<lower>:<upper>]

Добавление дополнительных измерений

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

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

Использование массивов

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

CREATE OR 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

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

Специальные типы данных

Тип данных SQL_NULL

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

WHERE col1 = :param1 OR :param1 IS NULL

после обработки, на уровне API запрос будет выглядеть как

WHERE col1 = ? OR ? IS NULL

В данном случае получается ситуация, когда разработчик при написании SQL запрос рассматривает :param1 как одну переменную, которую использует два раза, а на уровне API запрос содержит два отдельных и независимых параметра.Вдобавок к этому, сервер не может определить тип второго параметра, поскольку он идёт в паре с IS NULL.

Именно для решения проблемы “? IS NULL” и был добавлен этот специальный тип данных SQL_NULL.

После введения данного специального типа данных при передаче запроса и его параметров на сервер будет работать такая схема: приложение передаёт параметризованные запросы на сервер в виде “?”.Это делает невозможным слияние пары “одинаковых” параметров в один.Так, например, для двух фильтров (двух именованных параметров) необходимо передать четыре позиционных параметра (далее предполагается, что читатель имеет некоторое знакомство с Firebird API):

SELECT
  SH.SIZE, SH.COLOUR, SH.PRICE
FROM SHIRTS SH
WHERE (SH.SIZE = ? OR ? IS NULL)
  AND (SH.COLOUR = ? OR ? IS NULL)

После выполнения isc_dsql_describe_bind() sqltype 2-го и 4-го параметров устанавливается в SQL_NULL.Как уже говорилось выше, сервер Firebird не имеет никакой информации об их связи с 1-м и 3-м параметрами — это полностью прерогатива программиста.Как только значения для 1-го и 3-го параметров были установлены (или заданы как NULL) и запрос подготовлен, каждая пара XSQLVARs должна быть заполнена следующим образом:

Пользователь задал параметры

  • Первый параметр (сравнение значений): установка *sqldata в переданное значение и *sqlind в 0 (для NOT NULL);

  • Второй параметр (проверка на NULL): установка *sqldata в null (указатель null, а не SQL NULL) и *sqlind в 0 (для NOT NULL).

Пользователь оставил поле пустым

  • Оба параметра (проверка на NULL): установка *sqldata в null (указатель null, а не SQL NULL) и *sqlind в -1 (индикация NULL).

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

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

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

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

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

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

Синтаксис
CAST (<expression> | NULL AS <data_type>)

<data_type> ::=
    <datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN relname.colname

<datatype> ::=
    <scalar_datatype> | <blob_datatype> | <array_datatype>

<scalar_datatype> ::=  См. <<fblangref-datatypes-syntax-scalar,Синтаксис скалярных типов данных>>

<blob_datatype> ::= См. <<fblangref-datatypes-syntax-blob,Синтаксис типа данных BLOB>>

<array_datatype> ::= См. <<fblangref-datatypes-syntax-array,Синтаксис массивов>>

Преобразование к домену

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

Преобразование к типу столбца

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

CREATE TABLE TTT (
  S VARCHAR (40)
  CHARACTER SET UTF8 COLLATE UNICODE_CI_AI);
COMMIT;

/* У меня много друзей (шведский)*/
SELECT
  CAST ('Jag har manga vanner' AS TYPE OF COLUMN TTT.S)
FROM RDB$DATABASE;

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

Table 1. Допустимые преобразования для функции CAST
Из типа В тип

Числовые типы

Числовые типы, [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').

Important

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

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

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

<date_literal> ::=
  [YYYY<p>]MM<p>DD |
  MM<p>DD[<p>YYYY] |
  DD<p>MM[<p>YYYY] |
  MM<p>DD[<p>YY] |
  DD<p>MM[<p>YY]


<time_literal> := HH[:mm[:SS[.NNNN]]]

<datetime_literal> ::= <date_literal> <time_literal>

<time zone> ::=
    <time zone region> |
    [+/-] <hour displacement> [: <minute displacement>]

<p> ::= whitespace | . | , | - | /
Table 1. Описание формата даты и времени
Аргумент Описание

datetime_literal

Литерал даты-времени.

date_literal

Литерал даты.

time_literal

Литерал времени.

YYYY

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

YY

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

MM

Месяц.Может содержать 1 или 2 цифры (1-12 или 01-12). В качестве месяца допустимо также указывать трёх буквенное сокращение или полное наименование месяца на английском языке, регистр не имеет значение.

DD

День.Может содержать 1 или 2 цифры (1-31 или 01-31).

HH

Час.Может содержать 1 или 2 цифры (0-23 или 00-23).

mm

Минуты.Может содержать 1 или 2 цифры (0-59 или 00-59).

SS

Секунды.Может содержать 1 или 2 цифры (0-59 или 00-59).

NNNN

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

p

Разделитель, любой из разрешённых символов, лидирующие и завершающие пробелы игнорируются

time zone region

Один из часовых поясов связанных с регионом

hour displacement

Смещение времени для часов относительно GMT

minute displacement

Смещение времени для минут относительно GMT
Table 2. Литералы с предопределёнными значениями даты и времени
Литерал Значение Тип данных для диалекта 1 Тип данных для диалекта 3

'NOW'

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

TIMESTAMP

TIMESTAMP

'TODAY'

Текущая дата

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

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

'TOMORROW'

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

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

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

'YESTERDAY'

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

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

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

Правила:

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

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

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

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

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

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

При преобразовании строковых литералов с предопределёнными значениями даты и времени в тип TIMESTAMP точность составляет 3 знака после запятой (миллисекунды).

Tip

Настоятельно рекомендуем в литералах дат использовать только формы с полным указанием года в виде 4 цифр во избежание путаницы.
Example 1. Преобразование строк в дату и время:
SELECT
  CAST('04.12.2014' AS DATE) AS d1, -- DD.MM.YYYY
  CAST('12-04-2014' AS DATE) AS d2, -- MM-DD-YYYY
  CAST('12/04/2014' AS DATE) AS d3, -- MM/DD/YYYY
  CAST('04.12.14' AS DATE) AS d4,   -- DD.MM.YY
  -- DD.MM в качестве года берётся текущий
  CAST('04.12' AS DATE) AS d5,
  -- MM/DD в качестве года берётся текущий
  CAST('12/4' AS DATE) AS d6,
  CAST('2014/12/04' AS DATE) AS d7, -- YYYY/MM/DD
  CAST('2014.12.04' AS DATE) AS d8, -- YYYY.MM.DD
  CAST('2014-12-04' AS DATE) AS d9, -- YYYY-MM-DD
  CAST('11:37' AS TIME) AS t1, -- HH:mm
  CAST('11:37:12' AS TIME) AS t2, -- HH:mm:ss
  CAST('11:31:12.1234' AS TIME) AS t3, -- HH:mm:ss.nnnn
  -- HH:mm:ss.nnnn +hh
  CAST('11:31:12.1234 +03' AS TIME WITH TIME ZONE) AS t4,
  -- HH:mm:ss.nnnn +hh:mm
  CAST('11:31:12.1234 +03:30' AS TIME WITH TIME ZONE) AS t5,
  -- HH:mm:ss.nnnn tz
  CAST('11:31:12.1234 Europe/Moscow' AS TIME WITH TIME ZONE) AS t5,
  -- HH:mm tz
  CAST('11:31 Europe/Moscow' AS TIME WITH TIME ZONE) AS t6,
  -- DD.MM.YYYY HH:mm
  CAST('04.12.2014 11:37' AS TIMESTAMP) AS dt1,
  -- MM/DD/YYYY HH:mm:ss
  CAST('12/04/2014 11:37:12' AS TIMESTAMP) AS dt2,
  -- DD.MM.YYYY HH:mm:ss.nnnn
  CAST('04.12.2014 11:31:12.1234' AS TIMESTAMP) AS dt3,
  -- YYYY-MM-DD HH:mm:ss.nnnn +hh:mm
  CAST('2014-12-04 11:31:12.1234 +03:00' AS TIMESTAMP WITH TIME ZONE) AS dt4,
  -- DD.MM.YYYY HH:mm:ss.nnnn tz
  CAST('04.12.2014 11:31:12.1234 Europe/Moscow' AS TIMESTAMP WITH TIME ZONE) AS dt5,
  CAST('now' AS DATE) AS d_now,
  CAST('now' AS TIMESTAMP) AS ts_now,
  CAST('now' AS TIMESTAMP WITH TIME ZONE) AS ts_now_tz,
  CAST('today' AS DATE) AS d_today,
  CAST('today' AS TIMESTAMP) AS ts_today,
  CAST('today' AS TIMESTAMP WITH TIME ZONE) AS ts_today_tz,
  CAST('tomorrow' AS DATE) AS d_tomorrow,
  CAST('tomorrow' AS TIMESTAMP) AS ts_tomorrow,
  CAST('tomorrow' AS TIMESTAMP WITH TIME ZONE) AS ts_tomorrow_tz,
  CAST('yesterday' AS DATE) AS d_yesterday,
  CAST('yesterday' AS TIMESTAMP) AS ts_yesterday,
  CAST('yesterday' AS TIMESTAMP WITH TIME ZONE) AS ts_yesterday_tz
FROM rdb$database
Note

Поскольку CAST('NOW' AS TIMESTAMP) всегда возвращает актуальные значения даты и времени, то она может использоваться для измерения временных интервалов и скорости выполнения кода в процедурах, триггерах и блоках кода PSQL.
Example 2. Использование CAST('NOW' AS TIMESTAMP) измерения длительности выполнения кода
EXECUTE BLOCK
RETURNS (ms BIGINT)
AS
DECLARE VARIABLE t1 TIME;
DECLARE VARIABLE n BIGINT;
BEGIN
  t1 = CAST('now' AS TIMESTAMP);
  /* Долгая операция  */
  SELECT COUNT(*) FROM rdb$types, rdb$types, rdb$types INTO n;
  /*======*/
  ms = DATEDIFF(MILLISECOND FROM t1 TO CAST('now' AS TIMESTAMP));
  SUSPEND;
END
См. также:

Литералы даты и времени, [fblangref-scalarfuncs-cast].

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

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

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

WHERE DOC_DATE < '31.08.2014'

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

В 1-м диалекте можно смешивать целые данные и числовые строки, строки неявно преобразуются в целое, если это будет возможно, например:

2 + '1'

корректно выполнится.

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

2 + CAST('1' AS SMALLINT)

Неявное преобразование типов при конкатенации

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

Example 1. Неявное преобразование типов при конкатенации
SELECT 30||' days hath September, April, June and November' CONCAT$
FROM RDB$DATABASE
CONCAT$
------------------------------------------------
30 days hath September, April, June and November