I have a table_logs table, in which There are 2 fields: log_date (NUMBER), food_id (NUMBER). I save the date as a unix timestamp. I want to retrieve all the food a person has eaten in a few days.
For example, today or 2012-05-20 (yesterday) all the food I ate. I am searching for the GROPBY timestamp, but I have nowhere to go.
Or I just need to save the date as a string (I think this is not the most Best practice)
For example, you can use the following code in the query:
SELECT food_id, COUNT(*) FROM food_logs GROUP BY date(log_date,'unixepoch');
I spent a lot of time reading and still can’t find the best solution. I am designing an Android calorie counter application , I am using SQLite.
I have a table_logs table, which has 2 fields: log_date(NUMBER), food_id(NUMBER). I save the date as a unix timestamp. I want to retrieve what a person has eaten in a few days All foods.
For example, all foods eaten today or 2012-05-20 (yesterday). I am searching for the GROPBY timestamp, but I have nowhere to go.
Or I just need to save the date as a string (I think this is not the best practice)
You can convert the unix timestamp to characters when querying the table String value. Just look at SQLite date funcitons.
For example, you can use the following code in your query:
SELECT food_id, COUNT (*) FROM food_logs GROUP BY date(log_date,'unixepoch');