CODEWITHSUNDEEP
sqlpostgresql
Kris
Kris

Reputation: 13

How to subtract hours and minutes from each other in PostgreSQL

I have two fields dateTS and closingTime.

I need a PostgreSQL query to subtract the two field and get the number of minutes difference between them.

Using the examples given above the difference between the two fields would be 28 minutes

So far I've tried different variations of the datediff function, but it won't work.

My guess is I either have to

or

Upvotes: 0

Views: 3307

Answers (2)

Sean Johnston
Sean Johnston

Reputation: 174

Cast your closing time to an interval and the timestamp to time and then subtract the two. By casting the timestamp to time you are effectively discarding the date part. You can the subtract one from the other to generate the difference as an interval.

select closingTime::interval - dateTS::time...

e.g.:

# select '23:00'::interval - now()::time;
    ?column?
-----------------
 05:31:00.031141
(1 row)

If needed you can then convert the interval to minutes:

# select extract(epoch from ('23:00'::interval - now()::time)) / 60;
     ?column?
------------------
 327.435313083333
(1 row)

Upvotes: 0

user330315
user330315

Reputation:

You can just subtract them by converting the timestamp to a time: 

select closingtime - datets::time
from your_table;

That will give you an interval as the result.

To convert that to minutes you can get the number of seconds and divide it by 60:

select (extract epoch from closingtime - datets::time) / 60
from your_table;

Upvotes: 1

Related Questions