CODEWITHSUNDEEP
sqlstringsqlitedatetimedate-arithmetic
Luke Hoffman
Luke Hoffman

Reputation: 25

How do I get a the time difference in seconds in sqlite?

I'm attempting to get the difference of time in seconds between two timestamps on an sqlite db (running sqlite 3.12.1) I've tried several things I have found here on stack overflow but nothing seems to be working as expected.

for reference I've tried creating the datetime field as both an integer and a text field and I get the same results. Below is the query I'm running and the results set that it produces.

essentially I'm attempting to replicate the datediff() function in SQL Server

 select cast(JulianDay('now') - JulianDay(last_upd_dt) * 24 * 60 * 60 As Integer) "gives me null value",
 last_upd_dt as "from date",
 datetime('now') as "To date this will be another field in the future",
 (datetime('now') - last_upd_dt  ) as "Gives me 2012 on all records"
 from sqlite_db

Upvotes: 1

Views: 2484

Answers (2)

forpas
forpas

Reputation: 164099

The problem with your code is that you should use parentheses around the difference of the dates and then multiply by 24 * 60 * 60.
But there is a much simpler way to get the difference between 2 dates in seconds:

select strftime('%s', 'now') - strftime('%s', last_upd_dt)
from sqlite_db

The function strftime() with the '%s' modifier returns the unix timestamp (the number of seconds since '1970-01-01') of a date (provided it is formatted as YYYY-MM-DD hh:mm:ss).

Upvotes: 2

GMB
GMB

Reputation: 222482

Your date value should be stored as a TEXT datatype, in one of the formats supported by SQLite, such as YYYY-MM-DD HH:MI:SS.

Then, you can do

select (julianday('now') - julianday(last_upd_dt)) * 24 * 60 * 60 as date_diff_seconds
from mytable

Upvotes: 1

Related Questions