Introduction to Unix timestamp
Epoch/Unix time, also known as Unix timestamp, is a widely used system for representing time as a numerical value. It measures time as the number of seconds (though can also be milliseconds, microseconds and nanoseconds) that have elapsed since January 1, 1970, at 00:00:00 UTC (Coordinated Universal Time). While Unix timestamps are convenient for calculations and machine-readable applications, they are not easily understandable for humans. In this article, we will explore how to convert epoch/unix time to a human-readable format all within Sigma – without needing any additional programming languages and tools.
GA4 Use-case
A common place this use-case shows up is with Google Analytics 4 (GA4): a powerful web analytics platform offered by Google. Data from GA4 encompasses a wide range of information related to user interactions on websites, mobile apps, and other digital properties. It can be used to provide valuable insights into user engagement, website performance, marketing effectiveness, and customer journeys, enabling businesses to make data-driven decisions, optimize their online presence, and improve overall user experiences.
Let’s get started!
Step 1: Convert to human-readable format
To start we will be utilizing the “Event Timestamp” column, which in this case is in microseconds. To convert this to human-readable format we will use the following function which (1) convert “Event Timestamp” to milliseconds and (2) add this to January 1st, 1970.
DateAdd("millisecond", [Event Timestamp] / 1000, Date("1970-01-01"))
Step 2: Format for just date
This is currently in date time format. Formatting this to just date is as simple as using our point-and-click dropdown menu.
Step 3: Format for just time
Now if we want to break out just the time down to the millisecond for things like user session analyses we can utilize Sigma’s custom data formatting functionality. For today’s example we will click on the dropdown caret → format → custom and enter the following d3-format (for a full list of formatting options, see here).
%H:%M%:%S.%L
- %H = Hour (24-hour clock) as a decimal number (00–23)
- %M = Minute as a decimal number (00–59)
- %S = Second as a decimal number (00–60)
- %L = Milliseconds as a decimal number (000–999)
Step 4: Analyze your data!
From here you can start leveraging the full power of Sigma on time-series analyses in ways that are much more easily digestible for your end users. For some more ways to apply this, see additional articles on marketing analytics and working with user sessions.
Note: as of the posting of this article the utilization of microseconds in the DateAdd() function is not currently available but is in production.