Содержание


Обработка дат в Informix Dynamic Server

Назначим дату

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

Informix Dynamic Server (IDS) обеспечивает некоторые возможности для обработки дат. В данной статье рассматриваются существующие функции и дополнительные полезные функции.

Функции обработки дат IDS

IDS имеет два типа дат. Ими являются DATE и DATETIME. DATE представляет день, а DATETIME – конкретный момент с точностью от года до долей секунды. IDS предоставляет следующие функции для работы с этими типами:

  • DATE(VARCHAR(10)) returning DATE
    Эта функция принимает в качестве аргумента символьную строку в формате, указанном в переменной среды DBDATE, и возвращает тип DATE. По умолчанию используется формат для местоположения (locale) US English - "MDY4/".

  • DATE(DATETIME) returning DATE
    Функция, аналогичная предыдущей, но принимает в качестве аргумента DATETIME любой точности.

  • DATE(INTEGER) returning DATE
    Аргумент INTEGER представляет количество дней после 31 декабря 1899.

  • DAY(DATE) returning INTEGER
    Функция DAY возвращает день месяца как INTEGER.

  • DAY(DATETIME)
    Функция, аналогичная предыдущей, но принимает в качестве аргумента DATETIME любой точности.

  • EXTEND(DATE, precision) returning DATETIME
    Функция EXTEND корректирует точность аргумента DATE и возвращает соответствующий DATETIME. Поскольку это не совсем понятно, приведем пример:
    EXTEND(DATE(1), YEAR TO SECOND)

  • EXTEND(DATETIME, precision) returning DATETIME
    То же, что и выше, но работает с DATETIME вместо DATE.

  • MONTH(DATE) returning INTEGER
    MONTH извлекает номер месяца из аргумента DATE.

  • MONTH(DATETIME) returning INTEGER
    Эта функция извлекает месяц из DATETIME любой точности.

  • WEEKDAY(DATE) returning INTEGER
    Функция WEEKDAY возвращает INTEGER, представляющий день недели для указанной DATE. Ноль соответствует воскресенью, шесть – субботе.

  • WEEKDAY(DATETIME) returning INTEGER
    Функция, аналогичная предыдущей, но работающая с DATETIME.

  • YEAR(DATE) returning INTEGER
    Эта функция извлекает год из указанного аргумента DATE.

  • YEAR(DATETIME) returning INTEGER
    Аналогична предыдущей, но работает с DATETIME.

  • MDY(INTEGER, INTEGER, INTEGER) returning DATE
    Эта функция создает DATE на основе трех аргументов INTEGER. Эти аргументы указывают месяц, день и год соответственно. Обратите внимание, что год – это четырехзначное целое

  • TO_CHAR(DATE, VARCHAR(??)) returning VARCHAR(??)
    Эта функция принимает DATE, форматирует и возвращает строку, представляющую дату в указанном формате. Форматная строка может включать следующее:

    • %A: название дня недели
    • %B: название месяца
    • %d: день недели как десятичное число
    • %Y: год как четырехзначное число
    • %R: время в 24-часовой нотации
  • TO_CHAR(DATE, VARCHAR(??)) returning VARCHAR(??)
    Аналогична предыдущей.

  • TO_DATE(VARCHAR(??), VARCHAR(??)) returning DATE
    Обратная функция для TO_CHAR, использующая ту же форматную строку в качестве второго аргумента.

Кроме приведенных выше функций существуют две переменных среды, которые влияют на обработку дат:

  • DBDATE: Представляет пользовательский формат даты. Описан в "Справочном руководстве по SQL" (страницы 3-25).
  • DBCENTURY: Определяет, как расширять год при вводе даты: в виде двухзначного числа или четырехзначного. Приемлемыми значениями являются R, P, F и C. Они означают Current (текущий), Previous (предыдущий), Future (будущий) и Closest (ближайший) соответственно. Значение R является значением по умолчанию, если переменная DBCENTURY не установлена. DBCENTURY описывается в "Справочном руководстве по SQL" (страницы 3-22 для IDS 10.0).

Наконец, IDS определяет две встроенные функции, возвращающие текущую дату. CURRENT возвращает значение DATETIME, а TODAY – текущую дату.

Использование функций работы с датами

Описанные выше функции предоставляют функциональные возможности для ввода, вывода, форматирования и извлечения информации. Первое интересное применение, которое я хочу рассмотреть – это ввод дат в символьной строке.

Функция DATE() принимает в качестве входного аргумента символьную строку, но обрабатывает ее по-разному в зависимости от значений переменных DBDATE и DBCENTURY. Начнем с DBCENTURY.

Значением по умолчанию для DBCENTURY является R. Это означает, что век определяется веком текущей даты. В следующем примере используется местоположение по умолчанию US English:

SELECT date("9/2/92") FROM systables WHERE tabid = 1;

(constant)

09/02/2092

1 row(s) retrieved.

Если DBCENTURY установлен в P, то век будет установлен в ближайшее к текущей дате значение. Приведенный выше пример будет работать следующим образом:

SELECT date("9/2/92") FROM systables WHERE tabid = 1;

(constant)

09/02/1992

1 row(s) retrieved.

Остальные возможные варианты преобразования даты зависят от переменной среды DBDATE. Для местоположения US English по умолчанию используется "MDY4/". Это означает, что элементы строки даты разделяются символом "/" и идут в следующем порядке: месяц, день и год. Отмечу, что год указывается четырехзначным числом, но может быть расширен автоматически в соответствии с правилами, установленными в DBCENTURY. Вы можете изменить значение DBDATE для использования интернационального формата даты. Значение DBDATE может быть равным "Y4MD-". Кроме влияния на ввод дат в символьной строке это значение влияет также на преобразование даты в символьные строки:

select order_date from orders WHERE order_num = 1001;

order_date

1998-05-20

1 row(s) retrieved.

Для более детального отображения даты можно использовать функцию TO_CHAR и представить формат, как рассматривалось в предыдущем разделе:

select to_char(order_date, "%d %B %Y") from orders WHERE order_num = 1001;


(expression)  20 May 1998

1 row(s) retrieved.

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

SELECT YEAR(order_date) year, MONTH(order_date) month, COUNT(*) count
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;

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

Расширяемость IDS

IDS является первой базой данных, обладающей возможностью расширения для настройки под конкретные условия. Расширяемость стала доступной в IDS версии 9.01 (в 1997) и была улучшена в IDS версии 10. Вы можете создавать новые типы данных, новые функции, даже новые агрегатные функции. Функции и агрегатные функции могут быть написаны на языках C, Java™ или SPL. Если вы хотите узнать подробнее о том, как использовать расширяемость, обратитесь к ссылкам, приведенным в конце этой статьи.

Я обычно пишу пользовательские функции на C. Для целей этой статьи я использовал SPL. Преимущество SPL заключается в том, что он хорошо известен пользователям IDS. Он используется для написания встроенных процедур.

Функциональный индекс

IDS V9.x и выше поддерживает концепцию функционального индекса. Это означает, что вы можете создать индекс по результатам функции. Затем вы можете использовать этот индекс для ускорения обработки SQL-запросов, в которые входит эта функция.

Встроенные функции были созданы до добавления в IDS расширяемости. Так случилось, что вы не можете создать индекс по результатам встроенной функции. Но вы можете вызвать встроенную функцию в SPL-функции. Например, если бы вы хотели создать индекс по месяцу, вы могли бы создать следующую SPL-функцию:

CREATE FUNCTION udr_month(dt date)
RETURNING integer
WITH (NOT VARIANT)
RETURN MONTH(dt);
END FUNCTION;

С такой "оберточной" функцией вы можете создать следующий индекс:

CREATE INDEX orders_month_ids ON orders(udr_month(order_date));

Теперь вы можете выполнить SQL-команду, использующую этот индекс:

SELECT * FROM orders WHERE udr_month(order_date) = 6;

Новые функции обработки дат

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

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

CREATE FUNCTION day_of_year(dt date)
RETURNS integer
WITH(NOT VARIANT)

RETURN(1 + dt - MDY(1, 1, YEAR(dt)) );

END FUNCTION;

Ключевым моментом в реализации этой функции является знание того, что дата является целым числом, представляющим количество дней после 31 декабря 1899 года. Это значит, что если имеется дата для 1 января, мы можем использовать простое вычитание.

Вы можете использовать функцию в команде EXECUTE FUNCTION, устанавливая значение в функции или встроенной процедуре, либо использовать SQL-команду.

SELECT order_date, day_of_year(order_date) d_o_y FROM orders WHERE order_num = 1001;

order_date       d_o_y

05/20/1998         140

1 row(s) retrieved.

Функция определения недели года немного хитрее. Используется аналогичная формула, за исключением того, что нужно разделить результат на семь (количество дней в неделе):

CREATE FUNCTION week_of_year(dt date)
RETURNS integer
WITH(NOT VARIANT)

DEFINE day1 date;
DEFINE nbdays int;

LET day1 = MDY(1, 1, YEAR(dt));
LET nbdays = dt - day1;

RETURN 1 + (nbdays + WEEKDAY(day1)) / 7;

END FUNCTION;

Ключевым моментом в этой функции является смещение, предоставляемое встроенной функцией WEEKDAY. Функция WEEKDAY возвращает 0 для воскресенья, 6 для субботы. Если 1 января – это воскресенье, мы знаем, что 8 января – это следующее воскресенье, вторая неделя. Если 1 января начинается с любого другого дня, это означает что первая неделя короче. Встроенная функция WEEKDAY дает нам это смещение, позволяющее вычислить неделю года.

Функция week_of_year() имеет проблемы с последней неделей одного года и первой неделей следующего года. Например, 31 декабря 2004 была пятница, а 1 января 2005 года была суббота. Функция week_of_year определит следующее:

EXECUTE FUNCTION week_of_year(date("12/31/2004") );

(expression)

          53

1 row(s) retrieved.


EXECUTE FUNCTION week_of_year(date("1/1/2005"));

(expression)

           1

1 row(s) retrieved.

Эта проблема была учтена в стандарте ISO 8601. Джонатан Лефлер из IBM написал встроенные процедуры, реализующие стандарт. Его встроенные процедуры можно найти на Web-сайте международной группы пользователей Informix по адресу http://www.iiug.org. Ниже приведена реализация недели года в виде пользовательской функции:

CREATE FUNCTION day_one_week_one(yyyy INTEGER)
  RETURNING DATE
  WITH(NOT VARIANT)
  DEFINE jan1 DATE;
  LET jan1 = MDY(1, 1, yyyy);
  RETURN jan1 + MOD(11 - WEEKDAY(jan1), 7) - 3;
END FUNCTION;

CREATE FUNCTION iso8601_weeknum(dateval DATE DEFAULT TODAY)
RETURNING CHAR(8)
WITH(NOT VARIANT)
    DEFINE rv CHAR(8);
    DEFINE yyyy CHAR(4);
    DEFINE ww CHAR(2);
    DEFINE d1w1 DATE;
    DEFINE tv DATE;
    DEFINE wn INTEGER;
    DEFINE yn INTEGER;
    -- Calculate year and week number.
    LET yn = YEAR(dateval);
    LET d1w1 = day_one_week_one(yn);
    IF dateval < d1w1 THEN
        -- Date is in early January and is in last week of prior year
        LET yn = yn - 1;
        LET d1w1 = day_one_week_one(yn);
    ELSE
        LET tv = day_one_week_one(yn + 1);
        IF dateval >= tv THEN
            -- Date is in late December and is in the first week of next year
            LET yn = yn + 1;
            LET d1w1 = tv;
        END IF;
    END IF;
    LET wn = TRUNC((dateval - d1w1) / 7) + 1;
    -- Calculation complete: yn is year number and wn is week number.
    -- Format result.
    LET yyyy = yn;
    IF wn < 10 THEN
        LET ww = "0" || wn;
    ELSE
        LET ww = wn;
    END IF
    LET rv = yyyy || "-W" || ww;
    RETURN rv;
END FUNCTION;

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

CREATE FUNCTION iso8601_weekday(dateval DATE DEFAULT TODAY)
RETURNING CHAR(10)
WITH(NOT VARIANT)
  DEFINE rv CHAR(10);
  DEFINE dw CHAR(4);
  LET dw = WEEKDAY(dateval);
  IF dw = 0 THEN
    LET dw = 7;
  END IF;
  RETURN iso8601_weeknum(dateval) || "-" || dw;
END FUNCTION;

Продолжим наше рассмотрение обработки дат. Мы будем игнорировать стандарт ISO 8601 для упрощения функций. Как вы видели, можно легко адаптировать функции для поддержки стандарта.

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

CREATE FUNCTION week_of_month(dt date)
RETURNS integer
WITH(NOT VARIANT)

DEFINE day1 date;
DEFINE nbdays int;

LET day1 = MDY(MONTH(dt), 1, YEAR(dt));
LET nbdays = dt - day1;

RETURN 1 + (nbdays + WEEKDAY(day1)) / 7;

END FUNCTION;

Функции quarter()

Некоторые системы баз данных предоставляют функцию quarter(). Она обычно возвращает число от 1 до 4. Проблема заключается в том, что она подразумевает специальный год: стандартный календарный год.

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

Начнем с простой реализации квартала школьного года:

CREATE FUNCTION quarter(dt date)
RETURNS integer
WITH(NOT VARIANT)

RETURN (YEAR(dt) * 100) + 1 + (MONTH(dt) - 1) / 3;

END FUNCTION;

В этой реализации я включил год как часть квартала. Например, третий квартал 2005 года представлен как 200503. Это упрощает обработку, когда SQL-команда охватывает более чем один год. Вы могли бы решить создать другую реализацию, например возврат символьной строки вместо целого. Принимать решение нужно в зависимости от ваших требований.

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

В следующем коде реализован год, начинающийся 1 сентября. Легко адаптировать этот код для любой начальной даты.

CREATE FUNCTION bizquarter(dt date)
RETURNS integer
WITH(NOT VARIANT)

DEFINE yr int;
DEFINE mm int;

LET yr = YEAR(dt);
LET mm = MONTH(dt) + 4; -- sept. to jan. is 4 months
IF mm > 12 THEN
  LET yr = yr + 1;
  LET mm = mm - 12;
END IF

RETURN (yr * 100) + 1 + (mm - 1) / 3;

END FUNCTION;

Дополнительная обработка в этой функции по сравнению с функцией quarter() заключается в том, что в ней текущий месяц перемещается вперед на несколько месяцев, для того чтобы расчет номера квартала соответствовал бизнес-году.

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

Теперь, поскольку доступны новые функции, вы можете использовать их в SQL-командах, как будто они встроены в IDS. Например:

SELECT quarter(order_date) quarter, count(*) count
FROM orders
GROUP BY 1
ORDER BY 1;

    quarter            count

     199802               16
     199803                7

2 row(s) retrieved.

Можно создать индексы по функциям:

CREATE INDEX orders_week_ids
ON orders(week_of_year(order_date));

Это предоставляет вам широкие возможности при получении информации из базы данных, которую вы ищете. Расширяемость IDS может быть полезна во многих других областях. Обратитесь к разделу "Ресурсы" для поиска других статей по данному предмету.

Заключение

Расширить возможности IDS по обработке дат легко. Результат – меньший объем кода и потенциально более маленькие SQL-команды для выполнения, что ведет к повышению производительности. База данных – это не продукт массового спроса. Это стратегический продукт, который может дать вам преимущества в бизнесе.

Используя рассмотренные в статье технологии обработки дат, вы можете адаптировать IDS под ваши условия. Если предоставленные здесь функции работы с датами не совсем подходят для вас, можно легко изменить их. Гибкость IDS означает, что его возможности нужно принимать во внимание еще на этапе проектирования приложения. В результате можно получить более высокую производительность и масштабируемость, более простую реализацию.


Ресурсы для скачивания


Похожие темы

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management
ArticleID=108396
ArticleTitle=Обработка дат в Informix Dynamic Server
publish-date=12222005