Search code examples
databasesqliteandroid-sqlite

sqlite: how to add total time hh:mm:ss where column datatype is DATETIME?

COLUMN

This is the snapshot of my column in sqlite db and its datatype in schema is defined has DATETIME. Now I want to apply some aggregation function like sum() to calculate the total of this column.

the query that I used is:

select sum(total_expend_time) from timetable; but it returns 0.0 as result.

Solution

  • SQLite has no native DATETIME data type; the values are stored as strings.

    You have to use a built-in date function to convert from these strings to a number of seconds before you can add them:

    SELECT sum(strftime('%s', total_expend_time) -
           strftime('%s', '00:00:00'       ))
FROM timetable

    If you want to have this formatted as a time, you have to convert it back afterwards:

    SELECT time(sum(...), 'unixepoch') FROM timetable