CODEWITHSUNDEEP
sqlpostgresqltimedate-arithmetic
Alberto
Alberto

Reputation: 12899

Timestamp Difference In minutes for PostgreSQL

I have the following query:

Select works.id, works.client_id, COALESCE(TIMESTAMPDIFF(MINUTE,works.begin_at,works.finish_at), 0) / 60 * (clients.worker_cost_hourly * works.number_of_workers + works.machines *             clients.machine_cost_hourly) as cost
from works join clients on works.client_id = clients.id
where works.paid = 0

That works fine for mysql but postgresql is complaining about the TIMESTAMPDIFF... are there ways to achieve this behaviour in both DBMS with the same query?

Btw, works.begin_at and works.finish_at are defined as time

Upvotes: 0

Views: 1022

Answers (2)

nbk
nbk

Reputation: 49373

EXTRACT(EPOCH FROM works.begin_at) - EXTRACT(EPOCH FROM works.finish_at))/3600 gives you the hours difference

Select works.id, works.client_id
, COALESCE(T(EXTRACT(EPOCH FROM works.begin_at) - EXTRACT(EPOCH FROM works.finish_at))/3600, 0) / 60 * (clients.worker_cost_hourly * works.number_of_workers 
+ works.machines * clients.machine_cost_hourly) as cost
from works join clients on works.client_id = clients.id
where works.paid = 0

Upvotes: 1

GMB
GMB

Reputation: 222402

You can just substract the two times: this gives you an interval, which you can turn to seconds with extract(), and then to minutes using arithmetics:

extract(epoch from works.finish_at - works.begin_at) / 60

This returns an integer value. If you want the decimal part as well, then:

extract(epoch from works.finish_at - works.begin_at) / 60.0

Upvotes: 3

Related Questions