Most time-related data in your Moodle and Totara databases is stored as Unix Time. In order to create reader-friendly reports, it will likely be essential to create a calculated field that converts Unix Time to the traditional format that we are used to reading (February 1, 2016, 12:33PM).
Using the Calculated Fields tab of the Domain Designer, you can create a calculated field to convert field data stored in Unix Time to the standard format. For the purposes of this case study, we will convert the mdl_course_completions.timecompleted field to a readable standard format.
What is Unix Time? Unix Time is the literal number of seconds that have elapsed since Thursday, the 1st of January, 1970. The resulting number is a numeric string apt for storing time codes in a database. |
The Course Completions Domain (available in the Public folder of the Repository) contains several examples of this specific Unix Time conversion. We will walk through the steps of created the calculated field “datecompleted” which converts the Unix Time string from the “timecompleted” field.
This walkthrough assumes that you have opened a Domain in the Domain Designer and have applied the "mdl_course_completions" table to the Selected Tables panel on the Tables tab.
In the Domain Designer, click the Calculated Fields tab.
In the Field Name field, enter a name for the new calculated field—we will call our field "datecompleted."
From the Type menu, select "Timestamp" as the type of expression.
In the Expression field, enter the appropriate conversion expression as follows:
Begin by entering "from_unixtime()"—creating the initial instruction to convert your Timestamp from Unix Time.
Between the two parentheses written in the previous step, add the function "nullif(,0)"—creating a further instruction to return the term "null" when the selected field is equal to zero.
Select the space before the comma and in-between the parentheses in the "nullif(,0)" function.
From the Available Fields panel, identify the field to be converted. We will select the "timecompleted" field from the "mdl_course_completions" table. To enter this field into your expression, double-click it. "mdl_course_completions.timecompleted" will be inserted into your expression, creating the full expression as follows:
from_unixtime(nullif(mdl_course_completions.timecompleted, 0))Once completed, your Calculated Fields panel should appear as follows:
Click Save Field to save your new calculated field—it will appear as part of the mdl_course_completions table in the Available Fields panel:
To enable this field as accessible to users of the Domain, be sure to add it on the Display tab.
Components of the Expression | Description |
|---|---|
| from_unixtime() | An SQL function that returns a representation of the Unix Time data in the format of "yyyy-mm-dd hh:mm:ss" |
| nullif(,) | An SQL function that returns the term "null" if the provided parameters are equal, and the value of the first parameter if they are inequal. |
| mdl_table.fieldname | An identification of a specific field from a table in the Moodle or Totara database. The field name is after the period, and the table name is before it. |