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 Fix Micromax A28 Bolt Not Charging [Troubleshooting Guide]

Edge-AI Road Segmentation for Ride-share / Auto-pilot / Self-driving Applications

Winner of the Progate Star Learners Challenge, Problem Solver, App Developer, and Pro Coder!

SwiftUI folder structures as RN developer

Joining collections in MongoDB using the C# driver and LINQ

Building Something From Scratch Phase at Encora Apprenticeship — Week 4

A Failed Vasectomy Changed My Life

Using Azure Data Studio to connect to Postgres instance in Docker

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

Integrate Python and Oracle Databases

Utility Cost Tracker for Smart Home devices with Python and Django

Build your own website with Flask, Docker and Heroku for peanuts

Receive camera picture from user in Django