Форматная строка для представления даты и времени в разных системах

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

В таблице ниже приведена форматная строка для получения даты и времени в следующем виде:

2016.06.14 23:10:06

(14 июня 2016 год, 23 часа 10 минут 6 секунд. Год из 4 цифр. Месяц, день месяца, часы, минуты и секунды из двух цифр, дополняются нулями. Часы в 24 формате).

Система шаблонов Форматная строка
date (Linux) %Y.%m.%d %H:%M:%S
C/C++ %Y.%m.%d %H:%M:%S
Python %Y.%m.%d %H:%M:%S
SQLite %Y.%m.%d %H:%M:%S
ISO 8601 YYYY.MM.DD hh:mm:ss
Qt yyyy.MM.dd hh:mm:ss
C# yyyy.MM.dd HH:mm:ss
Реклама

Qt и SQLite

При использовании библиотеки Qt и встроенной в нее СУБД SQLite возникает желание проверять запросы вне исходного кода программы. Желательно проверять запросы с той же версией, что встроена в Qt.

Возьмем исходный код Qt. На данный момент я использую версию 5.4.0 и установил исходный код при установке Qt. Все действия происходят в терминале Линукса.

Перейдем в каталог с исходным кодом SQLite:

cd /opt/Qt5.4.0/5.4/Src/qtbase/src/3rdparty/sqlite

Соберем клиент SQLite и поместим его в /opt:

sudo gcc shell.c sqlite3.c -lpthread -ldl -o  /opt/sqlite3

Проверим версию:

/opt/sqlite3 --version

Готово.

SQLite: случайные значения первичного ключа

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

Мною были предприняты две попытки, которые дали отрицательный результат.

Первая неудачная попытка

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

create table table1 (
  id integer primary key default (random()),
  field1 integer
);

Таблица создается без ошибок, но при вставке записей значение первичного ключа задается как обычно, то есть условие default игнорируется.

Вторая неудачная попытка

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

create table table1 (
  id integer primary key,
  field1 integer
);

create trigger random_primary_key
instead of insert on table1
when new.id is not null
begin
  insert into table1 values (
    random(),
    new.field1
  );
end;

Но SQLite выдал ошибку:

Error: near line 30: cannot create INSTEAD OF trigger on table: table1

Оказалось, что триггеры, которые заменяют записи, могут работать только с представлениями (view). Что удалось узнать только из исходного кода SQLite. Так как в документации это не разъясняется. В файле src/trigger.c в том месте где генерируется выше названная ошибка сказано:

INSTEAD of triggers are only for views and views only support INSTEAD of triggers.

Перевод: триггер INSTEAD только для представлений и только представления поддерживают этот триггер.

Удачная попытка

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

create table table1 (
  id integer unique not null default (random()),
  field1 integer
);

У меня были сомнения, будут ли работать внешние ключи на уникальном поле. Эксперимент дал положительный результат.

Определение версии SQLite в Qt

Исходный код SQLite находится в каталоге

<Каталог Qt>/Src/qtbase/src/3rdparty/sqlite

версию можно найти в файле sqlite3.c или sqlite3.h.

Заглянув в репозиторий Qt можно узнать версию SQLite для различных версий Qt, например Qt 5.3.0 включает SQLite 3.8.4.3.

История версий SQLite.

Функции для работы с датой и временем в 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 (на английском).