CODEWITHSUNDEEP
sqlitedatetimegroup-by
user4202109
user4202109

Reputation:

How to use Group in Sqlite database w.r.t. DateColoumn?

I have below coloumn in my SQLite table : DataType of ActivityDate is date provided by Sqlite.

ActivityDate
2016-01-28 07:38:41 +0000
2016-01-28 03:26:56 +0000
2016-01-29 02:22:40 +0000
2016-01-26 01:13:39 +0000

I wants to get uniqe date(Only w.r.t. Date), I do not wants to consider Time in this. So my desired result is :

ActivityDate
2016-01-28 07:38:41 +0000
2016-01-29 02:22:40 +0000
2016-01-26 01:13:39 +0000

So, Is there any way to acheive this using SQLite Query?

Upvotes: 1

Views: 34

Answers (2)

CL.
CL.

Reputation: 180070

This is not one of the supported date formats.

To be able to use the built-in date functions, you have to snip off the timezone first:

SELECT date(substr(ActivityDate, 1, 19))
FROM transaction
GROUP BY date(substr(ActivityDate, 1, 19));

Upvotes: 1

Bozhidar Gorov
Bozhidar Gorov

Reputation: 66

So if i understand correctly you want to group by only the year-month-date

I this case you migth want to read here : https://www.sqlite.org/lang_datefunc.html and especialy the strftime() function

Here is another similar question Group by month in SQLite

Upvotes: 0

Related Questions