Postgres Time Difference

Question

I am trying to retrieve time difference in minutes from a table(login_history as t1) using postgresql .

When i tried this code

((date_part('hour', timestamp '2014-04-25 09:44:21')- date_part('hour', timestamp '2014-04-25 08:32:21'))*60 +(date_part('minutes', timestamp '2014-04-25 09:44:21')- date_part('minutes', timestamp '2014-04-25 08:32:21'))) as TimeNew

It works fine. But when i tried to retrieve information from a table t1 using this code

((date_part('hour', timestamp t1.login_date)- date_part('hour', timestamp t1.logout_date))*60 +
(date_part('minutes', timestamp t1.login_date)- date_part('minutes', timestamp t1.logout_date))
) as TimeNew

It throws this error

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "t1"

Thanks

Your calculation is wrong. Change the second timestamp to '2014-04-24 08:32:21' and the problem will be obvious. Calculate the interval first, then take the result apart to express it in minutes. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Sep 13, 2015 at 10:38

reupen · Accepted Answer · 2015-09-13 18:12:08Z

11

I would use the interval that results from subtracting two timestamps for a much simpler expression:

select extract (epoch from (timestamp '2014-04-25 09:44:21' - timestamp '2014-04-25 08:32:21'))::integer/60

(gives 72)

or for your table:

select extract (epoch from (t1.logout_date - t1.login_date))::integer/60

If you need to cast:

select extract (epoch from (t1.logout_date::timestamp - t1.login_date::timestamp))::integer/60

or see the to_timestamp function for custom string parsing: http://www.postgresql.org/docs/9.4/static/functions-formatting.html

answered Sep 13, 2015 at 18:12

reupen

5713 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sami Kuhmonen · Accepted Answer · 2015-09-13 09:45:37Z

0

I needed to remove the timestamp from the query before t1 and the query works.

edited Sep 13, 2015 at 9:45

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

answered Sep 13, 2015 at 7:10

Md Rashedul Hoque Bhuiyan

10.7k5 gold badges33 silver badges42 bronze badges

Collectives™ on Stack Overflow

Postgres Time Difference

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related