How to pass a value from the Sqlite DB to julianday()

Question

I need to calculate the difference between two dates represented as TEXT. julianday() can do this, but it accepts a literal:

select(julianday('2015-01-01 12:00:00') - julianday('2015-01-01 13:00:00'))

But if I have a table:

CREATE TABLE "time_u" (
    "time_in"   TEXT,
    "time_out"  TEXT
);

INSERT INTO time_u (time_in, time_out)
VALUES ('2007-01-01 10:00:50', '2007-01-01 11:00:00')

how can I do something like? :

julianday(time_u.time_out-time_u.time_in)

in other words take values directly from the database and subtract them as a date

You have already asked the same question: https://stackoverflow.com/questions/62495311/subtract-a-date-from-a-date-in-sqlite which was closed as a duplicate. The link to the original question contains answers provided contains all the info that you need. — forpas, Jun 21 '20 at 13:27

GMB · Accepted Answer · 2020-06-21T14:16:06.007

1

You can turn each date to Julian days, then substract:

select 
    t.*,
    julianday(time_out) -julianday(time_in) day_diff
from time_u t

If you want something more accurate, you can turn the datetimes to epoch timestamp. Say you want the difference in minutes, then:

select 
    t.*,
    (strftime('%s', time_out) - strftime('%s', time_in)) / 60.0 day_diff
from time_u t

edited Jun 21 '20 at 14:16

answered Jun 21 '20 at 13:21

GMB

216,147
25
84
135

Result: no such column: time_u.time_in – ibse Jun 21 '20 at 13:29
1

@ibse: that’s because of the table alias. Fixed. – GMB Jun 21 '20 at 13:31
how do I translate this into minutes? where do I add *24*60?? – ibse Jun 21 '20 at 13:50
@ibse: see my updated answer. – GMB Jun 21 '20 at 14:18

How to pass a value from the Sqlite DB to julianday()

1 Answers1