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!
