CODEWITHSUNDEEP
mysqldatetimedifference
Arun
Arun

Reputation: 3721

datetime difference shows wrong values in mysql

In my website I show some data according to the date it added to the database. It will place a tag "NEW" to the product which added with in last 7 days. The code works perfectly till today. Now it shows wrong values. I am using the below code to get the difference

DATE(stored_date_time) - DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS days

and the output of this code is 77. But today date is 2014-07-01

while echoing the value of DATE(stored_date_time) it gives the output

2014-07-01

and echoing of DATE_SUB(CURDATE(), INTERVAL 7 DAY) gives the output

2014-06-24

I cant find what is wrong with my code. Please help..

Upvotes: 0

Views: 1579

Answers (4)

Konstantin Utkin
Konstantin Utkin

Reputation: 508

Just encountered similar issue. I was using CAST((1000000 * (moment2 - moment1)) as UNSIGNED), where moment1 and moment2 are DATETIME(6). And it was working perfectly until once I got enormous value for one of thousands values. What is ridiculous, why it was working all the time, and only one pair of values gave invalid result. Now changed request to TIMESTAMPDIFF(MICROSECOND, moment1, moment2), it works OK.

Upvotes: 0

CrashOverload
CrashOverload

Reputation: 79

Arun, Since you asked what is wrong with your code.

select date1 - date2 as days;

It is not actually meaningful at all. The values of dates in number format are just formed as "yyyymmddhhmmss". So it contains the same information as the string, but instead of using characters, each digit is actually an integer. That format might be useful for someone, but you can't use that format to calculate differences.

Basically what i am trying to say is, if the query above is run for say '2014-07-02' and '2014-06-22' you'll get 80 as result. Which is in fact 

20140702-20140622 =80.

So like you have already been adviced on the other post, use the datediff() function.

Upvotes: 2

Sadikhasan
Sadikhasan

Reputation: 18600

Try with DATEDIFF function.

SELECT DATEDIFF(DATE(stored_date_time),DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AS days

Upvotes: 1

Bla...
Bla...

Reputation: 7288

Instead of subtracting those date, it would be better if you use DATEDIFF, like below: 

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate

which will return 61.. for more info func_datediff

Upvotes: 1

Related Questions