Using timezone on date fields

Objectives

By default, all dates in the Zetaly database are in UTC to ensure data consistency. However, you may want to expose this data on specific times zones for users. This article provides a step-by-step solution for adapting a sample Zetaly dashboard to load data on a selected time zone.

Update an existing cube

First, you need to access the desired cube. For our example, we'll use the cube named zc_si.a.e_CPU_Activity_sample.

Retrive the table name

Select the fact table and display the query used by the bi to gather data:

In our example, we are doing a selection of all column from the view "public"."fact_cpu_activity_sample". The aim is to modify this query to select exactly the same data, but add a date conversion function.

Extract column list

Next, you need to list all the columns in the fact table and identify the date columns to be modified.

There are several ways of extracting this list:

• You can copy/paste this list directly from the webpage
• Execute an SQL request

Copy/paste

All columns can be found directly in the model, on the left-hand side. The list can be selected and copy/paste directy using ctrl+C and ctrl+V:

Execute SQL request

The list can be obtained be executing a SQL request. Create a new data table as usual, but instead of selected a table, create a query table:

Copy/paste the following request (update the table_name with the table used by the fact table):

SELECT string_agg(column_name, ', ')
FROM information_schema.columns
WHERE table_name = 'fact_cpu_activity_sample';

Update the cube

Once you've retrieved and identified the date columns to be modified, select the fact table and display the query used by the bi to gather data:

Create a custom table:

Copy and paste the previous query, then replace the '*' by the column list.
Before:

SELECT * FROM "public"."fact_cpu_activity_sample"

After:

SELECT record_time, system_name, cec_name, main_memory_mb, type_num, model_num, seq_num, sid, cpus, cp_cpus, se_cpus, ifl_cpus, icf_cpus, cec_cap_msu, imsu, se_time, cp_time FROM "public"."fact_cpu_activity_sample"

Once it's done, you need to apply the time zone to your date column. We need to add the following function: AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Paris' (replace the time zone name 'Europe/Paris' by the desired one. A partial list can be found here: https://bill.harding.blog/2020/03/21/list-of-postgres-11-time-zones/)

After:

SELECT record_time AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Paris' AS record_time, system_name, cec_name, main_memory_mb, type_num, model_num, seq_num, sid, cpus, cp_cpus, se_cpus, ifl_cpus, icf_cpus, cec_cap_msu, imsu, se_time, cp_time FROM "public"."fact_cpu_activity_sample"

Once it's done, you can parse and preview the result. Next, you can validate and rebuild the cube.