Working with Unix Time: EPOCH

Unix time is just a way to record time as the number of seconds (sometimes milliseconds) since midnight, January 1, 1970, UTC. This format is super popular in development: it’s easy to store, transfer, and compare, since it’s just a number.

To put it simply, Unix time is like a timer that started ticking in 1970 and never stopped. See a weird number like 1697222400? Don’t freak out — that’s just how many seconds have passed since the beginning. Want to figure out what date that is? We’ll learn how to do that in a sec!

This format is often used when syncing data between systems, for storing timestamps, and whenever you need to quickly compare what happened earlier or later.

Converting to Unix Time

PostgreSQL has a special function EXTRACT(EPOCH FROM ...) that lets you convert a date or timestamp to Unix time.

Let’s convert the current date to this format:

SELECT EXTRACT(EPOCH FROM NOW());

The result will look something like:

1697222400

This result is just the current date and time in seconds since 1970.

Here’s how you can convert a fixed date:

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-10-01 12:00:00');

Result:

1696152000

Now we know that October 1, 2023 at 12:00 UTC matches this value.

Converting from Unix Time

Of course, there’s also a way to convert back. If you have Unix time and want to turn it into a human-friendly format, the TO_TIMESTAMP() function is your friend.

Let’s convert Unix time to a date and time:

SELECT TO_TIMESTAMP(1697222400);

Result:

2023-10-13 00:00:00+00

Now we see that this is October 13, 2023 at 00:00 UTC.

Practical Use of Unix Time

Let’s say we have a table called events where we store info about events, including their timestamps in Unix time format.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_time BIGINT -- for storing Unix time
);

We can insert data into this table, specifying timestamps in Unix time:

INSERT INTO events (event_name, event_time)
VALUES
('Server launch', 1697222400),
('Database update', 1697308800);

To turn this info into something readable, just use TO_TIMESTAMP:

SELECT event_name, TO_TIMESTAMP(event_time) AS readable_time
FROM events;

Result:

Tips and Common Mistakes

1. Wrong precision

If your Unix time format uses milliseconds (like 1697222400000), you can’t just pass it straight to TO_TIMESTAMP or use it in EXTRACT. In these cases, you gotta divide the value by 1000:

SELECT TO_TIMESTAMP(1697222400000 / 1000);

Result:

2023-10-13 00:00:00+00

2. Ignoring time zones

Unix time is in UTC, so results will always be timezone-free unless you specifically convert the time. For example:

SELECT TO_TIMESTAMP(1697222400) AT TIME ZONE 'Europe/Moscow';

Result:

2023-10-13 03:00:00

3. Problems with numbers that are too big

Sometimes devs accidentally pass a huge Unix time value (like milliseconds instead of seconds). This gives you bogus results:

SELECT TO_TIMESTAMP(1697222400000); -- This is not an error, but the result is meaningless: PG interprets 1697222400000 as seconds (not milliseconds) and returns a date far in the future (~year 55549). Correct: TO_TIMESTAMP(1697222400000 / 1000.0).

To fix it, just make sure you divide the number by 1000.

Example: Calculating Task Duration

Let’s take a real-life example: we have a tasks table, and we want to figure out how much time has passed since each task was created.

Create the table:

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    task_name TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Add a few tasks:

INSERT INTO tasks (task_name)
VALUES
('Task 1'),
('Task 2'),
('Task 3');

Now we can add a column with Unix time:

SELECT id, task_name, EXTRACT(EPOCH FROM created_at) AS created_epoch
FROM tasks;

Result:

And let’s calculate how much time has passed since creation in seconds:

SELECT id, task_name, EXTRACT(EPOCH FROM NOW()) - EXTRACT(EPOCH FROM created_at) AS elapsed_seconds
FROM tasks;

Result:

When and Why Use Unix Time?

Unix time is perfect for storing time data when you’re passing values between systems, comparing timestamps, and other stuff where you want minimal CPU overhead. It’s used all over the place in APIs, web development, analytics, and for syncing server and client apps. But always keep its quirks in mind: it’s UTC-based, can use different units (seconds or milliseconds), and has its own storage format.

If you ever need to integrate with external systems that “speak Unix time,” now you know how to handle it in PostgreSQL. Go ahead and use TO_TIMESTAMP and EXTRACT(EPOCH) to convert back and forth, and all your time-related tasks will be a breeze!