hey guys , i cant get my head around his one, here is the query

SELECT acdate,CONCAT(DAYOFYEAR(acdate)-DAYOFYEAR(NOW()),' days ', DATE_FORMAT(ADDTIME("2000-00-00 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(acdate + INTERVAL ktime DAY)-TIME_TO_SEC(acdate))),'%k hours and %i minutes')) AS time FROM tblproduct WHERE DATE_SUB(CURDATE(),INTERVAL 5 DAY) <= acdate;

acdate holds the date when a product was created, ktime holds an integer value on the amount of days a product is allowed to be active. the query above supposed to return the records from the last 5 days, and display the days hours between acdate and acdate + days, so it would say "3 hours 2min left till listing ends" , the query so far returns 0 for hours and minutes.

any clues?

Forgive me if I'm just blindly jumping in here but it looks like you're subtracting

DAYOFYEAR(acdate)-DAYOFYEAR(NOW())

The epoch started in about 1970 somewhere starts at zero seconds... the more recent the date/time, the higher the number. You're subtracting a date that could be seconds, days, weeks, months, or years old with the current date "NOW" which will always have a higher number and always return 0 (unless mysql time functions can return a negative integer that you could get the absolute value of) Try switching those two around so it's

DAYOFYEAR(NOW())-DAYOFYEAR(acdate)

Originally Posted by tmijs789

Forgive me if I'm just blindly jumping in here but it looks like you're subtracting

DAYOFYEAR(acdate)-DAYOFYEAR(NOW())

The epoch started in about 1970 somewhere starts at zero seconds... the more recent the date/time, the higher the number. You're subtracting a date that could be seconds, days, weeks, months, or years old with the current date "NOW" which will always have a higher number and always return 0 (unless mysql time functions can return a negative integer that you could get the absolute value of) Try switching those two around so it's

DAYOFYEAR(NOW())-DAYOFYEAR(acdate)

January 1st 1970 00:00:00 GMT.