mysql datetime

[acidglitter]

This is what I have right now to get the number of days left from the date saved until now..

 

DATEDIFF(DATE(thedate), CURDATE()) AS 'days_left'

 

What I want to do is keep it the way it is, BUT if there is only one day left, show how many hours or minutes left. I'm not really sure how to do that. I'm thinking a subquery might work so even if theres a few days left or hours it will still be called "days_left".... Could anyone help me with this?

[phpSensei]

Well try IF days is 0, then run a query for hours left, else just run a query that shows the days.

 

[acidglitter]

Well try IF days is 0, then run a query for hours left, else just run a query that shows the days.

 

 

i'm not sure how to code that in a mysql query though. could you write an example?

[phpSensei]

It would be like...

 

 

$query = mysql_query("SELECT.........etc DATEDIFF(DATE(thedate), CURDATE()) AS 'days_left'");

  if(mysql_num_rows($query)==0){

  // QUERY FOR HOURS LEFT
}
else
{
  // QUERY FOR DAYS LEFT

}

[acidglitter]

how could i show how many hours are left?

 

the query i'm using now (DATEDIFF(DATE(thedate), CURDATE()) AS 'days_left') only shows how many days are left

[Barand]

SELECT IF(DATEDIFF(NOW(), logged) >= 1, DATEDIFF(NOW(), logged), TIMEDIFF(NOW(),logged)) as timeleft  FROM tablename`

[PFMaBiSmAd]

Edit: Similar to Barand's code above ^^

 

No need for any subquery or slow php code, just a mysql IF() statement (assuming that 'thedate' is a DATETIME data type -

 

$query = "SELECT IF(DATEDIFF(DATE(thedate), CURDATE()) > 0,
DATEDIFF(DATE(thedate), CURDATE()),
SUBTIME(TIME(thedate), CURTIME())) AS 'days_left' FROM your_table";

 

The above works when thedate is greater than the current date/time, otherwise you get negative numbers.

[acidglitter]

thanks for the replies!

this is what i have now then...

SELECT *,
IF(DATEDIFF(DATE(thedate), CURDATE()) > 1,
DATEDIFF(DATE(thedate), CURDATE()),
SUBTIME(TIME(thedate), CURTIME())) AS 'days_left'

 

and it works like you said, if there are 2 days or more its a whole number.. if its less than a day its a negative number.. but i want it to be a positive number for how many hours left instead and i'm not sure what to change it so it does that..

[acidglitter]

*bump*