Сравнение даты и времени Sqlite с отметкой времени unix
В документации Sqlite говорится:
SQLite не имеет типа данных DATETIME. Вместо этого даты и время могут быть сохранены любым из следующих способов:
- В виде строки TEXT в формате ISO-8601. Пример: «2018-04-02 12:13:46».
- В виде INTEGER числа секунд с 1970 года (также известного как «время unix»).
...
поэтому я решил использовать временную метку INTEGER unix:
import sqlite3, time
conn = sqlite3.connect(':memory:')
conn.execute("CREATE TABLE data(datetime INTEGER, t TEXT);")
conn.execute("INSERT INTO data VALUES (CURRENT_TIMESTAMP, 'hello')")
Почему следующий запрос не возвращает результата?
ts = int(time.time()) + 31*24*3600 # unix timestamp 1 month in the future
print(list(conn.execute("SELECT * FROM data WHERE datetime <= ?", (ts, ))))
В более общем смысле, как выполнить запрос SELECT со сравнением с временной меткой unix с помощью Sqlite?
PS:
Я уже читал Сравнение SQLite DateTime и подобные вопросы, которые предлагают другие методы сравнения, но здесь я хотел бы точно обсудить, почему это сравнение временных меток unix не работает.
По соображениям производительности я хотел бы:
- сделать запрос, который сравнивает целые числа (что очень быстро, если много строк):
WHERE datetime <= unix_timestamp, - избегайте преобразования
unix_timestampв строку, а затем сравнивайтеdatetimeс этой строкой (думаю, это будет намного медленнее)
- сделать запрос, который сравнивает целые числа (что очень быстро, если много строк):
@mama Использует ли Sqlite временные метки unix в миллисекундах или секундах?
python использует секунды по умолчанию. idk о sqlite, но обычно по умолчанию используются миллисекунды
@DinoCoderSaurus Почему? Строка вставляется с текущей отметкой времени. Так что запрос с datetime <= timestamp_1_month_in_the_future должен работать.
@DinoCoderSaurus IIRC, он также содержит ЧЧ: ММ: СС и, возможно, миллисекунды? У вас есть источник об этом?
Ответы 1
Вы используете CURRENT_TIMESTAMP при вставке новых строк.
Это означает, что в вашем столбце значения не хранятся в виде временных меток unix, потому что CURRENT_TIMESTAMP возвращает текущую дату в формате YYYY-MM-DD hh:mm:ss.
Вы можете преобразовать временную метку unix в дату и время в формате YYYY-MM-DD hh:mm:ss с помощью функции datetime() и модификатора unixepoch:
conn.execute("SELECT * FROM data WHERE datetime <= datetime(?, 'unixepoch')", (ts, ))
Если ваша временная метка unix содержит миллисекунды, вы должны удалить их:
conn.execute("SELECT * FROM data WHERE datetime <= datetime(? / 1000, 'unixepoch')", (ts, ))
Или вы можете преобразовать строку datetime в столбце datetime в метку времени unix с помощью функции strftime():
conn.execute("SELECT * FROM data WHERE strftime('%s', datetime) + 0 <= ?", (ts, ))
Если вы хотите хранить целочисленные значения в столбце, используйте strftime() следующим образом:
INSERT INTO data VALUES (strftime('%s', CURRENT_TIMESTAMP) + 0, 'hello')
Спасибо @forpas. Из соображений производительности я хотел бы сделать запрос, который сравнивает целые числа (что очень быстро, если много строк): WHERE datetime <= unix_timestamp и не преобразовывать unix_timestamp в строку, а затем сравнивать дату и время с этой строкой (думаю, это будет далеко помедленнее?). Что вы думаете?
CURRENT_TIMESTAMP возвращает текущую дату в формате ГГГГ-ММ-ДД чч:мм:сс, может быть, в этом причина, у вас есть источник/ссылка для этого для дальнейшего использования? Кроме того, как я могу получить текущую временную метку в виде временной метки unix вместо строки?
Спасибо @forpas! P.S. Почему + 0?
strftime() возвращает строку, а +0 выполняет неявное преобразование в целое число.
Я не знаю, будет ли это полезно, но когда я использую время unix в миллисекундах, как int, это приведет к переполнению, лучше использовать long.