Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...