Dates before Unix Epoch
If you use a custom user profile field with a date and want to store a date that falls before Unix Epoch (
01/01/1970) (e.g. a date of birth), the data will be stored as a negative number.
For example, a date of birth of
20/11/1959 is stored as
-319284000 which means
319,284,000 seconds before
In MySQL/MariaDB you can convert these with the following SQL which works for both scenarios (before and after epoch,
01/01/1970) making it ideal when you can have either case.
select date_add(from_unixtime(0), interval data second) from mdl_user_info_field
If you just want the date without the timestamp:
select date(date_add(from_unixtime(0), interval data second)) from mdl_user_info_field