postgresql Postgres 时差
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32547188/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres Time Difference
提问by Md Rashedul Hoque Bhuiyan
I am trying to retrieve time difference in minutes from a table(login_history as t1) using postgresql .
我正在尝试使用 postgresql 从表（login_history as t1）中以分钟为单位检索时差。
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
它工作正常。但是当我尝试使用此代码从表 t1 中检索信息时
((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
谢谢
回答by reupen
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)
（给出 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_timestampfunction for custom string parsing: http://www.postgresql.org/docs/9.4/static/functions-formatting.html
或查看to_timestamp自定义字符串解析函数：http: //www.postgresql.org/docs/9.4/static/functions-formatting.html
回答by Md Rashedul Hoque Bhuiyan
I needed to remove the timestampfrom the query before t1and the query works.
我之前需要timestamp从查询中删除t1并且查询有效。
