Hi Matthew,
Do not wish to sound like a forum troll, but your descriptions are a bit
loosely worded making it difficult to follow, but we'd really like to
help, so...
Could you give us a small bit of real sample data from your table(s),
and the REAL query you use, and perhaps the output you expect to see?
That should allow us to point you fast and efficiently towards the best
solution.
Cheers!
On 2017/08/24 5:14 PM, Matthew Halliday wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
