Difference in seconds between timestamps in Sqlite

问题:

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;

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

回答1:

Got it:

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

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.

回答2:

Another variant to the nearest second

CAST(strftime('%s', CURRENT_TIMESTAMP) as integer) - 
CAST(strftime('%s', my_timestamp) as integer)