Example to use variable 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.