CODEWITHSUNDEEP
androidsqlitedatetime
kakka47
kakka47

Reputation: 3509

Get data from sqlite database per day DATE TIME

I have a sqlite database. I have three columns:_id, date, value.

I now want to extract a count of the _id:s depending on the day in the date, and calculate an average of the int value. This is for an Android app.

So I want to "select the day in date and for each day ( for sixty days), count how many _id:s there are for this day. Finally calculate the average of value.

I guess it is something like :

"SELECT DATE('now' 'days[i]') as date, COUNT(_id) as count,  AVG(value) as vl FROM v_efforts WHERE DATE(v_efforts.date) = DATE('now' 'days[i]')";

But I can't get the 'days[i]' to work. I don't know how i can get this value to increase to sixty, and then how I can store the count and vl for each of these sixty days.

THanks a lot!

Upvotes: 4

Views: 7501

Answers (2)

glibdud
glibdud

Reputation: 7850

You'll want to use a GROUP BY expression to aggregate the entries by date. It's not quite clear whether you're looking for the last 60 days of entries in the database, or the entries from the last 60 real days (which would only be the same if you can assume that there are entries every day).

For the former (last 60 days which had database entries), you can use a LIMIT clause:

SELECT date,COUNT(_id),AVG(value) FROM v_efforts GROUP BY date ORDER BY date DESC LIMIT 60;

For the latter (last 60 real days), you can use WHERE:

SELECT date,COUNT(_id),AVG(value) FROM v_efforts WHERE date>DATE('now','-60 days') GROUP BY date ORDER BY date DESC;

Upvotes: 5

bzarah
bzarah

Reputation: 358

The docs for Version 3 are pretty decent:

http://www.sqlite.org/lang_datefunc.html

I would look at the block that deals with the built in date time functions. Since SQLite doesn't support an actual date datetype:

Compute the current date:

SELECT date('now');

Compute the last day of the current month:

SELECT date('now','start of month','+1 month','-1 day');

Compute the date and time given a unix timestamp 1092941466.

SELECT datetime(1092941466, 'unixepoch');

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

Compute the current unix timestamp.

SELECT strftime('%s','now');

Compute the number of days since the signing of the US Declaration of Independence.

SELECT julianday('now') - julianday('1776-07-04');

Compute the number of seconds since a particular moment in 2004:

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

Compute the date of the first Tuesday in October for the current year.

SELECT date('now','start of year','+9 months','weekday 2');

Compute the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part):

SELECT (julianday('now') - 2440587.5)*86400.0;

Upvotes: 1

Related Questions