1

I'm working in a TimescaleDB in PostgreSQL. I have the following table:

| id |      timestamp      |
----------------------------
| 1  | 2021-07-12 01:04:58 |
| 2  | 2021-07-12 02:12:03 |
| 3  | 2021-07-12 04:44:11 |
| 4  | 2021-07-12 05:08:31 |

Can I select the time in one hour buckets with timestamp as timestamp_start and timestamp plus one hour as timestamp_end, in one query (meaning no sub query)?

So this is the result I'm looking for:

|   timestamp_start   |    timestamp_end    |
---------------------------------------------
| 2021-07-12 01:00:00 | 2021-07-12 02:00:00 |
| 2021-07-12 02:00:00 | 2021-07-12 03:00:00 |
| 2021-07-12 03:00:00 | 2021-07-12 04:00:00 |
| 2021-07-12 04:00:00 | 2021-07-12 05:00:00 |
| 2021-07-12 05:00:00 | 2021-07-12 06:00:00 |
TheStranger
  • 1,387
  • 1
  • 13
  • 35

2 Answers2

2

Use date_trunc, e.g.

SELECT 
  date_trunc('hour',tm) AS timstamp_start, 
  date_trunc('hour',tm) + interval '1 hour' AS timstamp_end
FROM t;

   timstamp_start    |    timstamp_end     
---------------------+---------------------
 2021-07-12 01:00:00 | 2021-07-12 02:00:00
 2021-07-12 02:00:00 | 2021-07-12 03:00:00
 2021-07-12 04:00:00 | 2021-07-12 05:00:00
 2021-07-12 05:00:00 | 2021-07-12 06:00:00
(4 rows)

Demo: db<>fiddle

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • Is it not possible with the time_bucket function? – TheStranger Jul 12 '21 at 13:44
  • HI@Ben, the documentation claims that `time_bucket` is a way more powerful and easier than `date_trunc` (although I can hardly see how it can get easier than that), so I cannot imagine it is not possible. In my example I just wanted to illustrate how easy it is with pure standard postgres. – Jim Jones Jul 12 '21 at 13:54
  • 1
    @JimJones right, and that claim is just wrong. time-bucket is not more powerful - it doesn't even work for months, etc.: see: https://github.com/timescale/timescaledb/issues/414#issuecomment-455556569 – TmTron Jul 13 '21 at 10:43
0

It works, you can declare the same as date_trunc:

tsdb=> select time_bucket('1 hour', time) as start, time_bucket('1 hour', time) + interval '1 hour' as end_date from conditions limit 1;
┌────────────────────────┬────────────────────────┐
│         start          │        end_date        │
├────────────────────────┼────────────────────────┤
│ 2010-01-06 22:00:00+00 │ 2010-01-06 23:00:00+00 │
└────────────────────────┴────────────────────────┘
jonatasdp
  • 1,072
  • 6
  • 8