Postgresql round timestamp to nearest 30 seconds

Question

is possible in postgres to round timestamp to nearest 30 seconds? For example:

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

and `2017-06-07 14:11:50 -> 2017-06-07 14:12:00`?.. – Vao Tsun Jun 07 '17 at 12:19 — Vao Tsun, Jun 07 '17 at 12:19

score 7 · Answer 1 · edited Jun 07 '17 at 12:42

7

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

edited Jun 07 '17 at 12:42

Thorsten Kettner

89,309
7
49
73

answered Jun 07 '17 at 12:25

verhie

1,298
1
7
7

That shoud be `to_timestamp()` instead of `timestamp()`: `to_timestamp(round( extract('epoch' from ts) / 30 ) * 30)` then it works perfectly. (While the accepted answer doesn't round to the nearest 30 secs.) I've edited the answer accordingly. – Thorsten Kettner Jun 07 '17 at 12:42
2

i guess you should be using floor instead of round – PirateApp Jun 05 '18 at 06:52

score 4 · Accepted Answer · answered Jun 07 '17 at 12:21

4

Here is one method:

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

Note: This rounds 30 second downward.

answered Jun 07 '17 at 12:21

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Jun 07 '17 at 12:36

quite ugly to round upwords and downwords:

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

Postgresql round timestamp to nearest 30 seconds

3 Answers3

Linked

Related