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.

important-2  NOTE

Unix Time is the 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.

For example, "1,604,966,287" in Unix Time refers to Monday, 9 November 2020, 11:58 UTC.

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.

important-2  NOTE 

This walkthrough assumes that you have opened a data source in the Data Source Designer and have applied the "mdl_course_completions"  table to the Selected Tables panel on the Tables tab.

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:


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


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