Работа с часовыми поясами(таймзонами) в php и mysql

Сложности работы с часовыми поясами, думаю, возникают у каждого кто сталкивается с этим впервые. Для примера рассмотрим создание планировщика: пользователь составляет расписание с заданиями – указывает время когда задание начинается и когда заканчивается.
Но, т.к. пользователи находятся в разных странах и, соответственно, в разных часовых поясах, возникает вопросы: “А как хранить время?” и “И как искать задачи, которые надо запустить/остановить сейчас?”
Continue reading “Работа с часовыми поясами(таймзонами) в php и mysql”

Периодические переменные в MySQL

В 1С есть очень полезная фича – периодические реквизиты, с помощью них можно хранить не только значение, но и всю историю его изменения. Например, если мы храним значение курса бакса по отношению к рублю, то хранится не только актуальное значение, но и значение за вчера, позавчера и т.д.(период хранения может быть любой). В MySQL, к сожалению, такой фичи нет(наверное и не надо). Приходится все делать самому ;).

Рассмотрим на примере. Допустим у нас есть простейшая система заявок основанная на таблице TTroubleticket.

Таблица TTroubleticket
fkey номер заявки
fcontact контактная информация подавшего заявку
ftext текст заявки

Кроме контактной информации и текста заявки необходимо хранить статус заявки со всей историей его изменений. Сразу напрашивается такое решение: хранить статус в другой таблице со всей историей изменений.

Таблица TStatusHistory
fkey
f_troubleticket ссылка на запись в TTroubleticket
fvalue значение
fsdate дата присвоения значения

Тогда чтобы получить текущее значение нам надо выполнить следующий запрос:

    SET @f_troubleticket = 10;
    SELECT
        tt.fkey, tt.fcontact, tt.ftext,
        (SELECT
            fvalue
         FROM
            TStatusHistory
         WHERE
            f_troubleticket = tt.fkey
         ORDER BY
            fsdate DESC
         LIMIT 0,1
        ) AS fstatus
    FROM
        TTroubleticket AS tt
    WHERE
        fkey = @f_troubleticket
    LIMIT 0, 1


Все складно, все хорошо, ничего лишнего, а при желании мы всегда сможем просмотреть всю историю изменений статуса. Первые трудности начнутся тогда, когда мы попытаемся получить значение на какую-нибудь дату. Поэтому добавим еще одно поле fedate – дата завершения действия значения. Пускай в этом поле будет храниться избыточная информация, но она нам очень упростит жизнь пригодится.
Таблица TStatusHistory
fkey
f_troubleticket ссылка на запись в TTroubleticket
fvalue значение
fsdate дата присвоения значения
fedate дата завершения действия значения
Теперь чтобы получить значение на дату нам надо немного изменить запрос:

    SET @f_troubleticket = 10;
    SET @dt = 20090401000000; # 01 апреля 2009г 00:00
    SELECT
        tt.fkey, tt.fcontact, tt.ftext,
        (SELECT
            fvalue
         FROM
            TStatusHistory
         WHERE
            f_troubleticket = tt.fkey
            AND @dt BETWEEN fsdate AND fedate
         ORDER BY
            fsdate DESC
         LIMIT 0,1
        ) as fstatus
    FROM
        TTroubleticket AS tt
    WHERE
        fkey = @f_troubleticket
    LIMIT 0, 1


С этой сложностью справились. Но время идет, а система совершенствуется: нам понадобился такой параметр как тип заявки и тоже периодический. Фигня!-добавляем еще таблицу.
Таблица TTypeHistory
fkey
f_troubleticket ссылка на запись в TTroubleticket
fvalue значение
fsdate дата присвоения значения
fedate дата завершения действия значения
Зарос преобразуется:

    SET @f_troubleticket = 10;
    SET @dt = 20090401000000; # 01 апреля 2009г 00:00
    SELECT
        tt.fkey, tt.fcontact, tt.ftext,
        (SELECT
            fvalue
          FROM
            TStatusHistory
          WHERE
            f_troubleticket = tt.fkey
            AND @dt BETWEEN fsdate AND fedate
          ORDER BY
            fsdate DESC
          LIMIT 0,1
        ) AS fstatus,
        (SELECT
            fvalue
          FROM
            TTypeHistory
          WHERE
            f_troubleticket = tt.fkey
            AND @dt BETWEEN fsdate AND fedate
          ORDER BY
            fsdate DESC
          LIMIT 0,1
        ) AS ftype
    FROM
        TTroubleticket AS tt
    WHERE
        fkey = @f_troubleticket
    LIMIT 0, 1


И со временем у нас может появиться куча дополнительных таблиц, т.е. система усложняется, а в этом нет ничего хорошего.
Усовершенствуем наш подход – не будем создавать отдельные таблицы для каждой периодической переменной, все периодические переменные будем хранить в одной таблице.
Таблица THistory
fkey
f_troubleticket ссылка на запись в TTroubleticket
fstatus статус
ftype тип
fsdate дата присвоения значения
fedate дата завершения действия значения
Также, все актуальные значения будем хранить в основной таблице.
Таблица TTroubleticket
fkey номер заявки
fcontact контактная информация подавшего заявку
ftext текст заявки
fstatus текущей статус
ftype текущий тип
И теперь когда нам понадобится получить актуальные значения, нам достаточно выполнить простой запрос:
    SET @f_troubleticket = 10;
    SELECT
        fkey, fcontact, ftext,
        fstatus,
        ftype
    FROM
        TTroubleticket
    WHERE
        fkey = @f_troubleticket
    LIMIT 0, 1

Ну, а если нам понадобится получить значения на дату, воспользуемся конструкцией LEFT JOIN:

    SET @f_troubleticket = 10;
    SET @dt = 20090401000000; # 01 апреля 2009г 00:00
    SELECT
        tt.fkey, tt.fcontact, tt.ftext,
        his.fstatus,
        his.ftype
    FROM
        TTroubleticket AS tt
            LEFT JOIN TTroubleticketHistory as his
                ON his.f_troubleticket = tt.fkey
                     AND @dt BETWEEN his.fsdate AND his.fedate
    WHERE
        tt.fkey = @f_troubleticket
    LIMIT 0, 1


Вот собственно и всё. Вроде бы получилось очень даже не сложное решение.
А как вы храните периодические значения?
p.s. За идею спасибо А. Матвееву.