10

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.

S.L. Barth is on codidact.com
  • 8,198
  • 71
  • 51
  • 66
Srikanth S
  • 1,717
  • 5
  • 16
  • 21

1 Answers1

10

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);
Bill
  • 11,595
  • 6
  • 44
  • 52
  • Good answer Bill, to improve it and not to worry about too many '((' you could use /3600 for hours, %3600/60 for minutes and %60 for seconds – sandino Aug 19 '14 at 16:21
  • How to get yaer and month? – Engr Waseem Arain Jan 08 '15 at 06:11
  • @EngrWaseemArain Unfortunately there is no easy, short solution for years and months because one month can have 28, 29, 30 or 31 days and one year can have 365 or 366 days. – Bill Jan 08 '15 at 09:00