Sqlite3 中时间戳之间的差异(以秒为单位) [英] Difference in seconds between timestamps in Sqlite3

问题描述

是否有可能获得 Sqlite3 中两个 TIMESTAMP 值之间的差异(以秒为单位)?

Is it possible to get the difference (in seconds) between two TIMESTAMP values in Sqlite3?

例如，我尝试了以下查询:

For instance, I've tried the following query:

SELECT CURRENT_TIMESTAMP - my_timestamp FROM my_table;

而且我总是得到0".谁能告诉我我做错了什么?(注意，我已经验证 my_timestamp 确实是过去的.)

And I always get '0'. Can anyone tell me what I'm doing wrong? (Note, I have verified that my_timestamp is indeed in the past.)

推荐答案

知道了:

SELECT (julianday(CURRENT_TIMESTAMP) - julianday(my_timestamp)) * 86400.0) FROM my_table;

julianday 返回自公元前 4714 年 11 月 24 日格林威治中午以来的小数天数.然后我将差值乘以每天的秒数.

julianday returns the fractional number of days since noon in Greenwich on November 24, 4714 B.C. I then take the difference and multiply by the number of seconds per day.

这篇关于Sqlite3 中时间戳之间的差异(以秒为单位)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！