I realised that - it was simply a typo. the delimeter was always a '-' as
below.
I now have all the date_time data in the correct ISO format:
2017-08-21 11:43:05
2017-08-21 11:43:05
2017-08-21 11:43:05
2017-08-21 11:43:05
2017-08-21 11:43:05
And it still won't work. Going to leave it for now and look at it fresh
tomorrow morning.
On Thu, Aug 24, 2017 at 3:07 PM, Radovan Antloga <radovan.antl...@siol.net>
wrote:
> You must have ISO8601 that has format
> YYYY-MM-DD not YYYY/MM/DD. If you compare
> strings it will not work. See result of
> this query:
>
> select date('now', '-1 days')
>
> you get: 2017-08-23
>
> Regards
> Radovan
>
> Matthew Halliday je 24.08.2017 ob 15:21 napisal:
>
> Looks like it's me.
>>
>> VBScript "NOW" = dd/mm/yyyy whereas ISO "NOW" = yyyy/mm/dd.
>>
>> Doh!
>>
>> Last part of the INSERT string reads:
>>
>> ,'" & strFreeSpace & "','" & pctFreeSpace & "','" & NOW & "');"
>>
>> Changing it to:
>>
>> ,'" & strFreeSpace & "','" & pctFreeSpace & "', datetime('NOW') );
>> so
>> it inserts the SQLite 'NOW' instead.
>>
>> See how well that works. I need to update the other date/time stamps
>> though.
>>
>>
>>
>> On Thu, Aug 24, 2017 at 2:06 PM, David Raymond <david.raym...@tomtom.com>
>> wrote:
>>
>> The date data is in the format: "21/08/2017 16:27:03". I thought TEXT
>>>> stored it in the ISO "YYYY/MM/DD" format?
>>>>
>>> What you declare the field as doesn't matter, so much as what you
>>> actually
>>> store in it. But all the functions apart from strftime use YYYY-MM-DD
>>> HH:MM:SS so if you're giving them slashes and a different order then
>>> they're all gonna mess up.
>>>
>>> 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
>>>>
>>> That's saying use date_time as the format string where -2 is the time
>>> string. I'm surprised that actually gave an answer rather than an error.
>>> And that you're getting a result with slashes is really really
>>> suspicious.
>>>
>>>
>>> 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...
>>>>
>>> Don't forget that if you have a date and time in the database, then
>>> you're
>>> comparing a whole time to just a day there. I think you'd want something
>>> like
>>>
>>> where date(date_time) = date(current_date, "-1 days") --for yesterday
>>> where date(date_time) >= date(current_date, "-6 days") --for within the
>>> last week
>>> where date(date_time) between date(current_date, "-3 days") and
>>> date(current_date, "-1 days") --3 days ago to yesterday.
>>> _______________________________________________
>>> 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
