This case study demonstrates how to create a derived table that converts Unix time into natural time for the Store Learn database on Lambda Analytics.
Most time-related data in your Store Learn database 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_user.timecreated field to a readable standard format.
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.
Creating a new data source using the Store Learn database contains several examples of this specific Unix Time conversion. We will walk through the steps of creating the derived table, "user_date_created."
If you would like to learn more about data sources, click here for more information.
1. From the Lambda Analytics homepage, click Create New under the Data Sources link on the top navigation bar.
2. Select the Store Learn database and click OK. Then, select the Totara schema.
3. On the Data Structures panel, click on the Totara schema to select all the necessary tables for your data source. For this case study, we will only be selecting the mdl_user table.
4. Once you are complete, navigate to the Joins tab.
5. On the Data Structures panel, hover over the Add icon and click Create a Derived Table.
6. Enter a name for your new derived table. Ensure that it is all lowercase, and that there are underscores instead of spaces. We will call this derived table "user_date_created."
7. In the Query field, enter the following query, and click Run Query.
SELECT from_unixtime(cast(mdl_user.timecreated as integer)) AS user_timecreated, mdl_user.id FROM mdl_user
8. Once the query has been successfully ran, click Create Derived Table.
9. Expand both your new derived table and the mdl_user table.
10. Under the mdl_user table, drag the id column onto the empty Joins panel.
11. Then, under your new derived table, drag the id column to the 'Drag a Field Here' placeholder to create a new Join. Ensure that the Join is set to Inner.
If you would like to learn more about creating Joins, click here for more information.
12. Once you are complete, click Save and enter a name for your new data source. Your new data source can now be used in Ad Hoc views and Reports, with the Unix Time columns being displayed in normal time.