Often in PostgreSQL you may want to convert a time interval to minutes.
You can use the EXTRACT and EPOCH statements to do so:
SELECT *,
EXTRACT(EPOCH FROM task_time) / 60 AS total_minutes
FROM emp;
This particular example creates a new column named total_minutes that converts the time interval from the task_time column in the emp table to a total number of minutes.
The following example shows how to use this syntax in practice.
Example: How to Convert Time to Minutes in PostgreSQL
Suppose that we create the following table named emp that contains information about how long it took various employees at a company to complete some task:
-- create table named 'emp'
CREATE TABLE emp (
empID INT,
task_time INTERVAL
);
-- add rows to the table
INSERT INTO emp VALUES (0001, '00:15:00');
INSERT INTO emp VALUES (0002, '04:05:08');
INSERT INTO emp VALUES (0003, '07:00:33');
INSERT INTO emp VALUES (0004, '09:15:46');
INSERT INTO emp VALUES (0005, '12:56:17');
-- view all rows in table
SELECT * FROM emp;
empid | task_time
-------+-----------
1 | 00:15:00
2 | 04:05:08
3 | 07:00:33
4 | 09:15:46
5 | 12:56:17
The time in the task_time column is currently shown as hours, minutes and seconds.
We can use the following syntax to create a new column that converts the times from the task_time column to minutes:
SELECT *,
EXTRACT(EPOCH FROM task_time) / 60 AS total_minutes
FROM emp;
empid | task_time | total_minutes
-------+-----------+----------------------
1 | 00:15:00 | 15.0000000000000000
2 | 04:05:08 | 245.1333333333333333
3 | 07:00:33 | 420.5500000000000000
4 | 09:15:46 | 555.7666666666666667
5 | 12:56:17 | 776.2833333333333333
The new column named total_minutes contains the time from the task_time column as a total number of minutes.
For example:
- 15 minutes is equivalent to 15 minutes.
- 4 hours, 5 minutes and 8 seconds is equivalent to 245.133 minutes.
- 7 hours and 33 seconds is equivalent to 420.55 minutes.
And so on.
You can use similar syntax to convert time to minutes in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: Select Rows where Timestamp is Empty
PostgreSQL: How to Select Records Between Two Timestamps
PostgreSQL: How to Remove Milliseconds from Timestamp
