时间戳

首页 > 解决方案 > 在 Postgres 中，如何舍入提取时代的回报

postgresql - 在 Postgres 中，如何舍入提取时代的回报

问题描述

Linux 上的 psql（9.6.7，服务器 11.3）

鉴于这个简单的表...

dvdb=> select * from delme;
        start_dt        |         end_dt         
------------------------+------------------------
 2020-03-01 00:00:00-05 | 2020-03-03 12:34:56-05
(1 row)

我想要时间增量的小时数，四舍五入到最接近的十分之一小时。我能走到这一步...

dvdb=> select extract(epoch from age(end_dt,start_dt)/3600) from delme;
 date_part 
-----------
 60.582222
(1 row)

但似乎不能圆....

dvdb=> select round(extract(epoch from age(end_dt,start_dt)/3600),1) from delme;
ERROR:  function round(double precision, integer) does not exist
LINE 1: select round(extract(epoch from age(end_dt,start_dt)/3600),1...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
dvdb=> 


dvdb=> select round(cast(extract(epoch from age(end_dt,start_dt)/3600) as float),1) from delme;
ERROR:  function round(double precision, integer) does not exist
LINE 1: select round(cast(extract(epoch from age(end_dt,start_dt)/36...
           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
dvdb=>

我想我没有正确理解 round ，它想要的第一个 arg 类型......

dvdb=> select round(123.456,1);
 round 
-------
 123.5
(1 row)

dvdb=> select round(cast(123.456 as float),1);
ERROR:  function round(double precision, integer) does not exist
LINE 1: select round(cast(123.456 as float),1);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
dvdb=>

标签： postgresql

解决方案

我想我明白了...

dvdb=> select round(cast(extract(epoch from age(end_dt,start_dt)/3600) as numeric),1) from delme;
 round 
-------
  60.6
(1 row)

推荐阅读