Android SQLite Group By Day

user_14086589 0
Viewed 56

I am trying to filter using sqlite query where it returns startdatetime which is in TEXT format.

The query I tried is :-

SELECT StartDateTime, GROUP_CONCAT(Remark,',') AS Remarks 
FROM History 
GROUP BY strftime('%D', date(StartDateTime)) 
HAVING COUNT(*) > 0

This works fine, but it only returns one value which is last value to be exact instead of giving multiple value in list

Does any one know how to do filter by today or month ?

Sample Data :

ID startDateTime remark
1 20210311173715854 test
2 20210312100958295 test2
3 20210312155019778 test3
4 20210315135920665 test4

Currently when I use above query, it returns 20210315135920665 with all remark without taking account which day its comes from..

What I want to achieve is when I execute query I need to have correct remark on correct datetime...

The result that I am expecting :

ID startDateTime remark
1 20210311173715854 test
2 20210312100958295 test2,test3
4 20210315135920665 test4
android-studio sqlite android
1 Answers

Your dates have the format YYYYMMDDhhmmsssss so you must group by the first 8 chars:

SELECT SUBSTR(StartDateTime, 1, 8) AS date, 
       GROUP_CONCAT(Remark) AS Remarks 
FROM History 
GROUP BY date

If you want the first datetime of each day:

SELECT MIN(StartDateTime) AS firstdatetime, 
       GROUP_CONCAT(Remark) AS Remarks 
FROM History 
GROUP BY SUBSTR(StartDateTime, 1, 8)
user_104988280
Related