Working with dates before epoch

If you use a custom user profile field with a date and want to store a date that falls before epoch, 01/01/1970 (e.g. a date of birth), the data will be stored as a negative number
e.g. 20/11/1959 is stored as -319284000 which means 319, 284, 000 seconds BEFORE 01/01/1970.

With MySQL you can convert these with the following SQL which works for both scenarios (before and after epoch, 01/01/1970):

select date_add(from_unixtime(0), interval data second) from mdl_user_info_field

Where in the above “data” is the column that returns the stored user profile field value e.g. date of birth from the mdl_user_info_field record.

If you just want the date (no timestamp):

select date(date_add(from_unixtime(0), interval data second)) from mdl_user_info_field

Moodle Tips


Leave a Reply

Your email address will not be published. Required fields are marked *