Converting Epoch Time (Millisecond) to TIMESTAMPTZ

Objective:

Learn to efficiently convert EPOCH time (milliseconds since Unix epoch) to TIMESTAMPTZ in Firebolt.

TL;DR:

To convert EPOCH time in milliseconds to TIMESTAMPTZ, use:

SELECT TO_TIMESTAMPTZ(epoch_in_milliseconds / 1000::NUMERIC(38, 3));

Replace epoch_in_milliseconds with your epoch time value/column.

Detailed explanation

Handling time-series data often requires converting epoch time to a human-readable timestamp format, especially when analyzing, reporting, and visualizing time-based data across multiple time zones.

Step-by-Step:

  1. Epoch Time Basics: Epoch time, or Unix time, counts milliseconds since 00:00:00 UTC on 1 January 1970. It's a common time format in many programming environments.
  2. Conversion Necessity: Transforming epoch time to TIMESTAMPTZ in Firebolt simplifies time data manipulation, making it more accessible and interpretable.
  3. Utilizing TO_TIMESTAMPTZ Function:Firebolt's TO_TIMESTAMPTZ function converts numeric epoch time (seconds since the Unix epoch) to TIMESTAMPTZ. For milliseconds, divide by 1000 and ensure numeric precision:
SELECT TO_TIMESTAMPTZ(epoch_in_milliseconds / 1000::NUMERIC(38, 3));

Conclusion:

This conversion method enables precise handling of epoch time within Firebolt, facilitating accurate time-based analysis and compatibility with various time zones.