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…

Course file system space usage query

The following query provides a summary of the file space usage (in bytes) and number of files used by each course in the system from the mdl_files meta table for the data directory. This is just for the space used in the data directory (not the database). select f.contextid, x.instanceid, c.fullname as course_full_name, c.shortname as…

Query blocks instances and positions through the database

The following query gives you an idea of all the moodle blocks that have been added to various pages on your site and where they are positioned: select bi.*, bp.*, x.* from mdl_block_instances bi inner join mdl_block_positions bp on bi.id = bp.blockinstanceid inner join mdl_context x on x.id = bp.contextid order by bi.blockname; Very handy…

Verify your Moodle Database Schema against XMLDB

There is a very handy CLI tool admin/cli/check_database_schema.php which compares the structure of your Moodle database against the XMLDB metadata and looks for any issues. These might includes issues such as: Missing tables – they exist in XMLDB but not in your DB (not good!) Unexpected tables – tables in your database not defined in…

MySQL query to get a list of Site Admin users

The following SQL will help you see which users are site admins in a MySQL (or MariaDB) database using the find_in_set function to query the values listed in the mdl_config siteadmins key-value. select u.* from mdl_user u where find_in_set(u.id, (select value from mdl_config where name = ‘siteadmins’)) Note you need to use find_in_set and NOT…