8

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.

Prateek
  • 3,923
  • 6
  • 41
  • 79
  • If you are in search of a quick answer then this is what , finally helped me `SELECT time(sum(strftime('%s', total_expend_time) - strftime('%s', '00:00:00')), 'unixepoch') FROM timetable` – Prateek Jan 15 '13 at 11:46

2 Answers2

14

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
CL.
  • 173,858
  • 17
  • 217
  • 259
  • what will this return `SELECT sum(strftime('%s', total_expend_time) - strftime('%s', '00:00:00' )) FROM timetable` number of seconds , as in my case it is returning 1770 what is that value. – Prateek Jan 03 '13 at 09:23
  • Yes, that's what I wrote, and what the documentation for `%s` says. – CL. Jan 03 '13 at 10:01
0

This works perfect for me:

SELECT time(
           sum( strftime('%s', total_expend_time) ),
           'unixepoch'
           )
FROM timetable

thank you CL!

Deyan Petkov
  • 53
  • 1
  • 7