Relative Dates & Times in PostgreSQL

Recently I have been doing some web scraping in Kotlin, and have now got a regularly updated set of records appearing in my database.

An interesting challenge arose when the scraped data was not quite as nice to work with as the web view I was scraping from. With a little front end whizz-bangery, the data I’d see in the browser would have relative times, whereas the data I scrape contains a Unix epoch timestamp (something like: 1684660500).

If you’re not familiar with this, a Unix epoch timestamp represents the number of seconds that have elapsed since the Unix epoch, which is January 1, 1970, 00:00:00 UTC.

In other words, January 1, 1970, 00:00:00 UTC is 0.

1684660500 is the number of seconds that we have all enjoyed since that very specific point in time. And by “all enjoyed” I mean, we have all enjoyed some of them, but maybe not all of them. Personally I never wore flares.

Setup

For this I will make use of Docker.

There will only be one Docker container, which is setup as follows:

# docker-compose.yaml

version: '3.9'
services:
  db:
    image: postgres:15.2
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: app_db
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: my_top_secret_password
    volumes:
      - "./init.sql:/docker-entrypoint-initdb.d/init.sql"Code language: YAML (yaml)

The magic happens thanks to the volumes section.

The Docker Postgres image will look for a file at the path /docker-entrypoint-initdb.d/init.sql when it first comes online. Note, this is when it first comes online, not every time it comes online. More on this in a moment.

We have mapped the local path ./init.sql to represent that init.sql file inside the resulting container.

The contents of init.sql are below.

Setup & Teardown

As above, the init.sql file will run when the container is first brought online:

docker-compose up

You can exit out of this by pressing ctrl + c

This will exit the container, but it will not delete the container. Sometimes you won’t want the container to be deleted. Other times you will.

If you wish to delete and recreate the database – something that is pretty hand to do frequently in test – the way you do that in this example is to delete the entire container, and recreate:

docker ps -a  

CONTAINER ID   IMAGE                        ... other stuff
1b2ade2d777c   postgres:15.2          ... other stuffCode language: CSS (css)

By using docker ps -a you will see all your containers, both running and exited.

You can then delete the container by providing enough of a unique match for the container id:

docker rm 1b2ade2d777c
docker rm 1b2
docker rm 1

So long as the id is unambiguous, you can provide as small a part of it as you can get away with.

Cool, so that’s how to set up and tear down your dockerised environment.

What We Have

Right, so we have a database. Let’s provide that init.sql script to create a database table and populate it with some data:

-- init.sql

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  created_at INTEGER NOT NULL
);

INSERT INTO products (name, created_at)
VALUES 
       ('Product A', EXTRACT(EPOCH FROM TIMESTAMP '2023-05-21 10:30:00')),
       ('Product B', EXTRACT(EPOCH FROM TIMESTAMP '2023-05-21 15:45:00'));Code language: SQL (Structured Query Language) (sql)

That’s going to create us a database table called products, and insert a couple of fake records that will allow us to test this whole shebang.

Note the lovely use of EXTRACT(EPOCH FROM TIMESTAMP '2023-05-21 10:30:00') which allows us to work with human readable dates and times in our text file, but write an integer representation to the database. Perfect for our needs.

Here’s our starting SQL query:

select id, name, created_at
from products;Code language: SQL (Structured Query Language) (sql)

And the result:

What We Want

OK, so we have a fake set of data with this valid, but not very friendly created_at integer timestamp.

What I’d like to do is get back some kind of human friendly display value, similar to what is seen on the web page.

So if the timestamp is 30 seconds ago, I want to display that.

If the timestamp is 25 minutes and 14 seconds ago, I want to display “25 minutes ago”.

If the timestamp is 3 days 11 hours 49 minutes and 13 seconds ago, I want to display “3 days ago”.

In other words, I want a relative date and time.

The question is: is it possible to do that using only SQL?

And the answer is: yes.

A Basic SQL Query For Relative Dates & Times

Probably best if we see the query, and then try to dissect it:

select id,
       title,
       content,
       created_at,
       to_char(timestamp 'epoch' + created_at * interval '1 second', 'yyyy-mm-dd hh24:mi:ss') as human_readable_date,
       case
           when age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second')) < interval '1 minute'
               then
                   extract(second from
                           age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) ||
                   ' seconds ago'
           when age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second')) < interval '1 hour' then
                   extract(minute from
                           age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) ||
                   ' minutes ago'
           when age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second')) < interval '1 day' then
                   extract(hour from age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) ||
                   ' hours ago'
           else
                   extract(day from age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) ||
                   ' days ago'
           end as relative_time
from posts;
Code language: SQL (Structured Query Language) (sql)

This one’s a bit of a nightmare to format, sadly.

Lines 5 through 22 are the new things.

Line 5 can be taken in isolation. Then lines 6 through 22 are all about the relative date or time display.

Here is an example of the output:

Let’s break this down.

Human Readable Date

In our init.sql insertion script, we had something like the following:

EXTRACT(EPOCH FROM TIMESTAMP '2023-05-26 12:30:00')Code language: SQL (Structured Query Language) (sql)

And what that did was allow us to work with human readable dates in our script, but convert them to integer timestamps when they ended up in the database.

Line 5 is helping us reverse that process:

to_char(timestamp 'epoch' + created_at * interval '1 second', 'yyyy-mm-dd hh24:mi:ss') as human_readable_date,Code language: SQL (Structured Query Language) (sql)

It’s quite hard to read as a one liner, so let’s break this up a bit using nicer formatting:

-- use the to_char function
to_char(
   -- do this calculation
   timestamp 'epoch' 
       + 
   created_at * interval '1 second',
   -- and then output as this format:
   'yyyy-mm-dd hh24:mi:ss'
) 
-- and display this value in our query result as...
as human_readable_date
Code language: SQL (Structured Query Language) (sql)

Everything is wrapped in the to_char function. We will come back to that.

Calculate The Timestamp As An Interval

Let’s start on line 4 with timestamp 'epoch'.

We’ve already talked about this, but the interesting thing here is that you can select this and get the value. The result is a timestamp value at the epoch start.

On line 6 we have the second part of the calculation: created_at * interval '1 second'

Again, we can do a select here to show us what we end up with:

In this case, we get back the two rows in our products table.

We get the created_at timestamp we already know about, and then this ‘interval’ value.

The interval value works fairly intuitively. Here’s some other outcomes:

The query for reference:

select interval '1 second' as interval_value
union all
select interval '43 hours' as interval_value
union all
select interval '19 days' as interval_value
union all
select interval '35 months 42 days 11 seconds' as interval_value
union all
select interval '100 years' as interval_value;Code language: SQL (Structured Query Language) (sql)

The only reason for using union all there was to get the interval_value outputs as a single column, rather than all in a row – display reasons, basically.

Back to the computation:

   timestamp 'epoch' 
       + 
   created_at * interval '1 second',Code language: JavaScript (javascript)

So a timestamp at the epoch (or 0), plus the created_at timestamp (e.g. 1684584000) which is multiplied (or converted) to an interval where every digit represents one second.

By multiplying this interval with the created_at value, we get the duration in seconds between the two timestamps.

Now, we add this duration to the Unix epoch timestamp. This gives us a new timestamp value that represents the actual date and time of the created_at timestamp, measured from the Unix epoch.

Format The Timestamp As A String

Back to the code for a quick recap:

to_char(
   -- ... we just did this bit,
   'yyyy-mm-dd hh24:mi:ss'
) Code language: SQL (Structured Query Language) (sql)

We’re going to use to_char to format the resulting timestamp calculation as text.

This might seem a little pointless – I will leave this for you to decide.

Why might it seem pointless? Well, let’s take a look at the query steps along the way:

Above we do the calculation step for both rows in the product table, and store the result for output as intermediate_step.

We don’t actually need to ‘name’ this step in our function. I’m doing it purely for display reasons above.

The output of intermediate_step already looks like a nicely formatted date. Why do we then need to convert that using to_char?

Well, it’s to do with the type of data we have. What we actually have here is a timestamp without a timezone. DataGrip – the JetBrains IDE I am using for my DB interactions – formats this for me in a nice fashion. But I am trying to replicate the web front end approach – and I would expect this to be a string.

Like I say, maybe this is pointless. That’s up to you.

But regardless, it’s interesting… and it’s my blog post, so I can do whatever I like 😀

Anyway, rather than you simply taking my word for it, let’s see the underlying type of intermediate_step:

select pg_typeof(intermediate_step) as intermediate_step_type
from (
    select (timestamp 'epoch' + created_at * interval '1 second') as intermediate_step
    from products
) as subquery;Code language: SQL (Structured Query Language) (sql)

It might not matter so much here, but getting control of your types can save headaches in many day to day developer circumstances.

Anyway, we’ve already seen how to convert this to a string. Or, more accurately, text, but let’s confirm this anyway:

A lot of work to validate one line of code. But I feel it’s important to understand exactly what is happening, rather than blindly copy / pasting crap you find on the internet, and hoping for the best.

Back to the original query.

Case In Point

The second part of this adventure is the case statement.

A case statement is a little like an if statement in SQL and can be quite powerful, but also quickly add a lot of noise to your SQL queries.

At a very high level we are using various functions to calculate the time elapsed between the current timestamp and the value of a created_at column.

We have already seen some of these commands, so we will only focus on the new additions for this section.

Let’s cover off just a subsection of the case statement:

when age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second')) < interval '1 minute'
   then
       extract(second from
               age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) ||
       ' seconds ago'Code language: SQL (Structured Query Language) (sql)

That’s still a lot of stuff, so let’s first try to format it in a slightly more readable fashion:

when 
    age(
        current_timestamp, 
        (timestamp 'epoch' + created_at * interval '1 second')
    ) < interval '1 minute'
   then
       extract(
            second from
               age(
                    current_timestamp, 
                    (timestamp 'epoch' + created_at * interval '1 second')
               )
       ) || ' seconds ago'Code language: SQL (Structured Query Language) (sql)

And now let’s tackle this by first looking at the when, and after that we will cover the then.

Inside The When Clause

This first section looks similar to what we had in the previous section:

    age(
        current_timestamp, 
        (timestamp 'epoch' + created_at * interval '1 second')
    ) < interval '1 minute'
Code language: SQL (Structured Query Language) (sql)

This time we have age instead of to_char, and we have a condition in the form of “less than an interval of 1 minute”.

On line 3 in the snippet above, we run that same computation to convert the created_at integer value into an interval that Postgres can work with.

Because of that, we can then use the age function that comes built into Postgres:

We’re passing two timestamps to the age function so will be using the first definition.

As the function signature describes, this will return an interval.

Because we then have an interval, we can use the usual comparison operators to compare two values of the same type:

Just like in an if statement, if that age function returns true, we run the then block. And if it’s false, we don’t.

So here we ask SQL to check if the difference between the current time and the created_at time less than 1 minute.

Inside The Then Clause

Assuming that our when clause returns true, then the then clause will be processed.

Here’s that code:

       extract(
            second from
               age(
                    current_timestamp, 
                    (timestamp 'epoch' + created_at * interval '1 second')
               )
       ) || ' seconds ago'Code language: SQL (Structured Query Language) (sql)

This is really where the magic happens.

extract is another one of Postgres’s provided date / time functions that can work on interval types:

In this specific when clause, we extract the second‘s value from the interval.

Remember this screenshot:

The when clause explicitly checked that this record was created_at within a minute of the current time.

Therefore we extract only the second‘s value from the interval. And we can show between 1 and 59 seconds ago.

We do that by using the second value we extract, and using the || operator to concatenate the extracted number of seconds at the string ' seconds ago'. Note the leading space.

Reviewing The Full Case

And that’s really all there is to this.

What looked very complicated to begin with, is now four different variations on the theme.

       case
           when age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second')) < interval '1 minute'
               then
                   extract(second from
                           age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) ||
                   ' seconds ago'
           when age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second')) < interval '1 hour' then
                   extract(minute from
                           age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) ||
                   ' minutes ago'
           when age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second')) < interval '1 day' then
                   extract(hour from age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) ||
                   ' hours ago'
           else
                   extract(day from age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) ||
                   ' days ago'
           end as relative_timeCode language: SQL (Structured Query Language) (sql)

Look at each when.

The difference is the compared interval. First minutes, then hours, then days.

And then the then.

What we change is the piece of the interval that we are interested in.

Again, seconds, minutes, hours, or days.

The only slight variation on this theme is that the final case, the else doesn’t have a conditional. It’s the catch all if nothing else fits.

Refactoring The SQL

We saw right back at that start that this code does work:

But along the way we have said that this big case statement is noisy.

And what if you need it in multiple places? Imagine copy / pasting that bad boy into various queries you have in your codebase. No one would like you. For me that’s not a problem, as not so many people like me to begin with, but I wouldn’t want you to lose friends over this sort of thing.

So how can we improve it?

Well, by extracting this case statement out to its own function, of course!

Let’s see how that can be done:

create or replace function get_relative_time(created_at integer)
returns text as $$
begin
    return case
        when age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second')) < interval '1 minute' then
            extract(second from age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) || ' seconds ago'
        when age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second')) < interval '1 hour' then
            extract(minute from age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) || ' minutes ago'
        when age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second')) < interval '1 day' then
            extract(hour from age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) || ' hours ago'
        else
            extract(day from age(current_timestamp, (timestamp 'epoch' + created_at * interval '1 second'))) || ' days ago'
    end;
end;
$$ language plpgsql;Code language: SQL (Structured Query Language) (sql)

This function is named get_relative_time, and it accepts the created_at argument of type integer. It returns the relative time as a string.

Everything inside the case statement remains as we have already seen.

The new stuff is one way functions can be defined inside Postgres.

The code begins with CREATE OR REPLACE FUNCTION. This is the starting point to create a new function or update an existing one if it already exists. It sets the foundation for defining the function.

The function’s name is get_relative_time. You can choose any name you like for your function.

Inside the parentheses (created_at integer), we define the input parameter and the type of that parameter for our function.

Next, we see RETURNS TEXT, which indicates the type of value the function will give us as a result. Here, the function will provide us with a relative date / time text representation, as we have already covered.

The AS $$ part signals the start of the function’s body.

Everything between BEGIN and END is the actual code that defines what the function does.

Finally, $$ LANGUAGE plpgsql; specifies the programming language used for the function. In this case, it uses the PL/pgSQL language, which is a specific language designed for working with PostgreSQL. We don’t have time to go into that here, but it’s just one of the available languages, although it’s the only one I have any experience with.

After we run that block, we should see the function – called a routine – displayed in DataGrip as above.

With that, we can update the original query to be far more succinct:

select id,
       title,
       content,
       created_at,
       get_relative_time(created_at) as relative_time
from posts;Code language: SQL (Structured Query Language) (sql)

And finally, we are done:

Of course there’s room for improvement.

1 days ago is jarring.

But hey, good enough for my needs. Overkill, even.

Still, we all learned something, right?

Good.

See you next time. Don’t forget to like, subscribe, hit the bell, buy a cat, feed the dog, and water the plants.