> 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
