Postgres 间隔返回十进制数

发布于 2025-01-14 08:42:10 字数 181 浏览 0 评论 0原文

我正在尝试计算 2 个时间戳字段之间的间隔,并使用数字而不是小数来获取结果。

我已经在“秒”字段上遇到了这个问题,并用“date_trunc”解决了它,但现在我在“小时”字段上再次遇到了这个问题。

例如:date_trunc('秒', (time1 - time2)) = 3.20:54:32

I'm trying to calculate interval between 2 timestamp fields and to get the result with numeric numbers instead decimal.

I already had this issue on 'seconds' field and I resolved it with 'date_trunc', but now I have it again on 'hour' field.

ex: date_trunc('seconds', (time1 - time2)) = 3.20:54:32

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

ㄟ。诗瑗 2025-01-21 08:42:10

减去 2 个时间戳会产生一个间隔(正如您已经看到的)。然后,您可以从结果间隔中提取各个组成部分,并应用必要的乘法来转换为所需的周期: 因此:

with times (t2, t1 ) as 
     (values ('20220315 23:53:34'::timestamp, '20220312 02:59:02'::timestamp)  )
     
select elapsed
     , round( ( extract('days'   from elapsed) * 24  
              + extract('hour'   from elapsed)  
              + extract('minute' from elapsed) / 60.0 
              + extract('second' from elapsed) / 3600.0 
              ) 
           , 2) total_hours
  from ( select t2 - t1  elapsed from times) duration; 

结果:

+-----------------+-------------+
|     elapsed     | total_hours |
+-----------------+-------------+
| 3 days 20:54:32 | 92.91       |
+-----------------+-------------+

The subtraction of 2 timestamps produces an interval (as you have already seen). Then you can extract the individual components from the resulting interval and apply the necessary multiplication to convert to the desired period: So:

with times (t2, t1 ) as 
     (values ('20220315 23:53:34'::timestamp, '20220312 02:59:02'::timestamp)  )
     
select elapsed
     , round( ( extract('days'   from elapsed) * 24  
              + extract('hour'   from elapsed)  
              + extract('minute' from elapsed) / 60.0 
              + extract('second' from elapsed) / 3600.0 
              ) 
           , 2) total_hours
  from ( select t2 - t1  elapsed from times) duration; 

Result:

+-----------------+-------------+
|     elapsed     | total_hours |
+-----------------+-------------+
| 3 days 20:54:32 | 92.91       |
+-----------------+-------------+
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文