Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres Time Difference

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

like image 739
Md Rashedul Hoque Bhuiyan Avatar asked Sep 13 '15 07:09

Md Rashedul Hoque Bhuiyan


People also ask

Does PostgreSQL have datediff?

PostgreSQL provides a datediff function to users. The datediff means we can return the difference between two dates based on their specified interval. The datediff function plays an important role in the database management system because datediff functions as a calendar and it is very helpful to users.

What is interval in PostgreSQL?

In PostgreSQL the interval data type is used to store and manipulate a time period. It holds 16 bytes of space and ranging from -178, 000, 000 years to 178, 000, 000 years. It also has additional attribute called “precision (denoted by p)” that can be used to set the level of precision in the query results.

How do you find the difference between two timestamps?

If you'd like to calculate the difference between the timestamps in seconds, multiply the decimal difference in days by the number of seconds in a day, which equals 24 * 60 * 60 = 86400 , or the product of the number of hours in a day, the number of minutes in an hour, and the number of seconds in a minute.


1 Answers

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

like image 72
reupen Avatar answered Sep 20 '22 17:09

reupen