[Edit]

PostgreSQL - count rows per day

1 contributors

2 contributions

0 discussions

0 points

Created by:

Amir-Hashempur

607

In this article, we would like to show you how to count rows per day in PostgreSQL.

Quick solution:

xxxxxxxxxx
 
SELECT 
    DATE_TRUNC('day', "datetime_column1") AS "alias1", 
    COUNT("datetime_column1") AS "alias2"
FROM "table_name"
GROUP BY DATE_TRUNC('day', "datetime_column1");

Practical example

Edit

To show how to create or replace a view, we will use the following table:

PostgreSQL - count rows per day — PostgreSQL - example data used to count rows per day

Note:

At the end of this article you can find database preparation SQL queries.

Example

Edit

In this example, we will display the date and the number of registered users for each day.

Query:

xxxxxxxxxx
 
SELECT 
    DATE_TRUNC('day', "registration_time") AS "day", 
    COUNT("registration_time") AS "number_of_users"
FROM "users"
GROUP BY DATE_TRUNC('day', "registration_time");

Result:

Database preparation

Edit

create_tables.sql file:

xxxxxxxxxx
 
CREATE TABLE "users" (
    "id" SERIAL,
    "username" VARCHAR(50) NOT NULL,
    "registration_time" DATETIME NOT NULL,
    PRIMARY KEY ("id")
);

insert_data.sql file:

xxxxxxxxxx
 
INSERT INTO "users"
    ("username", "registration_time")
VALUES
    ('Tom', '2021-01-01 11:41:31'),
    ('Chris','2021-01-02 11:42:45'),
    ('Jack','2021-01-03 15:13:39'),
    ('Kim','2021-01-03 15:24:51'),
    ('Marco','2021-01-04 22:35:38'),
    ('Kate','2021-01-04 22:46:51'),
    ('Nam','2021-01-04 22:57:37');