I would like to generate a list of dates with timestamp at an hourly interval using SQL in teradata

Question

just want to know if there is a way that I can generate a list of dates for a certain date range (e.g.2023-01-01 to 2023-01-20) with timestamp at an hourly interval in Teradata using SQL

Results that I want to derive (each date will have 24 rows)

2023-01-01 00:00:00

2023-01-01 01:00:00

.

2023-01-01 23:00:00

2023-01-02 00:00:00

.

Many thanks!

score 2 · Answer 1 · answered May 02 '23 at 16:03

2

Use EXPAND ON against your calendar table, e.g.

select begin(pd)
from sys_calendar.calendar
where calendar_date between date '2023-01-01' and date '2023-01-20'
expand on period(cast(calendar_date    as timestamp(0))
                ,cast(calendar_date +1 as timestamp(0))) as pd 
by interval '1' hour
order by 1;

answered May 02 '23 at 16:03

dnoeth

59,503
4
39
56

One day I really need to just force myself to sit down and figure out the expand on stuff, instead of just avoiding it. – Andrew May 02 '23 at 16:50

score 0 · Answer 2 · answered May 02 '23 at 15:50

Quick and dirty way is just to build a volatile table with your hours, and cross join to your dates.

create volatile table vt_hour (hour_col varchar(50)) on commit preserve data;

insert into vt_hour values ('00:00:00');
insert into vt_hour values ('01:00:00');
...
create volatile table vt_date (date_col varchar(50)) on commit preserve data;
insert into vt_date values ('2023-05-02');
insert into vt_date values ('2023-05-03');


select
cast (date_col || ' ' || hour_col as timestamp)
from
vt_date
cross join vt_hour
order by 1

I would like to generate a list of dates with timestamp at an hourly interval using SQL in teradata

2 Answers2