Функции для работы с датой и временем в SQLite

SQLite поддерживает пять функций для работы с датой и временем:

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, ...)

Все функции принимают в качестве аргумента строку времени (timestring). За строкой времени следуют необязательные модификаторы (modifier). Функция strftime(), кроме этих аргументов, принимает строку формата  (format) в качестве первого аргумента.

Для представления даты и времени функции используют подмножество форматов из стандарта ISO 8601.  Функция date() возвращает дату в формате «YYYY-MM-DD». Функция time() возвращает время в формате «HH:MM:SS». Функция datetime() возвращает «YYYY-MM-DD HH:MM:SS». Функция julianday() возвращает Юлианскую дату — количество дней с полудня 24 ноября 4714 года до н. э. григорианского календаря по Гринвичу.

Функция strftime() возвращает строку в соответствии со строкой формата, которая указывается в качестве первого аргумента этой функции. Строка формата поддерживает наиболее распространенные шаблоны из функции strftime() стандартной библиотеки Си и два новых шаблона: %f и %j. Полный список поддерживаемых шаблонов:

Шаблон	Значение
%d	день месяца: 01…31
%f	доли секунд: SS.SSS
%H	час: 00…23
%j	день года: 001…366
%J	Юлианская дата
%m	месяц: 01…12
%M	минута: 00…59
%s	количество секунд с 1 января 1970 года
%S	секунда: 00…59
%w	день недели: 0…6 (0 — воскресенье)
%W	неделя года: 00…53
%Y	год: 0000…9999
%%	%

Любую функцию можно заменить функцией strftime():

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

Другие функции, кроме strftime(), предоставляются для обеспечения удобства и эффективности.

Строка времени

Строка времени (timestring) может иметь один из следующих форматов:

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. При этом используется всемирное координированное время (UTC). Формат 12 — Юлианской дата, представленная числом с плавающей точкой. В форматах 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) прибавляют указанное количество часов, минут, секунд, месяцев или лет к дате и времени. Если первоначальная дата в формате YYYY-MM-DD, то модификатор ‘±NNN month’ добавляет ±NNN месяцев к значению MM, а затем нормализует результат. Например, для даты 2001-03-31 указан модификатор ‘+1 month’, это дает 2001-04-31, но в апреле только 30 дней, поэтому дата нормализуется в 2001-05-01. Аналогичный эффект возникает, когда в первоначальной дате указано 29 февраля високосного года и применяется модификатор ‘±N years’, где N не кратно четырем.

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

Модификатор ‘weekday N’ продвигает дату вперед, где номер дня недели — N. 0 — это воскресенье, 1 — понедельник и так далее.

Модификатор ‘unixepoch’ (11) работает только если строка времени в формате DDDDDDDDDD. Модификатор заставляет функции интерпретировать DDDDDDDDDD не как Юлианскую дату, как это должно быть в обычной ситуации, а как UNIX-время — количество секунд с 1970 года. Если модификатор ‘unixepoch’ не следует после строки времени в формате DDDDDDDDDD, выражающей количество секунд с 1970 года, или если другой модификатор стоит между модификатором ‘unixepoch’ и строкой времени, то результат будет неопределенным. Из-за ограничений, налагаемых использованием 64-разрядных целых чисел, модификатор ‘unixepoch’ работает только для дат между 0000-01-01 00:00:00 и 5352-11-01 10:52:47 (UNIX-время от −62167219200 до 10675199167 секунд включительно).

Модификатор ‘localtime’ предполагает, что строка времени соответствует всемирному координированному времени (UTC), и приводит время к локальному. Модификатор ‘utc’ противоположен ‘localtime’, предполагает, что время соответствует локальному, и приводит время к UTC.

Примеры

Вычисляет текущую дату.

SELECT date('now');

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

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

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

SELECT datetime(1092941466, 'unixepoch');

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

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:

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

Предостережения и ошибки

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

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

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

Все внутренние вычисления происходят в соответствии с Григорианским календарем. Кроме того, предполагается, что каждый день продолжительностью ровно 86 400 секунд.

Ссылки

Функции даты и времени в официальной документации SQLite (на английском).