Brightlane's monitoring platform forwards alert events to a downstream system that represents time as Unix seconds — the count of seconds elapsed since 1970-01-01 00:00:00.
Write a query to return the Unix epoch seconds for the timestamp '2024-01-01 00:00:00'.
Output:
- A single row with one column,
unix_seconds, containing the elapsed seconds as a number.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
EXTRACT(
epoch
FROM
'2024-01-01 00:00:00'::TIMESTAMP
) AS unix_secondsThe shape
EXTRACT(epoch FROM ...) converts a datetime to the number of seconds elapsed since 1970-01-01 00:00:00 UTC and returns a floating-point number. The midnight-on-New-Year's-Day timestamp returns 1704067200, which is the Unix-seconds value the downstream monitoring system stores.
Clause by clause
SELECT EXTRACT(epoch FROM '2024-01-01 00:00:00'::timestamp)evaluates the extraction against a single literal. The::timestampcast resolves the string as a timezone-naive datetime, andEXTRACT(epoch FROM ...)interprets a naive timestamp as if it were UTC for the purpose of counting seconds since the epoch. The result is the count from the epoch to that instant.AS unix_secondslabels the output as the elapsed-seconds value the alert forwarder expects to receive.
Why epoch and not extracting year, month, day separately
The receiving system reads time as a single number, not as a structured datetime. Producing the year, the month, the day, the hour, the minute, and the second separately would mean the consumer has to reassemble them back into an absolute time, which is the work epoch already does in one call. EXTRACT(epoch FROM ...) is the canonical bridge between PostgreSQL's datetime types and any external system that represents time as a number of seconds since 1970.
The trap
The return type is double precision. A bigint consumer that expects an integer Unix timestamp will see a floating-point number with a fractional part for any input that has sub-second precision. The fractional component is the microsecond resolution PostgreSQL preserves through the conversion, and it is correct, but it can collide with a downstream schema that declares the column as an integer. When the consumer is strict about the type, cast the result explicitly to the target numeric type rather than letting an implicit conversion drop the fractional part silently.
You practiced EXTRACT(epoch FROM ...) — convert a datetime to a single floating-point number, the standard bridge between SQL datetime types and systems that store time as Unix seconds.
