mysql - Why does FROM_UNIXTIME is returning date back in 1970? -

mysql - Why does FROM_UNIXTIME is returning date back in 1970? -

i trying interval between 2 date.

if have '2014-10-31' , '2014-10-10' results needs '2014-10-21'

here have done.

select from_unixtime(unix_timestamp(curdate()) - unix_timestamp('2014-10-10'), '%y-%m-%d')

but returning 1970-01-22

what want homecoming 2014-10-21

why not working? , how reply looking for?

thank in advance

the result of unix_timestamp() - might have guessed - unix timestamp: integer starting 0 @ 1970-01-01 00:00:00 utc , counting 1 every sec since. of import part is, 0 on timescale neither 0 a.d. nor other historic event, jan 1st, 1970.

your example, unix_timestamp(curdate()) - unix_timestamp('2014-10-10') calculates difference of 21 days (as of posting time) - translates date of 1970-01-01 + 21 days, resulting in 1970-01-22.

edit

as @mike's question in comments: subtracting 2 dates gives timespan, not date. interpret date again, need add together starting point in time. seen above, unix timestamp uses 1970-01-01, while gregorian calendar uses ficitve year of christ's birth. utilize this, take

date_add('0001-01-01', interval (unix_timestamp(curdate()) - unix_timestamp('2014-10-10')) second)

resulting in right value of 0001-01-22.

mysql intervals