How to convert epoch time to usable timestamp in visualizations

A lot of times you have data in seconds since EPOCH, and want to use it within Arcadia to visualize as a more standard time format like month and year.

Step 1 - setup calculation using from_unixtime()

  • Make sure your Epoch time is in seconds. Values such as 1499396705000 are in milliseconds so we want to divide this number by 1000 for easy processing.

  • If you have data in STRING make sure it’s casted as BIGINT

The function we will use is from_unixtime as shown here:

from_unixtime(cast(cast([_time_epoch] as BIGINT)/1000 as BIGINT))

Here’s an example output:

Step 2 - Save as calculated field in dataset

To make subsequent steps easier, make sure to save your custom expression as calculated column in the dataset.

You can do this using the “Save Expression” options
image image

Step 3 - Use saved dataset field to create visuals

Once you have your timestamp conversion field saved, you can start using it in your visualizations.

4 - date manipulations using trunc() function

To do further time manipulations you can use the “trunc” function as mentioned here:

We can also uncheck the “Auto-detect time as continuous” for better control as explained here:

1 Like