Volver

Query data from PostgreSQL to represent it in a time series graph

Diario del capitán, fecha estelar d384.y40/AB

PostgreSQL Databases
Fundador & CTO
Query data from PostgreSQL to represent it in a time series graph

In this blog post, I will show you how to query data from PostgreSQL to represent it in a time series graph.

If we have a table named historic with the following structure:

    id: integer
    timestamp: timestamp
    value: integer

We can query this data by intervals by using the following query:

    SELECT
        day_timestamp,
        avg(historic.value)
    FROM
        generate_series(timestamp '2023-05-01', timestamp '2023-07-01', interval '1 day') AS day_timestamp
        LEFT OUTER JOIN historic ON (date(historic.timestamp) = date(day_timestamp))
    GROUP BY
        day_timestamp
    ORDER BY
        day_timestamp

Here, I am joining data between 2023-05-01 to 2023-07-01 from the historic table and I am performing an AVG in case there is more than one record in the same day. We can use any other aggregation function instead, like COUNT, SUM, etc.

The important function of this table is the generate_series that allows me to generate records between two values and a period. By joining and grouping the resulting data, we get the desired results.

If we want to group by months instead, then we just need to change the generate_series interval to 1 month and change the conditions in the LEFT OUTER JOIN to compare between the month and the year of the dates.

Limitations

This query might not be performant for millions of historic records, but for small to medium sized projects, this should be more than enough.

To optimise this query we can create functional indices to index the timestamp in its date, month or year forms, or store the month and year as separate columns in the database and add indices for them.

Compartir este post

Artículos relacionados

One year of the MarsBased newsletter: data, learnings and lots of unknowns

One year of the MarsBased newsletter: data, learnings and lots of unknowns

After a year of sending our monthly newsletter, it's time to review how it's been and reflect on what we've done right and wrong.

Leer el artículo
Robocleaner

Remember to vacuum your databases!

There's a way to declutter PostgreSQL databases that will impact the performance of your projects. Learn about vacuum.

Leer el artículo
Disk

MySQL deferred constraints and unique checks

MySQL and PostgreSQL are very similar but not exact. Take a look at this scenario that works with PostgreSQL but not with MySQL.

Leer el artículo