Case Study: Converting Unix Time to Natural Time

This case study focuses on using a calculated field to convert Unix time to natural time in an Ad Hoc view on Lambda Analytics.

Most time-related data in your Moodle or Totara databse is stored as Unix Time. To create reader-friendly reports, it would be best to create a calculated field that converts Unix Time to the traditional format that we are used to reading (Month/Date/Year, Hour/Minute). 

Using the Calculated Fields tab of the Data Source 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 data source (available in Public Artifacts) contains several examples of this specific Unix Time conversion. We will walk through the steps of creating the calculated field "datecompleted," which converts the Unix Time string from the "timecompleted" field.

1.  From the Lambda Analytics homepage, click Create under the Ad Hoc view header to create a new Ad Hoc view.

2.  From the Data Selector, select an appropriate data source. Then, click Choose Data to access the Data Chooser.

3.  In the Data Chooser, configure the Fields, Pre-Filters, and Display settings, and click OK to access the Ad Hoc editor.

4.  In the Ad Hoc editor, navigate to the Data Selection panel and hover over the Detail Selector icon at the upper-right corner of the Fields section. Select Create Calculated Field from the context menu to access the New Calculated Field dialog box.

5.  In the Field Name field, enter a name for the new calculated field. We will call our field "datecompleted."

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

7.  In the Formula Builder, 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. Double-click this field to enter it into your expression.

"mdl_course_completions.timecompleted" will be inserted into your expression, creating the full expression as follows:

from_unixtime(nullif(mdl_course_completions.timecompleted,0))

8.  Click Create Field to save your new calculated field.