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
You will need to also add the relevant custom user profile field id that stores the date field you are working with.

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
Back to top