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
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 
       date_part('hour',date)+1       AS hr, 
       (date_part('minute',date)/5)+1 AS bucket 
FROM   dimdatecreator;


  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

  2. It is available in} both equipment and preassembled varieties, however need to|if you wish to} learn extra about 3D printing ought to buy|you should purchase|you can purchase} the equipment. It's a wonderful introduction to how the whole process works, and Bottle Warmers it will save you money. It's simple to arrange and use and it's normally priced at underneath $200.


Post a Comment

Popular posts from this blog

PTO Tracker

Coursera Final Project

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