CODEWITHSUNDEEP
sqlteradata
lea
lea

Reputation: 143

Create a Timestamp table till current date in Teradata

I want to create a view that has all timestamps till the current date with incremental hour and save it as a view in teradata. How do I do it. I saw some ways using recursion but doesnt help.

date_x
1/1/2021 0:00
1/1/2021 1:00
1/1/2021 2:00
1/1/2021 3:00
1/1/2021 4:00 
CREATE VIEW db1.table1 WITH RECURSIVE DateRanges(CALENDAR_DATE) AS
(
SELECT DATE '2020-01-01' AS CALENDAR_DATE
FROM
( SELECT * FROM 
(SELECT NULL AS X)X) DUMMYTABLE
UNION ALL
SELECT
CALENDAR_DATE + INTERVAL '1' DAY
FROM
DateRanges
WHERE
CALENDAR_DATE < CURRENT_DATE
)
SELECT *
FROM DateRanges;

Upvotes: 0

Views: 965

Answers (1)

dnoeth
dnoeth

Reputation: 60462

Recursion is not very efficient for a large number of levels. But there's Teradata's hardly known NORMALIZE syntax:

REPLACE VIEW DateRanges AS
SELECT BEGIN(pd) AS TS
EXPAND ON PERIOD(CAST(DATE '2021-04-01' AS TIMESTAMP(0))
                ,CAST(TRUNC(CURRENT_TIMESTAMP(0)) AS TIMESTAMP(0))) AS pd
          BY INTERVAL '1' HOUR

Normally EXPAND ON only works when a table is accessed in FROM, but applying the TRUNC function seems to forget about this rule :-)

If the parser is still complaining about the missing from you can use any select returning a single row. I prefer sys_calendar because there's a public access right:

SELECT BEGIN(pd) AS TS
FROM sys_calendar.calendar
WHERE calendar_date = current_date
EXPAND ON PERIOD(CAST(DATE '2021-04-01' AS TIMESTAMP(0))
                ,CAST(CURRENT_DATE AS TIMESTAMP(0))) AS pd
          BY INTERVAL '1' HOUR

Upvotes: 3

Related Questions