How To Select Records from Last 24 Hours using PostgreSQL

Sometimes you may need to get records from past 24 hours or select rows from last 24 hours for reporting & analysis. Here is how to select records from last 24 hours using PostgreSQL.

Get Records from last 24 hours in PostgreSQL

Here is the SQL query to get records from last 24 hours in PostgreSQL. Let’s say you have the following table sales(order_date, amount).

postgres-# create table sales(order_date timestamp, amount int);

postgres-# insert into sales(order_date,amount) 
     values('2020-06-07 01:00:00',200),
     ('2020-06-07 02:30:00',350),
     ('2020-06-07 04:40:00',410),
     ('2020-06-07 12:10:00',600),
     ('2020-06-07 15:00:00',300),
     ('2020-06-07 18:55:00',450),
     ('2020-06-07 21:00:00',1200),
     ('2020-06-08 03:00:00',800),
     ('2020-06-08 05:30:00',900),
     ('2020-06-08 07:20:00',100),
     ('2020-06-08 10:10:00',250),
     ('2020-06-08 12:05:00',300),
     ('2020-06-08 13:30:00',200);

postgres-# select * from sales;
+---------------------+--------+
| order_date          | amount |
+---------------------+--------+
| 2020-06-07 01:00:00 |    200 |
| 2020-06-07 02:30:00 |    350 |
| 2020-06-07 04:40:00 |    410 |
| 2020-06-07 12:10:00 |    600 |
| 2020-06-07 15:00:00 |    300 |
| 2020-06-07 18:55:00 |    450 |
| 2020-06-07 21:00:00 |   1200 |
| 2020-06-08 03:00:00 |    800 |
| 2020-06-08 05:30:00 |    900 |
| 2020-06-08 07:20:00 |    100 |
| 2020-06-08 10:10:00 |    250 |
| 2020-06-08 12:05:00 |    300 |
| 2020-06-08 13:30:00 |    200 |
+---------------------+--------+

Bonus Read : How to Increase Max Connections in PostgreSQL

Get rows from past 24 hours in PostgreSQL

Here’s the SQL query to get records from last 24 hours in PostgreSQL.

postgres-# select * from sales
       where order_date > now() - interval '24 hours';
+---------------------+--------+
| order_date          | amount |
+---------------------+--------+
| 2020-06-07 15:00:00 |    300 |
| 2020-06-07 18:55:00 |    450 |
| 2020-06-07 21:00:00 |   1200 |
| 2020-06-08 03:00:00 |    800 |
| 2020-06-08 05:30:00 |    900 |
| 2020-06-08 07:20:00 |    100 |
| 2020-06-08 10:10:00 |    250 |
| 2020-06-08 12:05:00 |    300 |
| 2020-06-08 13:30:00 |    200 |
+---------------------+--------+

In the above SQL query, we use PostgreSQL system function now() to get current datetime. Then we use INTERVAL clause to select those rows where order_date falls within past 24 hours of present datetime.

Bonus Read : PostgreSQL Performance Tuning Tips

You can also specify time interval in days, instead of hours.

postgres-# select * from sales
     where order_date > now() - interval '1 day';
+---------------------+--------+
| order_date          | amount |
+---------------------+--------+
| 2020-06-07 15:00:00 |    300 |
| 2020-06-07 18:55:00 |    450 |
| 2020-06-07 21:00:00 |   1200 |
| 2020-06-08 03:00:00 |    800 |
| 2020-06-08 05:30:00 |    900 |
| 2020-06-08 07:20:00 |    100 |
| 2020-06-08 10:10:00 |    250 |
| 2020-06-08 12:05:00 |    300 |
| 2020-06-08 13:30:00 |    200 |
+---------------------+--------+

Bonus Read : Top 5 PostgreSQL Query Monitoring Tools

If you want to select records only for the present day, and not past 24 hours, use the following query.

postgres-# select * from sales
      where date_trunc('date',order_date) = current_date;
+---------------------+--------+
| order_date          | amount |
+---------------------+--------+
| 2020-06-08 03:00:00 |    800 |
| 2020-06-08 05:30:00 |    900 |
| 2020-06-08 07:20:00 |    100 |
| 2020-06-08 10:10:00 |    250 |
| 2020-06-08 12:05:00 |    300 |
| 2020-06-08 13:30:00 |    200 |
+---------------------+--------+

In the above query, you will get rows only present date, and not past 24 hours. We use DATE_TRUNCT function to select only those rows where the date value of order_date is same as date value of CURRENT_DATE system variable, that is, present date.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!