Функции даты и времени в SQLite

Как известно, в базе данных SQLite нет типа данных для хранения даты или времени. Предполагается хранить дату и время либо в строковом поле, либо в виде числа, т.е. использовать один из трех вариантов:

• TEXT - Для хранения даты/времени в формате "YYYY-MM-DD HH:MM:SS.SSS" (подробнее см.ниже)
• REAL - Для записи даты/времени в виде числа - Юлианского дня
• INTEGER - Чтобы сохранить дату/время как время Unix (число секунд с 1970-01-01 00:00:00 UTC)

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

Функции даты и времени в SQLite

Для работы с датой и временем SQLite предлагает 5 встроенных функций:

1. date(timestring, modifier, modifier, ...)
2. time(timestring, modifier, modifier, ...)
3. datetime(timestring, modifier, modifier, ...)
4. julianday(timestring, modifier, modifier, ...)
5. strftime(format, timestring, modifier, modifier, ...)

Все пять функций даты и времени принимают в качестве аргумента строку времени. За строкой времени могут следовать один или несколько модификаторов. Функция strftime() function также принимает строку формата в качестве первого аргумента.

Функции даты и времени используют стандарт ISO-8601 для строк формата. Функция date() возвращает дату в формате: YYYY-MM-DD. Функция time() возвращает время в формате HH:MM:SS. Функция datetime() возвращает "YYYY-MM-DD HH:MM:SS". Функция julianday() возвращает Юлианский день - число дней, прошедших начиная с полудня понедельника, 1 января 4713 до н. э. юлианского календаря.

Функция strftime() возвращает дату, отформатированную в соответствии со строкой формата, указанной в качестве первого аргумента. Строка формата поддерживает основные замены, которые есть в функции strftime() из стандартной библиотеки C плюс еще 2 замены: %f и %J. Ниже список всех корректных замен функции strftime() в SQLite:

%d		День месяца: 00
%f		Доли секунды: SS.SSS
%H		час: 00-24
%j		день года: 001-366
%J		Юлианский день
%m		месяц: 01-12
%M		минуты: 00-59
%s		количество секунд с 1970-01-01 (unix timestamp)
%S		секунды: 00-59
%w		день недели 0-6 где Воскресенье==0
%W		неделя года: 00-53
%Y		год: 0000-9999
%%		%

Обратите внимание, что все другие функции даты и времени могут быть выражены через strftime():

Функция		Эквивалент strftime()
date(...)		strftime('%Y-%m-%d', ...)
time(...)		strftime('%H:%M:%S', ...)
datetime(...)		strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...)		strftime('%J', ...)

Основная причина использования других функций вместо strftime() - это удобство и эффективность.

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

Для того, чтобы SQlite правильно понимал и работал с датой (сортировал, сравнивал и т.д.), строка содержащая дату и время должна быть в одном из следующих форматов:

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

В форматах с 5 по 7 символ "T" означает разделитель даты и времени, как это требуется стандартом ISO-8601. В форматах с 8 по 10 указано только время, при этом считается, что дата равна 2000-01-01. Формат 11 - строка 'now' преобразуется в текущую дату и время как полученные методом xCurrentTime объекта sqlite3_vfs. Значение 'now' функций даты и времени всегда возвращают одно и то же значение для нескольких вызовов в пределах одного и того же sqlite3_step(). Формат 12 - это Юлианский день выраженный в виде дробного числа.

Обратите внимание: Функции даты и времени в SQLite используют Всемирное координированное время (UTC). Чтобы получить локальные дату и время, следует использовать модификатор localtime, например, чтобы получить текущую локальную дату и время, можно воспользоваться таким запросом:

select datetime('now','localtime')

Форматы со 2 по 10 могут дополнительно сопровождаться индикатором часового пояса в формате "[+-]HH:MM" или просто указанием часовой зоны "Z". Функции даты и времени используют UTC или "zulu" время (время по Гринвичу), таким образом суффикс "Z" не обязателен, если вы используете такое время. Любой не пустой суффикс "HH:MM" вычитается из указанной даты и времени для вычисления времени zulu. Например, все следующие строки эквивалентны:

2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00
2456572.84952685

В форматах 4, 7 и 10 значение доли секунды SS.SSS может содержать одну или несколько цифр после запятой. В примерах показаны ровно три цифры, поскольку только первые три цифры значимы для результата, но входная строка может иметь меньше или больше трех цифр, при этом функции даты и времени будут работать правильно. Аналогично, формат 12 отображается с 10 значащими цифрами, но функции даты/времени действительно принимают столько цифр, сколько необходимо для представления числа в юлианский день.

Модификаторы даты и времени

За строкой времени может следовать один или более модификаторов, изменяющих дату и/или время. Каждый модификатор - это преобразование, которое применяется к значению времени слева от него. Модификаторы применяются слева направо, порядок важен. Доступны следующие модификаторы.

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc

Первые шесть модификаторов (от 1 до 6) просто добавляют указанное количество времени к дате и времени, заданным предыдущим временем и модификаторами. Символ "s" в конце имен модификаторов является необязательным. Обратите внимание, что "±NNN months" работает путем преобразования оригинальной даты в формат YYYY-MM-DD, затем добавляется ±NNN месяцев к MM значению, затем результат нормализуется. Например, дата 2001-03-31 модифицированная с помощью '+1 month' изначально дает 2001-04-31, но в апреле только 30 дней, поэтому дата нормализуется и становится 2001-05-01. Аналогичный эффект происходит когда дата February 29 високосного года и используется модификатор ±N years, где N не кратно четырем.

Модификаторы "start of" (с 7 по 9) сдвигает дату назад, на начало месяца, года или дня.

Модификатор "weekday" переносит дату вперед на следующую дату, где номер дня недели равен N. Воскресенье равно 0, Понедельник равен 1 и т.д.

Модификатор "unixepoch" (11) работает только в случае использования строки времени в формате DDDDDDDDDD. Этот модификатор заставляет dddddddddddd интерпретироваться не как номер Юлианского дня, как это обычно было бы, а как Unix Time - количество секунд с 1970. Если модификатор "unixepoch" получит значение в отличном от формата DDDDDDDDDD который будет означать количество секунд с 1970 или если предыдущие модификаторы, которые использованы до "unixepoch", преобразуют значение в отличное от DDDDDDDDDD тогда результат будет не корректным. В SQLite версий до 3.16.0 (2017-01-02), модификатор "unixepoch" работает только для дат от 0000-01-01 00:00:00 до 5352-11-01 10:52:47 (unix times с -62167219200 до 106751991167).

Модификатор "localtime" (12) ожидает, что строка времени слева является UTC и настраивает строку времени так, чтобы она отображала локальное время. Если "localtime" получает строку времени не в UTC, тогда результат будет не корректным. Модификатор "utc" - противоположность модификатору "localtime". "utc" ожидает, что срока слева - локальное время и преобразует его в UTC. Если строка не будет локальным временем, тогда "utc" вернет некорректный результат.

Примеры использования функций даты/времени в SQLite

Определить текущую дату.

SELECT date('now');

Вычислить последний день текущего месяца.

SELECT date('now','start of month','+1 month','-1 day');

Вычислить дату и время имея на входе метку времени unix 1092941466.

SELECT datetime(1092941466, 'unixepoch');

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

SELECT datetime(1092941466, 'unixepoch', 'localtime');

Получить текущую unix метку времени.

SELECT strftime('%s','now');

Вычислить количество дней с момента подписания Декларации Независимости США.

SELECT julianday('now') - julianday('1776-07-04');

Вычислить количество секунд с определенного момента в 2004 году:

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

Вычислить дату первого вторника октября текущего года.

SELECT date('now','start of year','+9 months','weekday 2');

Вычислить время с эпохи unix в секундах (аналогично strftime('%s','now') не считая дробной части):

SELECT (julianday('now') - 2440587.5)*86400.0;

Предупреждения и ошибки

Вычисление местного времени в значительной степени зависит от прихоти политиков и, таким образом, трудно получить правильное время для всех часовых поясов. В этой реализации стандартная функция библиотеки C localtime_r() используется для вычисления местного времени. Функция localtime_r() обычно работает только в течение нескольких лет между 1970 и 2037. Для дат за пределами этого диапазона SQLite пытается сопоставить год с эквивалентным годом в пределах этого диапазона, выполнить расчет, а затем сопоставить год назад.

Все функции работают в пределах дат от 0000-01-01 00:00:00 до 9999-12-31 23:59:59 (юлианские дни от 1721059.5 до 5373484.5). Для даты вне этого диапазона, результаты этих функций не определены.

Не Vista платформы Windows поддерживают только один набор правил. Vista поддерживает только два. Поэтому на этих платформах исторические расчеты DST будут неверными. Например, в США, в 2007 году - правила перехода на летнее время изменились. Не Vista платформы Windows применят правила DST 2007 за все предыдущие годы. Vista делает несколько лучше получить результаты исправить обратно в 1986 году, когда правила были также изменены.

Все внутренние вычисления предполагают Григорианский календарь. Также предполагается, что каждый день содержит ровно 86400 секунд.