Unix Time Conversion

Concepts covered: Timestamp Types

Unix time, also called epoch time, stores a moment as a single number: the seconds since 1970-01-01 00:00:00 UTC. The lesson's clock, 2024-03-15 14:30:45 UTC, is 1710513045. The number has no zone, so it names the same instant in Tokyo and in Lisbon, which is why event logs and APIs use it so often.

• 10 digits
• 13 digits

Count the digits before converting anything. Seconds and milliseconds for the same moment differ by a factor of 1,000, and both begin with 17 for any moment between November 2023 and January 2027, so the leading digits say nothing about the unit.

From epoch to timestamp

from_unixtime(seconds, zone) turns an epoch number into a TIMESTAMP WITH TIME ZONE, displayed on the clock of the zone you pass. A milliseconds column is divided by 1000 first. Cast it to DOUBLE before dividing, because BIGINT / BIGINT is integer division and throws the milliseconds away.

Divided

Milliseconds become seconds with the fraction kept, so event 2 shows its 250 milliseconds.

SELECT
  event_id,
  event_ms,
  from_unixtime(CAST(event_ms AS DOUBLE) / 1000, 'UTC') AS event_at
FROM app_events

Int divide

BIGINT division truncates before the conversion, so event 2 loses its 250 milliseconds.

SELECT
  event_id,
  from_unixtime(event_ms / 1000, 'UTC') AS event_at
FROM app_events

Not divided

Read as seconds, a milliseconds value lands beyond the year 50,000.

SELECT
  event_id,
  from_unixtime(CAST(event_ms AS DOUBLE), 'UTC') AS event_at
FROM app_events

A column clicked_at_ms holds 1710513045250. Which expression returns the click as a timestamp with its milliseconds intact?

• from_unixtime(CAST(clicked_at_ms AS DOUBLE) / 1000, 'UTC')
• from_unixtime(clicked_at_ms / 1000, 'UTC')
• from_unixtime(CAST(clicked_at_ms AS DOUBLE), 'UTC')
• to_unixtime(CAST(clicked_at_ms AS DOUBLE) / 1000)

From timestamp to epoch

to_unixtime(ts) goes the other way and returns a DOUBLE of seconds, fractions included. Multiply by 1000 and cast to BIGINT to match a milliseconds column. The cast rounds to the nearest whole number, which is exact for a timestamp stored to the millisecond.

SELECT
  session_id,
  CAST(to_unixtime(started_at) * 1000 AS BIGINT) AS started_ms,
  CAST(to_unixtime(ended_at) - to_unixtime(started_at) AS BIGINT) AS seconds_passed,
  date_diff('day', started_at, ended_at) AS days_passed
FROM sessions

Subtracting 2 epoch values always gives time passed. Session 2 crossed midnight and still reads 280 seconds. Session 3 ran 86399 seconds, 1 short of a full day, so date_diff reports 0 days for it.

Dates count from 1970 too

Some file formats store a DATE as whole days since 1970-01-01. date_add('day', 19797, DATE '1970-01-01') turns day 19797 back into 2024-03-15, and date_diff('day', DATE '1970-01-01', d) gives the day number of any date d.

One instant, many wall clocks

The zone argument changes only how the instant is displayed and which parts EXTRACT reads from it. from_unixtime(1710513045, 'UTC') shows 14:30:45 and from_unixtime(1710513045, 'America/New_York') shows 10:30:45 the same morning, so hour() returns 14 for one and 10 for the other. Group a day's events by hour in the zone your users live in, or the morning rush lands at the wrong hours.

SELECT
  event_id,
  hour(from_unixtime(event_s, 'UTC')) AS utc_hour,
  hour(from_unixtime(event_s, 'America/New_York')) AS ny_hour,
  day(from_unixtime(event_s, 'America/New_York')) AS ny_day
FROM events

Event 3 is midnight on March 16 in UTC and 8 PM on March 15 in New York, so even its day depends on the zone you convert into.

Events arrive with event_ms in epoch milliseconds. Return each event's id and the hour it happened on New York clocks, keep only events from midnight UTC on March 15, 2024 onward (1710460800 in epoch seconds), and sort by that hour.

About this lesson

This section is part of the Dates: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.