Select taking the difference between Sqlite dates

Asked

Viewed 1,804 times

3

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

My return would be something like:

0 days 1 hour and 15 minutes to go...

I need this in a sql return.

sql sqlite
  • 1

    "I need this in a sql return." Sure? Can do, but it’s usually an unnecessary complication. It would be much easier to do in the application part.

1 answer

5

This won’t exactly format your message 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 response from Stackoverflow in English you can use a 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 get the 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

Browser other questions tagged sql sqlite

You are not signed in. Login or sign up in order to post.