GROUP BY range of timestamp values

sqlite, timestamp, unix-timestamp

Solution

You may convert your unix timestamp into a string value while querying your table. Just take a look at the SQLite date funcitons.

For example you may use the following piece of code in your query:

SELECT food_id, COUNT(*) FROM food_logs GROUP BY date(log_date, 'unixepoch');

Problem

I spent a lot of time reading and still can't find the best solution. I am designing an Android Calorie Counter Application and I'm using SQLite. I have a table `food_logs` in which I have 2 fields: `log_date(NUMBER) , food_id(NUMBER)`. I save the dates as unix timestamp. I want to retrieve all the foods a person has eaten by days. For example all foods eaten today or 2012-05-20( yesterday). I was searching GROPBY timestamp but I got nowhere. Or I just need to save date as a string ( which I think is not the best practice)

Original source