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

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

В СУБД 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.

docnext count = 33

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