FROM_UNIXTIME(unix_timestamp, [format])
is all you need
FROM_UNIXTIME(user.registration, '%Y-%m-%d') AS 'date_formatted'
FROM_UNIXTIME
gets a number value and transforms it to a DATE
object,
or if given a format string, it returns it as a string.
The older solution was to get the initial date object and format it with a second function DATE_FORMAT
... but this is no longer necessary