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