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 01/01/1970.
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
No Comments