Time of

Time of

(1) By anonymous on 2020-09-06 14:36:27 [link][source]

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. Thx

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

If you change the slashes to hyphens, those strings would be in ISO 8601 format, which then means SQLite's date and time functions 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.

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][source]

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;

``````

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[source]

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.