Select by taking the difference between SQLite dates

Question

Select by taking the difference between SQLite dates

Navigation

#1 by (3 votes)

3

I wanted a select that gave me the following sentence comparing two dates, for example: "2015-03-12 13:00"e "2015-03-12 14:15" .

My return would be something like:

0 days left 1 hour 15 minutes to ...

I need this in a sql return.

sql sqlite

asked by anonymous 21.02.2016 / 20:39

1 answer

setInterval does not work after clearInterval Problems with JavaScript calculations

score 3 · Answer 1

This will not format your message exactly the way you want it, but it can help you get the values separately and then concatenate them into a string and then assemble the final message.

According to this StackOverflow response in English you can use code similar to the following:

SELECT julianday('2015-03-12 14:15') - julianday('2015-03-12 13:00')

This other answer gives examples of how to make a difference in days, hours, minutes, or seconds.

Difference in days

Select Cast (
    JulianDay('2015-03-12 14:15') - JulianDay('2015-03-12 13:00')
) As Integer

Difference in hours:

Select Cast ((
    JulianDay('2015-03-12 14:15') - JulianDay('2015-03-12 13:00')
) * 24) As Integer

Difference in minutes:

Select Cast ((
    JulianDay('2015-03-12 14:15') - JulianDay('2015-03-12 13:00')
) * 24 * 60) As Integer

Difference in seconds:

Select Cast ((
    JulianDay('2015-03-12 14:15') - JulianDay('2015-03-12 13:00')
) * 24 * 60