Is it possible to select one column based on another column without using subquery in SQL?

Question

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 |

Answer 1

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

Answer 2

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 │
└────────────────────────┴────────────────────────┘