Understanding Timestamps in Labfront: Unix Time

This article explains the timestamps in your downloaded Labfront data and shows how to convert data provided in Unix time to local time.

In this article, we'll cover:

Time Data in Labfront

When viewing your data in Labfront, you'll see the following variables:

  • timezone - The geographic region based on the device settings.
    • Ex: Asia/Taipei
  • unixTimestampInMs - The timestamp of the corresponding data type in Unix time. It has 13 digits because it displays milliseconds.
    • Ex: 1676951478000
  • isoDate -  The converted local time in the standard format used to express a numeric calendar date.
    • Ex: 2023-02-21 11:51:18.000 +08:00


You can view our article on data metrics and meanings to better understand the various data types and variables. 


What is Unix time? 

Unix time is a system for representing timestamps as the number of seconds that have elapsed since January 1, 1970 00:00:00 UTC (epoch time). Since this point in time technically does not change no matter where you are located on the globe, it's very useful for tracking and sorting dated information.

Converting Unix time to local time

Option 1: Utilize the isoDate column to obtain the local time

The first option is also the simplest. As you can see in the following image, the isoDate is the local time with the timezone indicated at the end. No formula is necessary. 


Screen Shot 2023-03-20 at 2.08.42 PM

Option 2: Derive the local time through timezone and unixTimestampinMs

Here we'll show how to manually convert the timestamp to a more readable format.
Ex: 1676951478000 → 2023/02/21 11:51:18

  1. First, enter the following formula to covert from Unix timestamp to GMT+0: =unixTimestampInMs/1000/3600/24+offsetInHour/24+DATE(1970,1,1)

    Screen Shot 2023-03-20 at 3.36.32 PM
    In this example, you'd select B8 (unixTimestampinMs) and the offsetInHour is +8 (Asia/Taipei) so the formula is =B8/1000/3600/24+8/24+DATE(1970,1,1)

  2. Next, select the correct date formatting of the cell after converting from Unix timestamp to GMT+0.

    In Excel:
    1. Select the cell 
    2. Select ‘Format Cells’ from the context menu
    3. In the ‘Format Cells’ dialog box, under the ‘Number’ tab, click ‘Date’ in the Category list
    4. Select the appropriate Date format you want (you can use YYYY/MM/DD h:mm:ss)

Screen Shot 2023-03-20 at 2.26.36 PM

📽️ How-To Video

Need additional support? View our example using sample Labfront BBI data and Google Sheets.
HubSpot Video



Why does Labfront use Unix time?
The Unix timestamp is the number of seconds between a particular date and the Unix Epoch (Jan 1, 1970). Since this point in time technically does not change no matter where you are located on the globe, it's very useful for accurately comparing and manipulating dates and times across different platforms and time zones.
Do you offer any services to help analyze the data? 
We do! Our team of experts has over 20+ years of experience in digital biomarkers and research analytics. You can outsource your analytics to us and save up to several months of work. Learn more about Labfront Analytics here
Can I see a sample of the downloadable data format? 
You sure can! Take a look at a sample of our data here