An ‘interval’ in PostgreSQL represents a duration of time. It can be the result of subtracting two TIMESTAMP values or explicitly defined using the INTERVAL keyword.
Here’s an example:
SELECT INTERVAL '2 days 3 hours 40 minutes' AS example_interval;
Expected Output:
example_interval
-------------------
2 days 03:40:00
Extracting Days, Hours, and Minutes
Extracting Days
To extract the total number of days from an interval:
SELECT EXTRACT(DAY FROM INTERVAL '5 days 4 hours 30 minutes') AS total_days;
Expected Output:
total_days
------------
5
Extracting Hours
When extracting hours, the EXTRACT function only returns the hour component of the interval, not the total hours, including days. For example:
SELECT EXTRACT(HOUR FROM INTERVAL '2 days 4 hours 30 minutes') AS hours_component;
Expected Output:
hours_component
-----------------
4
To get the total hours, including days, you need to perform some arithmetic:
SELECT EXTRACT(DAY FROM INTERVAL '2 days 4 hours 30 minutes') * 24 +
EXTRACT(HOUR FROM INTERVAL '2 days 4 hours 30 minutes') AS total_hours;
Expected Output:
total_hours
------------
52
Extracting Minutes
Like hours, extracting minutes only returns the minute part of the interval:
SELECT EXTRACT(MINUTE FROM INTERVAL '2 days 4 hours 30 minutes') AS minutes_component;
Expected Output:
minutes_component
-------------------
30
To get the total minutes from the interval, you can calculate:
SELECT (EXTRACT(DAY FROM INTERVAL '2 days 4 hours 30 minutes') * 24 * 60) +
(EXTRACT(HOUR FROM INTERVAL '2 days 4 hours 30 minutes') * 60) +
EXTRACT(MINUTE FROM INTERVAL '2 days 4 hours 30 minutes') AS total_minutes;
Expected Output:
total_minutes
---------------
3150
Practical Examples
Let’s look at a practical example using a table. Suppose we have a table called events that stores the start and end timestamps for various events:
CREATE TABLE events (
event_id SERIAL PRIMARY KEY,
start_time TIMESTAMP,
end_time TIMESTAMP
);
INSERT INTO events (start_time, end_time)
VALUES
('2024-08-01 08:00:00', '2024-08-03 12:30:00'),
('2024-08-05 09:15:00', '2024-08-05 18:45:00');
We can calculate the interval for each event and extract total days, hours, and minutes:
SELECT
event_id,
end_time - start_time AS duration,
EXTRACT(DAY FROM end_time - start_time) AS total_days,
(EXTRACT(DAY FROM end_time - start_time) * 24 +
EXTRACT(HOUR FROM end_time - start_time)) AS total_hours,
(EXTRACT(DAY FROM end_time - start_time) * 24 * 60 +
EXTRACT(HOUR FROM end_time - start_time) * 60 +
EXTRACT(MINUTE FROM end_time - start_time)) AS total_minutes
FROM events;
Expected Output:
event_id | duration | total_days | total_hours | total_minutes
---------+---------------+------------+-------------+--------------
1 | 2 days 04:30 | 2 | 52 | 3150
2 | 09:30:00 | 0 | 9 | 570
Conclusion
Extracting days, hours, or minutes from an interval in PostgreSQL is straightforward using the EXTRACT function. For total counts, you will need to combine EXTRACT with arithmetic operations. These techniques are very useful for analyzing time-based data in your applications.
Other articles you may enjoy:
Το Beekeeper Studio Είναι Ένα Δωρεάν & Ανοιχτού Κώδικα GUI Βάσης Δεδομένων
Το καλύτερο εργαλείο SQL query & editor που έχω χρησιμοποιήσει. Παρέχει όλα όσα χρειάζομαι για να διαχειριστώ τη βάση δεδομένων μου. - ⭐⭐⭐⭐⭐ Mit
Το Beekeeper Studio είναι γρήγορο, διαισθητικό και εύκολο στη χρήση. Το Beekeeper υποστηρίζει πολλές βάσεις δεδομένων και λειτουργεί εξαιρετικά σε Windows, Mac και Linux.
Τι Λένε Οι Χρήστες Για Το Beekeeper Studio
"Το Beekeeper Studio αντικατέστησε εντελώς την παλιά μου ροή εργασίας SQL. Είναι γρήγορο, διαισθητικό και κάνει τη δουλειά με βάσεις δεδομένων απολαυστική ξανά."
"Έχω δοκιμάσει πολλά GUIs βάσεων δεδομένων, αλλά το Beekeeper βρίσκει την τέλεια ισορροπία μεταξύ χαρακτηριστικών και απλότητας. Απλά δουλεύει."