Content Comparison

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.

...

1. Span
   style color: #000000;
   In the Domain Designer, click the Calculated Fields tab.

   
   
   

2. Span
   style color: #000000;
   In the Field Name field, enter a name for the new calculated field—we will call our field "datecompleted."

   
   
   

3. Span
   style color: #000000;
   From the Type menu, select "Timestamp" as the type of expression.

   
   
   

4. Span
   style color: #000000;
   In the Expression field, enter the appropriate conversion expression as follows:

    

   1. Span
      style color: #000000;
      Begin by entering "from_unixtime()"—creating the initial instruction to convert your Timestamp from Unix Time.

      
      
      

   2. Span
      style color: #000000;
      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. Span
      style color: #000000;
      Select the space before the comma and in-between the parentheses in the "nullif(,0)" function.

      
      
      

   4. Span
      style color: #000000;
      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. Span
      style color: #000000;
      Once completed, your Calculated Fields panel should appear as follows:

      
      
      
      

   6. Span
      style color: #000000;
      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. Span
      style color: #000000;
      To enable this field as accessible to users of the Domain, be sure to add it on the Display tab.