date_diff question

IBazz · December 9, 2010, 4:37am

Mulling this over in my head before starting the code and I am stuck on part of it.

I have two dates in my table and an ISO date for today.

2010-12-11
2010-12-17
2010-12-09

I need to work out how many of the date range are within several time slots eg less than 1 day, less than three days and less then 7.

(the day numbers are not set in concrete so I need to make the same query work if those numbers differ).

I am thinking along the lines of this code, for when checking just one date but I need to build it into one query for all the ‘day numbers’ I may use


select count(*)

from table1 AS t1
where (
           ( date_diff ( 2010-12-09 , t1.check_in_date)  < 3 )
          OR
           ( date_diff ( 2010-12-09 , t1.check_in_date + 1 )  < 3 )
          OR
           ( date_diff ( 2010-12-09 , t1.check_in_date + 2 )  < 3 )
        )

select count(*)
from table2 AS t2
where (
           (
             ( date_diff ( 2010-12-09 , t2.check_in_date  )  <= 5 )
             AND
             ( date_diff ( 2010-12-09 , t2.check_in_date )  > 3 )
           )
          OR
           (
             ( date_diff ( 2010-12-09 , t2.check_in_date + 1  )  <= 5 )
             AND
             ( date_diff ( 2010-12-09 , t2.check_in_date +1 ) > 3 )
           )
          OR
           (
             ( date_diff ( 2010-12-09 , t2.check_in_date + 2  )  <= 5 )
             AND
             ( date_diff ( 2010-12-09 , t2.check_in_date + 2 )  > 3 )
           )
          OR
          (
             ( date_diff ( 2010-12-09 , t2.check_in_date + 3 )  <= 5 )
             AND
             ( date_diff ( 2010-12-09 , t2.check_in_date + 3 )  > 3 )
           )
          OR
           (
             ( date_diff ( 2010-12-09 , t2.check_in_date + 4  )  <= 5 )
             AND
             ( date_diff ( 2010-12-09 , t2.check_in_date + 4 )  > 3 )
           )
        )
  


select count(*)
from table3 AS t3
where (
           (
             ( date_diff ( 2010-12-09 , t3.check_in_date  )  <= 7 )
             AND
             ( date_diff ( 2010-12-09 , t3.check_in_date )  > 5 )
           )
          OR
           (
             ( date_diff ( 2010-12-09 , t3.check_in_date + 1  )  <= 7 )
             AND
             ( date_diff ( 2010-12-09 , t3.check_in_date +1 ) > 5 )
           )
          OR
           (
             ( date_diff ( 2010-12-09 , t3.check_in_date + 2  )  <= 7 )
             AND
             ( date_diff ( 2010-12-09 , t3.check_in_date + 2 )  > 5 )
           )
          OR
          (
             ( date_diff ( 2010-12-09 , t3.check_in_date + 3 )  <= 7 )
             AND
             ( date_diff ( 2010-12-09 , t3.check_in_date + 3 )  > 5 )
           )
          OR
           (
             ( date_diff ( 2010-12-09 , t3.check_in_date + 4  )  <= 7 )
             AND
             ( date_diff ( 2010-12-09 , t3.check_in_date + 4 )  > 5 )
           )
          OR
           (
             ( date_diff ( 2010-12-09 , t3.check_in_date + 5  )  <= 7 )
             AND
             ( date_diff ( 2010-12-09 , t3.check_in_date + 5 )  > 5 )
           )
          OR
          (
             ( date_diff ( 2010-12-09 , t3.check_in_date + 6 )  <= 7 )
             AND
             ( date_diff ( 2010-12-09 , t3.check_in_date + 6 )  > 5 )
           )
          
        )

It looks like a UNION ALL might do it but all that code looks too repetitive to be correct.

I need to bring back, how many days are less than 7 from ‘today’, how many are less than 5 days from ‘today’ and how many are less than 3 days away from today. BUT, I need to make sure that if a date is within 3 days, that it isn’t also counted as being within 5 days.

bazz

IBazz · December 9, 2010, 4:41am

if there were some way to have all those repeated lines reduced such that they performed only as many times as were needed - as determined by the ‘day numbers’. I mean, if I have to check how many are within 9 days, I don’t want to have to add more rows to the query when on other occasions I may only need to check for 3 days at most.

Oh-h-h, I hope I haven’t just confused what I thought was a well explained issue.

r937 · December 9, 2010, 5:05am

let’s start by asking which database system you’re using

mysql doesn’t have a “date_diff” function

mysql has a DATEDIFF function, but it requires two date or datetime parameters, and one of your parameters is a date plus a number, which does not actually result in a date

IBazz · December 9, 2010, 2:19pm

Sorry it was meant to be DATEDIFF.

I’m not sure what you mean when you say that one of my parameters is a date + a number.

the code would read as


 ( date_diff ( 2010-12-09 , 2010-12-13 )  < 3 )

bazz

r937 · December 9, 2010, 3:04pm

have you tested it?

IBazz · December 9, 2010, 4:10pm

well, I have it working for the 3 day check.

The first_date of the period is 2010-12-12
the last date of the period is 2010-12-20

and I am checking to see how many days of the period are within 3 from today.


SELECT count(*)
  FROM booked AS t1
 WHERE (
             ( 
                 ( DATEDIFF( '2010-12-09' , t1.check_in_date) ) < 3 )
             OR
             ( 
                 ( DATEDIFF( '2010-12-09' , t1.check_in_date + 1 ) )  < 3 )
             OR
             (
                 ( DATEDIFF( '2010-12-09' , t1.check_in_date + 2 ) )  < 3 )
           )
  and id = 1281

This returns 1, which is correct.

However, If I continue with that coding as per my first post, would that be the correct way to do it? It seems orfly long and repetitive, to me.

bazz

r937 · December 9, 2010, 4:17pm

my concern centered around t1.check_in_date + 2

ordinarily i would expect this to be coded as t1.check_in_date + INTERVAL 2 DAY

as for your coding question, what would your query look like if you did the DATEDIFF in a a subquery (inline view)?

in other words, rather than the WHERE clause in post #6, you instead had something like WHERE date_diff < 3, where “date_diff” is a column alias given to the result of the function in the subquery

IBazz · December 9, 2010, 4:31pm

Hmm, I’ll ponder some more.

here is what I have currently and, whilst it seems to work for the first query, it returns 0 for the second.


SELECT count(*)

 
FROM booked AS t1
WHERE (
           ( ( DATEDIFF( '2010-12-09' , t1.check_in_date) ) < 3 )
          OR
          ( ( DATEDIFF( '2010-12-09' , t1.check_in_date + 1 ) )  < 3 )
          OR
          ( ( DATEDIFF( '2010-12-09' , t1.check_in_date + 2 ) )  < 3 )
        )
  and id = 1281

union all
SELECT count(*)

 
FROM booked AS t1
WHERE (
          ( 
            ( DATEDIFF( '2010-12-09' , t1.check_in_date) ) < 7 
            and
            ( DATEDIFF( '2010-12-09', t1.check_in_date) ) >= 3  
          )
          OR
          (
            ( DATEDIFF( '2010-12-09' , t1.check_in_date + interval 1 day ) )  < 7 
            and
            ( DATEDIFF( '2010-12-09', t1.check_in_date + interval 1 day ) ) >= 3 
          )
          OR
          ( 
            ( DATEDIFF( '2010-12-09' , t1.check_in_date + interval 2 day ) )  < 7 
            and
            ( DATEDIFF( '2010-12-09', t1.check_in_date + interval 2 day ) ) >= 3 
          ) 
        )
  and id = 1281

bazz

IBazz · December 9, 2010, 4:37pm

actually, there was an impossible WHERE in that so here it is without it. yet it still returns zero 0 for the second query


SELECT count(*)

 
FROM booked AS t1
WHERE (
          ( ( DATEDIFF( '2010-12-09' , t1.check_in_date) ) < 3 )
          OR
          ( ( DATEDIFF( '2010-12-09' , t1.check_in_date + 1 ) )  < 3 )
          OR
          ( ( DATEDIFF( '2010-12-09' , t1.check_in_date + 2 ) )  < 3 )
        )
  and id = 1281

union all
SELECT count(*)

 
FROM booked AS t1
WHERE     ( 
          DATEDIFF( '2010-12-09' , t1.check_in_date) < 7 
          and
          DATEDIFF( '2010-12-09', t1.check_in_date) >= 3  
          )
          OR
          (
          DATEDIFF('2010-12-09', t1.check_in_date + interval 1 day ) < 7 
          and
          DATEDIFF('2010-12-09', t1.check_in_date + interval 1 day ) >= 3 
          )
          OR
          ( 
          DATEDIFF('2010-12-09', t1.check_in_date + interval 2 day ) < 7 
          and
          DATEDIFF('2010-12-09', t1.check_in_date + interval 2 day ) >= 3 
          ) 
       
  and id = 1281

bazz

IBazz · December 9, 2010, 5:24pm

ok, a bit of progress perhaps.

the first_date is 2010-12-09
the last_date is 2010-12-20

the today date is 2010-12-09


SELECT count(*)
, date_diff_1.number
from (select count(*) as number
  , datediff( '2010-12-09', check_in_date ) as diff
  , datediff( '2010-12-09', check_in_date + interval 1 day ) as diff2
  from booked
  where id = 1281
) as date_diff_1
where date_diff_1.diff < 3
 and date_diff_1.diff2 < 3

This still shows just 1 for the count and 1 for date_diff_1.number where one of them should show 2. OK maybe it can’t but I need it SUM the counts so I can return how many days are less than three away from 2010-12-09.

Should I be looking for how to apply SUM in the sub query? I tried but it errored.

bazz

r937 · December 9, 2010, 5:49pm

rule #1 of aggregation is explained briefly as follows

if there is no GROUP BY clause, then every expression in the SELECT clause must be an aggregate function, e.g. COUNT() or SUM() or MIN(), etc., and cannot be one of the columns

break dis rule and de results be unpredictable

rule #2 of aggregation is explained briefly as follows

if there is a GROUP BY clause, then columns are allowed in the SELECT clause alongside aggregate expressions (which are then optional), but every such column in the SELECT clause must be one of the columns in the GROUP BY clause

mysql docs call this a “hidden” column, because it’s in the SELECT clause but hidden (missing) from the GROUP BY clause

break dis rule and de results be unpredictable

IBazz · December 9, 2010, 5:50pm

OK, this is very sloooow.

I have found one way of bring back the results I need but they are not summed or counted.


SELECT count(*)
, date_diff_1.diff_0 as zero_day
, date_diff_1.diff_1 as 1_day
, date_diff_1.diff_2 as 2_days
, date_diff_1.diff_3 as 3_days
from (select 
     datediff( check_in_date, '2010-12-09' ) as diff_0    
   , datediff( check_in_date + interval 1 day, '2010-12-09' ) as diff_1
   , datediff( check_in_date + interval 2 day, '2010-12-09' ) as diff_2
   , datediff( check_in_date + interval 3 day, '2010-12-09' ) as diff_3
     
  from booked
  where id = 1281
) as date_diff_1

I get a result of four cols, with 0,1,2,3 respectively. Is this query close to how it ought to be done properly and if so, how can I accumulate the results into one number? ie, 3. oo-er, I think I might try MAX.

bazz

r937 · December 9, 2010, 5:59pm

before you go much further, you’re still breaking rule #2

IBazz · December 9, 2010, 6:18pm

Thanks, rudy.

much more complex than I imagined! I need to find a way to do the datediff comparison so I can see how many days fall within 3 of ‘today’, within 5 of ‘today’ and within 7 of ‘today’. But I need to ensure also that a date that is 5 days from today, isn’t after the last_date.

groan.

bazz

r937 · December 9, 2010, 6:28pm

groan is right

remember my subquery suggestion? it looked for a moment like you were going in that direction with this –

FROM (SELECT count(*) as number
  , DATEDIFF( '2010-12-09', check_in_date ) as diff
  , DATEDIFF( '2010-12-09', check_in_date + INTERVAL 1 DAY ) as diff2
  FROM booked
  WHERE id = 1281
) as date_diff_1

what i was hinting at was this subquery –

SELECT DATEDIFF('2010-12-09',check_in_date) AS diff
  FROM booked
 WHERE id = 1281

this will return a single number, yes?

so then you can do stuff like…


WHERE diff IN (0,1,2)

in the outer query