我不知道我错在哪里。你知道吗?
在java中,我使用以下代码:
Class.forName("org.sqlite.JDBC");
Connection c = DriverManager.getConnection("jdbc:sqlite:db.db3");
Statement stmt = c.createStatement();
ResultSet rs = stmt.executeQuery(query);
select creation_time as 'Task creation date',
sum(case when working_hrs <= 5.0 then 1 else 0 end) as '0-5 hours',
sum(case when working_hrs between 5.1 and 10.0 then 1 else 0 end ) as '5-10 hours',
sum(case when working_hrs between 10.1 and 15.0 then 1 else 0 end ) as '10-15 hours',
sum(case when working_hrs between 15.1 and 20.0 then 1 else 0 end ) as '15-20 hours'
from (select task_id, strftime('%d-%m-%Y',datetime(creation_timestamp/1000,'unixepoch','localtime')) as creation_time,
round((end_timestamp/1000 - creation_timestamp/1000) / 3600.0, 2) as working_hrs from tasks order by creation_timestamp desc
) group by creation_time
java的结果截图:
SQLite的结果截图:
"task_id" ,"session_id" ,"creation_timestamp","start_timestamp","end_timestamp"
"375501182808662" ,"8337010517175324685","1539828105263" ,"1539828105601" ,"1539828158217"
"485386198356091" ,"7933542082890916440","1540554061336" ,"1540576602811" ,"1540576630640"
"978182458409950" ,"9006702446688652505","1539801529300" ,"1539810165157" ,"1539810210255"
"1153933381873910","727803067664621142" ,"1541049927995" ,"1541056911723" ,"1541057700285"
"7007028125080279","2997722960661559829","1539635192934" ,"1539635197122" ,"1539635280448"
