CODEWITHSUNDEEP
sqlpostgresqltime
Adam Matan
Adam Matan

Reputation: 136141

SQL: Create a table with 24 hourly rows

In PostreSQL, it is fairly easy to create a timestamp with date and the current hour:

vioozer=> SELECT to_char(NOW(), 'YYYY-MM-DD HH24:00');
     to_char
------------------
 2014-08-12 12:00
(1 row)

The previous hour can be shown using NOW()-interval '1 hour':

vioozer=> SELECT to_char(NOW()-interval '1 hour', 'YYYY-MM-DD HH24:00');
     to_char
------------------
 2014-08-12 11:00
(1 row)

Is there a way to easily generate a table with 24 columns for the last 24 hours, a-la:

vioozer=> SELECT MAGIC_FROM_STACK_OVERFLOW();
     to_char
------------------
 2014-08-12 12:00
 2014-08-12 11:00
 2014-08-12 10:00
 ...
 2014-08-11 13:00
(24 row)

Upvotes: 2

Views: 1296

Answers (1)

user330315
user330315

Reputation:

Use generate_series()

select i
from generate_series(current_timestamp - interval '24' hour, current_timestamp, interval '1' hour) i

Upvotes: 8

Related Questions