PostgreSQL Date table

Using recursive in SQL, I can create a sequence that make up a date table.



Basic recursive

With recursive a as 
(Select 1 as Num
union
Select Num + 1
From a
Where Num + 1 < 11)

Select * from a



Postgres SQL bucket date to 5 mins interval

WITH recursive dimdatecreator(dateAS 
       SELECT (to_timestamp('2020-01-01','YYYY-MM-DD HH24:MI:SS')) AS date -- Start Date 
       UNION ALL 
       SELECT date + interval '00:05' 
       FROM   dimdatecreator 
       WHERE  date <= (to_timestamp('2020-01-31','YYYY-MM-DD HH24:MI:SS')) -- End Date 
SELECT date
       date_part('hour',date)+1       AS hr, 
       (date_part('minute',date)/5)+1 AS bucket 
FROM   dimdatecreator;

Comments

  1. That Amazing blogs! We are happy to read a unique center which you are posted from best training if any one who want to learn software testing contact us on 9311002620 or website https://www.htsindia.com/software-testing-training-courses

    ReplyDelete

Post a Comment

Popular posts from this blog

R and Shiny App, a path from data engineering to web app deployment in one language

Data Join and Blend in Tableau