SQLite: express the difference as days, hours, minutes between two given dates

sqlite datetime datediff
Srikanth S picture Srikanth S · Nov 10, 2011 · Viewed 10k times · Source

I am trying to express the difference of two given dates in days, hours, and minutes (like 1 day, 6 hours, 17 minutes.) as SQLite query output. I have entryin and entryout as datetime fields in a SQLitedatabase. I tried all combinations of julianday and strftime but still running into rough weather.

I tried strftime('%d %H:%M', julianday(entryout)-julianday(entryin)). For a row the values are 2011-11-10 11:46, and 2011-11-09 09:00. but the output is 25 14:46 instead of 01 02:46.

Can some one help me with this, or point me correct logic for this? Thanks in advance.

Answer

Bill picture Bill · Nov 10, 2011

You can try something like this:

SELECT
    CAST((strftime('%s', '2011-11-10 11:46') - strftime('%s', '2011-11-09 09:00')) / (60 * 60 * 24) AS TEXT) || ' ' ||
    CAST(((strftime('%s', '2011-11-10 11:46') - strftime('%s', '2011-11-09 09:00')) % (60 * 60 * 24)) / (60 * 60) AS TEXT) || ':' ||
    CAST((((strftime('%s', '2011-11-10 11:46') - strftime('%s', '2011-11-09 09:00')) % (60 * 60 * 24)) % (60 * 60)) / 60 AS TEXT);

Related questions

How do I insert datetime value into a SQLite database?

I am trying to insert a datetime value into a SQLite database. It seems to be sucsessful but when I try to retrieve the value there is an error: <Unable to read data> The SQL statements are: create …

Read More
How do DATETIME values work in SQLite?

I’m creating Android apps and need to save date/time of the creation record. The SQLite docs say, however, "SQLite does not have a storage class set aside for storing dates and/or times" and it's "capable of storing …

Read More
How can I store the current timestamp in SQLite as ticks?

I have a SQLite database where I store the dates as ticks. I am not using the default ISO8601 format. Let's say I have a table defined as follows: CREATE TABLE TestDate (LastModifiedTime DATETIME) Using SQL, I wish to insert …

Read More