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

Благодаря способу хранения даты и времени с этими типами возможны арифметические операции вычитания из более поздней даты (времени) более раннюю.Дата представлена количеством дней с "нулевой даты" – 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 предоставляет ряд функций для получения информации о часовых поясах.

docnext count = 38

Виртуальная таблица `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

Операции, Использующие Значения Даты И Времени

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

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

docnext count = 38

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

Пакет RDB$TIME_ZONE_UTIL

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

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

Unicode

NCHAR

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

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

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

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

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

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

BINARY

CHAR

VARBINARY

VARCHAR

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

BOOLEAN

Оператор IS

Примеры BOOLEAN

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

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

BLOB

Подтипы BLOB

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

Массивы

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

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

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

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

Тип данных SQL_NULL

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

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

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

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

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

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

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

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

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

Пакет `RDB$TIME_ZONE_UTIL`

`NCHAR`

`BINARY`

`CHAR`

`VARBINARY`

`VARCHAR`

`BOOLEAN`

Оператор `IS`

Примеры `BOOLEAN`

`BLOB`

Тип данных `SQL_NULL`

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