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
