CODEWITHSUNDEEP
c#sqlitesystem.data.sqlite
EsteveBlanch
EsteveBlanch

Reputation: 115

SQLIte SUM datetimes

I have a database with rows, with time values. I want a SUM of that time values (generaly, the format are %H:%M).

I used SUM value like that:

SELECT SUM(n_hores) FROM table;

But only I get 0.0.

How I can do that ?

Thanks

Upvotes: 1

Views: 1394

Answers (2)

Alex
Alex

Reputation: 35409

You're probably better off subtracting the two dates by getting the "number of milliseconds since the last epoch aka (modern epoch)." There should be a function, in SQLite, that returns the number of milliseconds since the last epoch. Once you've found this, the solution should be a simple subtraction and conversion to seconds, minutes and/or hours.

Upvotes: 1

CL.
CL.

Reputation: 180080

Time values in hh:mm format are strings, and strings cannot be summed meaningfully.

You should convert these time values into a number (strftime(%s) returns the number of seconds since 1970-01-01, so you have to subtract the offset from the start of the day to get the number of seconds corresponding to your time value):

SELECT sum(strftime('%s', MyTime) - strftime('%s', '00:00')) FROM MyTable

You can also convert this number of seconds back into a time string (time() returns it in hh:mm:ss format):

SELECT time(sum(strftime('%s', MyTime) - strftime('%s', '00:00'))) FROM MyTable

Upvotes: 2

Related Questions