I am trying to get the interval between 2 date.
If I have '2014-10-31' and '2014-10-10' then results needs to be '2014-10-21'
Here is what I have done.
SELECT FROM_UNIXTIME(unix_timestamp(CURDATE()) - unix_timestamp('2014-10-10'), '%Y-%m-%d')
But this is returning 1970-01-22
What I want it to return is 2014-10-21
Why is it not working? and how to get the answer that I am looking for?
Thank you in advance
The result of UNIX_TIMESTAMP() is - you might have guessed - a UNIX timestamp: An integer starting with 0 at 1970-01-01 00:00:00 UTC and counting one for every second since. The important part is, that zero on this timescale is neither 0 A.D. nor some other historic event, but Jan 1st, 1970.
Your example, unix_timestamp(CURDATE()) - unix_timestamp('2014-10-10') calculates a difference of 21 days (as of your posting time) - this translates to a date of 1970-01-01 + 21 days, resulting in 1970-01-22.
EDIT
As from @Mike's question in the comments: Subtracting two dates gives a timespan, not a date. To interpret this as a date again, you need to add it to some starting point in time. As seen above, the UNIX timestamp uses 1970-01-01, while the gregorian calendar uses a ficitve year of Christ's birth. To use this, you would take
DATE_ADD('0001-01-01', INTERVAL (UNIX_TIMESTAMP(CURDATE()) - UNIX_TIMESTAMP('2014-10-10')) SECOND)
resulting in the correct value of 0001-01-22.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments