17

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

Tom
  • 18,685
  • 15
  • 71
  • 81

2 Answers2

18

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.

Tom
  • 18,685
  • 15
  • 71
  • 81
  • 1
    Yes it does. Running `select (julianday("2015-01-01T12:00:00") - julianday("2015-01-01T11:00:00")) * 24;` yields one hour as expected, or close enough to it: `0.99999999627471`. – Tom Nov 09 '15 at 17:04
  • If you're only getting a difference of dates, make sure the input to `julianday` actually is a timestamp and not just a datestamp. – Tom Nov 09 '15 at 17:05
10

Another variant to the nearest second

CAST(strftime('%s', CURRENT_TIMESTAMP) as integer) - 
CAST(strftime('%s', my_timestamp) as integer)
Habibutsu
  • 592
  • 1
  • 8
  • 20