Num Of Weeks And Date Between Two Dates (And A Num Of Days)

[Antonio Conte]

Hello everyone.

 

Time for another challenge. This time, I need to save stats about player injuries. I need some help with the thought process here.

 

I'm thinking abot the following structure:

- Players ID (INT)

- injury date (Datetime)

- ready to play date (Datetime)

- Day fixer (INT, default null)

- Type of injury (String (OR foreign keys to injury_type table))

 

-------------------------------------------

 

What I need with the data:

- Calculate the number of weeks before the injury is over. I also think about using the field additional days for the possibility of adding/subtract a number of days for when the injury is over.

 

-------------------------------------------

 

Hope some of you know mysql date functions. I'm want to calculate the number of weeks and days between INJURY DATE and (FIT DATE, plus/minus a number of days).

 

How it will be displayed:

- Player name

- Player injury type

- Ready in (about) X number of weeks/(days if less than a week)

 

Thank you in advanced. I will use some time to think about how to solve this.

[Paul Swanson]

DATEDIFF() looks to be what you want. It returns the number of days between two dates. Divide by 7 to get the number of weeks, and use PHP's modulus operator (%) to get the remainder for calculating the extra days.

[Antonio Conte]

I LOVE MYSQL! It's always a challenge to do new things, but you can really see the benefit of learning how to do things.

 

I use the following query to find the WHOLE number of weeks and days before the injury is finished:

 

SELECT player_id,

ROUND((datediff( IFNULL(date_add(`recovery_date`, INTERVAL `variable` DAY ) , `recovery_date`) , `injury_date`) / 7 )) AS weeks,

ROUND((datediff( IFNULL(date_add(`recovery_date`, INTERVAL `variable` DAY ) , `recovery_date`) , `injury_date`))) - (ROUND((datediff(IFNULL(date_add(`recovery_date`, INTERVAL `variable` DAY ) , `recovery_date` ) , `injury_date` ) /7 )) *7) AS days

FROM `abc_injuries

Edit:

 

This would be right

 

SELECT player_id, ROUND( (datediff( IFNULL( date_add(  `recovery_date` ,  INTERVAL  `variable` 
DAY  ) ,  `recovery_date`  ) ,  `injury_date`  )  /7  )  - 0.5) AS weeks, (ROUND( (datediff( IFNULL( date_add(  `recovery_date` ,  INTERVAL  `variable` 
DAY  ) ,  `recovery_date`  ) ,  `injury_date`  )  ) ) - ( ROUND( (datediff( IFNULL( date_add(  `recovery_date` ,  INTERVAL  `variable` 
DAY  ) ,  `recovery_date`  ) ,  `injury_date`  )  )  )  - ROUND( datediff( IFNULL( date_add(  `recovery_date` ,  INTERVAL  `variable` 
DAY  ) ,  `recovery_date`  ) ,  `injury_date`  )  %7  ) )) AS days
FROM  `abc_injuries` 
LIMIT 0 , 100 

 

I tried to explain how this works. I just realized I can't.

 

Edit 2: Had to subtract the number of weeks with 0.5 to prevent 6 extra days to become for example 3.8 days and rounded up to 4 weeks. This is in the first part of the query (the AS weeks part)

[Paul Swanson]

It can be explained with just one word: MAGIC!