CODEWITHSUNDEEP
sqlitetimestampunix-timestamp
Radoslav
Radoslav

Reputation: 1455

GROUP BY range of timestamp values

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)

Upvotes: 5

Views: 5091

Answers (2)

David Gorsline
David Gorsline

Reputation: 5018

The various timestamp values in your table will have distinct values of hour, minute, second. SQL's GROUP BY is used to collect multiple rows with the same value, so that syntax won't work for you here. If you only need one day's worth of data summed up, you can do something like this, assuming you have a table food_info with columns food_id and calorie_count:

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 have to adjust this a little to fit SQLLite's specific semantics.

Upvotes: 1

hpityu
hpityu

Reputation: 891

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');

Upvotes: 6

Related Questions