Как сохранить текущую временную метку в SQLite как тики?

У меня есть база данных SQLite, где хранятся даты как тики. Я не использую формат ISO8601 по умолчанию. Скажем, у меня есть таблица, определяемая следующим образом:

CREATE TABLE TestDate (LastModifiedTime DATETIME)

Используя SQL, я хочу вставить текущую дату и время. Если я выполняю любое из приведенных ниже инструкций, я получаю дату и время, хранящиеся в виде строки, а не в тиках.

INSERT INTO TestDate (LastModifiedTime) VALUES(CURRENT_TIMESTAMP)
INSERT INTO TestDate (LastModifiedTime) VALUES(DateTime('now'))

Я посмотрел SQLite documenation, но я, похоже, не нашел никакой возможности получить текущую метку времени в тиках.

Я могу, конечно, определить параметр в С# и сохранить значение как System.DateTime. Это приводит к тому, что datetime будет храниться в базе данных по тикам.

То, что я хотел бы сделать, - это возможность вставлять и обновлять текущую временную метку непосредственно из инструкции SQL. Как мне это сделать?

Edit:

Причина, по которой я хочу, чтобы данные, хранящиеся в качестве тиков в базе данных, заключались в том, что даты хранятся в том же формате, который хранится поставщиком данных ADO.Net, и поэтому, когда данные также запрашиваются с использованием ADO.Net провайдера, он правильно извлекается как тип System.DataTime.Net.

Ответ 1

После дальнейшего изучения документации SQLite и другой информации, найденной при конвертации числа дней, я придумал следующую формулу, которая появляется выведите правильные результаты:

INSERT INTO TestDate(LastModifiedTime)
    VALUES(CAST((((JulianDay('now', 'localtime') - 2440587.5)*86400.0) + 62135596800) * 10000000 AS BIGINT))

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

Update:

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

Ответ 2

Эта особая странность SQLite вызвала у меня много мучений.

Простой способ - сохранить и получить в качестве обычной метки времени

create table TestDate (
        LastModifiedTime datetime
);
insert into TestDate (LastModifiedTime) values (datetime('now'));
select datetime(LastModifiedTime), strftime('%s.%f', LastModifiedTime)  from TestDate;

Выход: 2011-05-10 21: 34: 46 | 1305063286.46.000

Болезненный путь - сохранение и извлечение в качестве метки времени UNIX

Вы можете использовать strftime для извлечения значения в тиках. Кроме того, чтобы сохранить временную метку UNIX (примерно эквивалентную тикам), вы можете окружать количество секунд в одиночных кавычках.

 insert into TestDate (LastModifiedTime) values ('1305061354');

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

 select datetime(LastModifiedTime, 'unixepoch') FROM TestDate;

Чтобы сохранить текущую дату и время, используйте strftime ('% s', 'now').

 insert into TestDate (LastModifiedTime) VALUES (strftime('%s', 'now'));

Полный пример:

create table TestDate (
        LastModifiedTime datetime
);      
insert into TestDate (LastModifiedTime) values (strftime('%s', 'now'));
select datetime(LastModifiedTime, 'unixepoch') from TestDate;

При выполнении sqlite3 этот script с печатью:

2011-05-10 21:02:34 (or your current time)

Ответ 3

Далее будет возвращено число миллисекунд с эпохи UNIX:

SELECT (strftime('%s', 'now') - strftime('%S', 'now') + strftime('%f', 'now')) * 1000 AS ticks

Он работает, захватывая количество секунд с момента Unix Epoch (% s), вычитая количество секунд в текущее время (% S), добавляя количество секунд с десятичными знаками ( % f) и умножить результат на 1000 для преобразования от секунд до миллисекунды.

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