Then you can have query like this:
select date_time, servername, drive, sum(diff_used) DailyUsed_mb
from tmp_dspace_import
where date_time >= julianday(current_date) - 1
group by date_time, servername, drive
This would be for yesterday and today. If you need more days in past
then change expression: date_time >= julianday(current_date) - 1
Just yesterday is then: date_time = julianday(current_date) - 1
for two days back: date_time >= julianday(current_date) - 2
etc...
Regards
Radovan
Matthew Halliday je 24.08.2017 ob 12:06 napisal:
Hi Radovan, just tried that -
if I use strftime(date_time, -1) it still gives me today, but also the last
4 days.
If I use date(date_time,'-1 days') I get a NULL.
SELECT date_time,
servername,
drive,
SUM(diff_used) AS DailyUsed_mb
FROM tmp_dspace_import
where date_time = date('now',' -1 day')
GROUP BY date_time,
servername,
drive;
returns a blank set. Does the order in the query make a difference?
Its probably me missing something here.
On Thu, Aug 24, 2017 at 10:48 AM, Radovan Antloga <radovan.antl...@siol.net>
wrote:
Hi,
you don't have all columns in group by.
You should have grup by 1,2,3. Column 3
is expression.
Regards,
Radovan
Matthew Halliday je 24.08.2017 ob 11:41 napisal:
Correct:
SELECT strftime(date_time, -2) as iDATE from tmp_dspace_import
Result
21/08/2017 11:43:05
21/08/2017 11:43:05
21/08/2017 11:43:05
Not working:
select servername, drive, strftime(date_time, -2) as iDATE, SUM(diff_used)
AS DailyUsed
from tmp_dspace_import
group by servername, drive;
Result:
server_01 C: 24/08/2017 04:02:31 -312
server_01 D: 24/08/2017 04:02:31 0
server_01 E: 24/08/2017 04:02:31 0
SELECT servername,
date_time,
drive,
SUM(diff_used) AS DailyUsed
FROM tmp_dspace_import
WHERE date_time = strftime(date_time, -2)
GROUP BY servername,
drive;
gives data from last 3 days.
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
23/08/2017 11:45:33
23/08/2017 11:45:33
23/08/2017 11:45:33
24/08/2017 04:02:20
24/08/2017 04:02:20
24/08/2017 04:02:20
On Thu, Aug 24, 2017 at 10:27 AM, Clemens Ladisch <clem...@ladisch.de>
wrote:
Matthew Halliday wrote:
SELECT strftime('%Y-%m-%d',date_time , '-1 days') as DATE from
tmp_dspace_import
If I run it as a stand-alone single statement it works.
If I run it as part of a longer query I get either just 131 rows of just
today's data or a collumn of NULL values.
Obviously, the problem is with the longer query. Which you have not
shown.
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
