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?
Cheers,
Jonathan
