Case Study: Converting Unix Time to Natural Time

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.

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.

1. In the Domain Designer, click the Calculated Fields tab.
   
   

2. In the Field Name field, enter a name for the new calculated field—we will call our field "datecompleted."
   
   

3. From the Type menu, select "Timestamp" as the type of expression.
   
   

4. In the Expression field, enter the appropriate conversion expression as follows:  

   1. Begin by entering "from_unixtime()"—creating the initial instruction to convert your Timestamp from Unix Time.
      
      

   2. 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.
      
      

   3. Select the space before the comma and in-between the parentheses in the "nullif(,0)" function.
      
      

   4. 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))
      
      

   5. Once completed, your Calculated Fields panel should appear as follows:
      
      
      

   6. 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:
      
      
      

   7. To enable this field as accessible to users of the Domain, be sure to add it on the Display tab.