On 2016/05/21 5:12 PM, Jonathan wrote: > Hi List, > I've come across a little curiousity and was wondering what was going > on (and if there's an even better way). > > I have some DATETIME data: > > 2013-06-03 16:08:55+01:00 > 2013-06-03 16:46:22+00:00 > 2013-06-03 16:48:57+00:00 > ... > > I want to get the the start of the day that was 28 days ago from the > max() value. > > I can think of two ways to do this and as best I can tell they both > give the same (correct) result: > > --Get the maximum datetime value (the inner "datetime()" is > required so that the max() takes into account the timezones) and then > work out the necessary time/date for 28 days ago for the one result. > select > datetime( max( datetime(my_timedate_column)) , '-28 day' , 'start > of day' ) > from my_table > > --Convert all datetimes to the date 28 days ago, then get the > maximum from them. > select > > max( datetime(my_timedate_column, '-28 day' , 'start of day' ) ) > from my_table > > > As far as I can tell from my non-thorough testing, the first of these > is a smidgen (5-10%) faster than the second although the second is > easier to read. Logically I can see why the second would be slower, > but I was hoping the second would be optimised to the same thing as > the first. > > Is there a reason for this? And is there a even more efficient way to > do this?
Yes, a very good reason. What the first statement does is find the highest value in the list (very fast if that list is indexed too) and then, once found, calculates a date-shift on the output. The second way calculates a date-shift on each of them (potentially many many more than the first), and then finds the highest among them. Should be much slower on a large list. Another possibly even better option might be to simply do the whole query and then transform the date, for example: SELECT datetime( ( SELECT max( datetime( my_timedate_column ) ) FROM my_table) , '-28 day' , 'start of day' ) --- OR perhaps if you value readability: SELECT datetime ( d.x, '-28 day' , 'start of day' ) FROM (SELECT max( datetime( my_timedate_column ) ) AS x FROM my_table) AS d (In SQLite this aliasing of the FROM nested select (AS d) is not strictly needed, but in most other SQL engines the rule is that any sub-select in the FROM clause MUST be aliased, for good reason, so I do it always.) Note: If you use ISO-8601 dates (which is what SQLite does) and maintains your dates as such and not use different time-zone modifiers (or at least, not different-signed timezone modifiers), then you can get away with a straight-forward comparison like MAX ( x ) in stead of having to do MAX ( datetime ( x ) ), since the ISO format sorts correctly in string terms too - which will shave off another few clock cycles and improve readability, if that's what you need. Cheers, Ryan
