SQLite Forum

Time of

Time of

(1) By anonymous on 2020-09-06 14:36:27

Hi everybody
I have 2 columns named hours1 and hours2 with datas like :

2020/06/03 22:05:42 and 2020/06/03 23:08:43

I m looking for à formula to calculate the time between this 2 columns.
Have you got an idea ? 
I try à lot of things but it didnt work. 

(2) By Warren Young (wyoung) on 2020-09-06 16:23:28 in reply to 1 [link]

If you change the slashes to hyphens, those strings would be in [ISO 8601 format][1], which then means SQLite's [date and time functions][2] will operate on them, which allows something like this to work:

       SELECT julianday(hours2) - julianday(hours1)
             FROM mytable
             WHERE criterion;

That gives you the delta in fractional days. If you want seconds instead, multiply by 86400.

[1]: https://en.wikipedia.org/wiki/ISO_8601
[2]: https://sqlite.org/lang_datefunc.html

(3) By anonymous on 2020-09-06 16:59:15 in reply to 2 [link]

how can i change slashes to hyphens?
your request give results for all the table or just for one line?

(4) By Ryan Smith (cuz) on 2020-09-06 17:04:46 in reply to 1 [link]

What Warren said... And, the function to change the slashes into dashes is:

  replace(OriginalText, StuffToReplace, StuffToReplaceItWith)

So that, using Warren's example, it becomes:

  SELECT julianday(replace(hours2,'/','-')) - julianday(replace(hours1,'/','-'))
    FROM mytable
   WHERE criterion;


(5) By anonymous on 2020-09-06 17:17:35 in reply to 4 [link]

it on the right way but WHERE Criterion doesn'y work it s unknown by browser for sqlite

(6) By Ryan Smith (cuz) on 2020-09-06 17:34:43 in reply to 5 [link]

Sorry, a language deficit.  
"Criterion" here is just a way of saying "whatever you want the criteria to be".

The criterion (single) or criteria (plural) is the set of Rules by which to filter, so if you only want to SELECT for times where hour1 is later than February 1, 2020, for instance, then the "Criterion" becomes "hours1 > '2020/02/01'" and the full statement becomes:

  SELECT julianday(replace(hours2,'/','-')) - julianday(replace(hours1,'/','-'))
    FROM mytable
   WHERE hours1 > '2020/02/01';


But since we do not know what criteria you want to filter by, and it is irrelevant to the answer itself, we just added the word "Criterion" to complete the structure of the SELECT statement. It's up to you to decide what that is.

(7) By anonymous on 2020-09-06 18:11:41 in reply to 6 [link]

ok big thx.
it s not always easy when the english is not your native language.
I just want the the duration between two schedules at the same line of the table in hours.

it s helps me to know how many hours a car stay in a parking.