PostgreSQL Time Dimension (By Hours and Days) Error

Question

I am am building a Time Dimension table in PostgreSQL with DATE_ID and DATE_DESC. My T-SQL (works perfectly) script is:

set DATEFIRST 1
 ;WITH DATES AS (
    SELECT CAST('2019-01-01 00:00:00.000' AS datetime) AS [DATE]
    UNION ALL
    SELECT DATEADD(HH,1,[DATE])
    FROM DATES
    WHERE DATEADD(HH,1,[DATE]) <= CAST('2019-12-31' AS datetime)
)
SELECT
DATE_ID, DATE_DESC
from
(
SELECT  
CONVERT(int, CONVERT(char(8), DATE, 112)) AS DATE_ID,
DATE AS DATE_DESC
FROM
    DATES)a
order by 1
OPTION (MAXRECURSION 0)

At the moment Im trying to convert this code to PostgreSQL readable one and it does not work.. Here is mine at the moment:

set EXTRACT(DOW FROM TIMESTAMP  '2019-01-01 00:00:00.000')+1
;WITH DATES AS (
    SELECT CAST('2019-01-01 00:00:00.000' AS timestamp) AS DATE
    UNION ALL
    SELECT CURRENT_DATE + INTERVAL '1 hour'
    FROM DATES
    WHERE CURRENT_DATE + INTERVAL '1 hour' <= CAST('2019-12-31' AS timestamp)
)

SELECT DATE_ID, DATE_DESC from
(SELECT  cast(to_char((DATE)::TIMESTAMP,'yyyymmddhhmiss') as BIGInt) AS DATE_ID,
DATE AS DATE_DESC
FROM
    DATES)a
order by 1
OPTION (MAXRECURSION 0)

I need all the hours (24h) between 2019-01-01 and 2019-12-31 . At the moment I think OPTION (MAXRECURSION 0) and set EXTRACT(DOW FROM TIMESTAMP '2019-01-01 00:00:00.000')+1 is not working properly.

score 0 · Accepted Answer · answered Mar 10 '21 at 09:17

0

Its a problem of Recursive CTE, In Postgresql, your desired query will be like below

WITH recursive DATES AS (
    SELECT CAST('2019-01-01 00:00:00.000' AS timestamp) AS date_
    UNION ALL
    SELECT date_ + INTERVAL '1 hour'
    FROM DATES
    WHERE date_ + INTERVAL '1 hour' <= CAST('2019-12-31' AS timestamp)
)

SELECT DATE_ID, DATE_DESC from
(SELECT  cast(to_char((date_)::TIMESTAMP,'yyyymmddhhmiss') as BIGInt) AS DATE_ID,
date_ AS DATE_DESC
FROM
    DATES)a
order by 1

DEMO

answered Mar 10 '21 at 09:17

Akhilesh Mishra

5,876
3
16
32

Hello! Thank you for your answer. It seems that DATE_DESC duplicates for every hour. For example 5pm has two rows **2019-01-01 05:00:00** and **2019-01-01 17:00:00** – Konstantins Kovalovs Mar 10 '21 at 09:38
To correct it, we need to delete the **hhmiss** part from your code part: `(SELECT cast(to_char((date_)::TIMESTAMP,'yyyymmddhhmiss') as BIGInt) AS DATE_ID ` ! Like this: `(SELECT cast(to_char((date_)::TIMESTAMP,'yyyymmdd') as BIGInt) AS DATE_ID ` – Konstantins Kovalovs Mar 10 '21 at 16:05

PostgreSQL Time Dimension (By Hours and Days) Error

1 Answers1