将Postgresql时间戳四舍五入到最接近的30秒

Question

在PostgreSQL中，是否可以将时间戳舍入到最近的30秒？例如：

source -> result

2017-06-07 14:11:20 -> 2017-06-07 14:11:00
2017-06-07 14:11:40 -> 2017-06-07 14:11:30
2017-06-07 14:12:10 -> 2017-06-07 14:12:00

- areklipno

---

2017-06-07 14:11:50 -> 2017-06-07 14:12:00 的意思是什么？ - Vao Tsun

Answer 1

select to_timestamp( round( ( extract ('epoch' from ts) ) /30 ) * 30 )

- verhie

---

应该使用 to_timestamp() 而不是 timestamp()：to_timestamp(round( extract('epoch' from ts) / 30 ) * 30) 这样就可以完美运行。(而被接受的答案没有四舍五入到最近的30秒。) 我已经相应地编辑了答案。 - Thorsten Kettner

我猜你应该使用 floor 而不是 round。 - PirateApp

Answer 2

这里有一种方法：

select (date_trunc('minute', ts) +
        (case when extract(second from ts) > 30 then 30 else 0 end) * interval '1 second' 
       ) as rounded_ts

注意：这将把30秒向下取整。

- Gordon Linoff

Answer 3

将数字四舍五入到最接近的整数可能会有些丑陋：

t=# with a(ts) as (
  values ('2017-06-07 14:11:20'::timestamp),('2017-06-07 14:11:40'),('2017-06-07 14:12:10'),('2017-06-07 14:11:50')
)
, c as (
  select *,date_trunc('minute',ts) t0, date_trunc('minute',ts) + '15 seconds'::interval t1,date_trunc('minute',ts)+ '30 seconds'::interval t2,date_trunc('minute',ts)+ '45 seconds'::interval t3, date_trunc('minute',ts) + '1 minute'::interval t5
  from a
)
select case when ts between t0 and t1 then t0 when ts between t1 and t3 then t2 when ts between t3 and t5 then t5 end rnd,ts from c;
         rnd         |         ts
---------------------+---------------------
 2017-06-07 14:11:30 | 2017-06-07 14:11:20
 2017-06-07 14:11:30 | 2017-06-07 14:11:40
 2017-06-07 14:12:00 | 2017-06-07 14:12:10
 2017-06-07 14:12:00 | 2017-06-07 14:11:50
(4 rows)

Time: 0.434 ms

- Vao Tsun