GROUP BY Timeline Range

Question

I spent a lot of time reading and still cannot find a better solution. I am developing an Android Calorie Counter application and I am using SQLite. I have a food_logs table in which I have 2 fields: log_date(NUMBER) , food_id(NUMBER) . I save the dates as a unix timestamp. I want to get all the foods that a person ate in a few days.

For example, all foods eaten today or 2012-05-20 (yesterday). I searched for the GROPBY timestamp, but I was nowhere to be found.

Or I just need to save the date as a string (which I think is not the best practice)

Answer 1

You can convert your unix timestamp to a string value while querying the table. Just take a look at the functions of SQLite date .

For example, you can use the following code snippet in your query:

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

Answer 2

Different values ​​of timestamps in the table will have different values ​​of hour, minute, second. SQL GROUP BY is used to collect multiple rows with the same value, so the syntax will not work here. If you only need one day to evaluate the data, you can do something like this if you have a food_info table with food_id and calorie_count columns:

 SELECT SUM(calorie_count) FROM food_logs fl INNER JOIN food_info fi ON fi.food_id = fl.food_id WHERE log_date >= '2012-21-05' AND log_date < '2012-21-06' 

You may need to adjust it a bit to match SQLLite's specific semantics.