Example to use variable in Mysql

Posted on August 10, 2013 by Suresh Kamrushi in Mysql

After a long time i used variable in SQL queries.
This time i have to calculate time/days difference between added date of a particular news and current date, so that viewers get how old a particular news is.

Query is like below:

SELECT @diff := DATEDIFF(now(),news.added_date),IF(@diff>0,@diff,TIMESTAMPDIFF(MINUTE,news.added_date,now())) AS days, news.* from news WHERE news_id = 1;

Here “@diff” is a variable which store difference with the help of inbuilt function “DATEDIFF”. This function returns the number of days between the dates provided in function.

If it return 0 means there is a difference in hours. And for calculating difference in hours again i used the variable @diff if it is less than zero than i am calculating difference

in minute, so that i will get the difference in minutes.

  Comments or questions are welcome  

Tagged as ,