How to group rows by time in PostgreSQL (and in Django)

Setting up the database

create table temperatures (
value real,
measured_at timestamp with time zone
)
insert into temperatures 
select
random() * 40 + 1,
generate_series(
'2021-05-01 00:00:00'::timestamp,
'2021-05-31 23:59:59'::timestamp,
'10 second'
)
select * from temperatures limit 10

SQL to group by time

select distinct on (key) *, date_trunc('hour', measured_at) as key 
from temperatures
limit 20;

Use Django ORM to group rows by the time

from django.db import models

class Temperature(models.Model):
value = models.FloatField()
measured_at = models.DateTimeField()
from django.db.models.functions import Trunc

Temperature.objects.annotate(key=Trunc('measured_at', 'hour')).distinct('key').order_by('key')

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How to Become a Great C# Developer

Kubecost vs. CAST AI: A Perfect Match for Cost Optimization — CAST AI

Syntax Analysis in Compiler Design (Parsers)

Streaming Heart Rate Data with IoT Core and QuestDB

Import a CSV file into FreeAgent

Cycle Time: A Longitudinal Look

The Database is Just Another Microservice.

How I generated my personal landing page with Figma & Flutter using Parabeac

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alex Oleshkevich

Alex Oleshkevich

More from Medium

Python’s urllib. request for HTTP Requests

Simple Flask Application

Python MongoDB Tutorial: Getting Started with MongoDB in Python

Retrieve Twilio Call Logs Using Django