Geek asks and answers

Get data from sqlite database per day. DATE TIME

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

Now I want to extract the counter _id: s depending on the day in the date and calculate the average value of int. This is an Android app.

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

I think this 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 make it 'days[i]'work. I do not know how I can increase this value to sixty, and then how I can store the account and vl for each of these sixty days.

Extremely important!

+3
source share
2 answers

GROUP BY . , 60 60 ( , , ).

( 60 , ) LIMIT:

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

( 60 ) WHERE:

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

3 :

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

, . SQLite :

:

SELECT date ('now');

:

SELECT date ( "", " ", "+ 1" ',' - 1 ');

, unix 1092941466.

SELECT datetime (1092941466, 'Unixepoch');

, unix 1092941466, .

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

unix.

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

.

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

2004 :

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

.

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

unix (, strftime ('% s', 'now'), ):

SELECT (julianday ('now') - 2440587,5) * 86400,0;

+1

More articles: