How to Sum Hours/Minutes through SQLite in C#?

Question

I have a simple table called WorkDuration which contains a field WorkedHour from type varchar.

For Instance:

WorkedHour:

04:23   

08:15    

02:00

Now I want sum(WorkedHour) from WorkDuration.

For that I have tried the following line of code to get the total duration which is working, but not correctly.

 database.ExecuteScalarAsync<int?>("SELECT SUM(WorkedHour) FROM WorkDuration WHERE WorkedHour > 0");

So how can I sum all WorkedHour's columns to have the total duration like following:

WorkedHour:

04:23   

08:15    

02:00  
-----------

Total: 14:38 Hours

It would be a pleasure if someone could help me.

Thanks in advance.

SQLite does not do `DateTime` very well. In fact, SQLite does not even have a `DateTime` datatype. My suggestion would to return all time values and then use the power of C# and do the calculations in code. — Barns, Mar 21 '20 at 15:48
that's a string. You can't use SUM on a string. You could conceivably parse the data out and cast it in SQL, but it would be much easier to do this in your app code instead. Also consider modifying your db to store this data as a single int field containing minutes. — Jason, Mar 21 '20 at 16:15
Possible duplicate : https://stackoverflow.com/questions/14134945/sqlite-how-to-add-total-time-hhmmss-where-column-datatype-is-datetime — Simon Mourier, Mar 21 '20 at 16:18

score 0 · Answer 1 · answered Feb 03 '21 at 02:03

Result in format HH:MM:SS

select time(
             sum(strftime('%s', WorkedHour), 
             'unixepoch'
           ) 
from WorkDuration;

Or if you want to take only some of the units:

select strftime(
                '%H:%M:%S', 
                ( sum('%s', WorkedHour) ),
                'unixepoch'
               )
from WorkDuration;

In the second example you can remove %H or %M or %S as you like.

How to Sum Hours/Minutes through SQLite in C#?

1 Answers1