CODEWITHSUNDEEP
sqldatabasepostgresql
nmkyuppie
nmkyuppie

Reputation: 1476

How to calculate date difference for all the rows in a table in PostgreSQL?

I have a table called effective, it has id, start time, end time.

I want to calculate the date difference for all the rows. I've tried like following method,

select DATE_PART('hour', age(endtime,starttime )) OVER (PARTITION BY id) as increase from effective

But it shows the following error

ERROR: OVER specified, but date_part is not a window function nor an aggregate function

I want a result like, 

id || starttime                 || endtime                       || hour
1  || '2017-09-15 14:50:39.312' || '2017-09-15 16:50:34.349'     || 2
2  || '2017-09-15 14:50:34.349' || '2017-09-15 15:55:48.894319'  || 1

Upvotes: 1

Views: 66

Answers (1)

Matt
Matt

Reputation: 15061

Use date_part and subtract the times.

SELECT id, starttime, endtime, date_part('hour',endtime-starttime) AS hour
FROM effective

Output

id  starttime            endtime              hour
1   2017-09-15T14:50:39Z 2017-09-15T16:50:34Z 1
2   2017-09-15T14:50:34Z 2017-09-15T15:55:48Z 1

SQL Fiddle: http://sqlfiddle.com/#!15/916ac9/2/0

Also ID 1 start to end is only 1 hour 59 min and 55 seconds, NOT 2 hours.

Upvotes: 2

Related Questions