Predictive Hacks

How to Generate Date Series in Redshift

In a previous post, we have provided an example of how to generate date series in PostgreSQL. Let’s see how we can do it in Redshift. Here, it is not so straightforward since we have to generate a series first. A good example is to consider an arbitrary table to get the row numbers and then based on the row numbers to generate the date series. Let’s say that we want to generate a monthly date series from now and looking behind 200 months.

 SELECT
      DATEADD('month', -n, DATE_TRUNC('month', add_months(CURRENT_DATE,12))) AS gdate
    FROM (SELECT ROW_NUMBER() OVER () AS n FROM one_table LIMIT 200) n

Notice: So far you cannot use the generate_series() in Redshift because it is supported only by the master node and it fails to work when you have to join it with other tables

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

Subscribe To Our Newsletter

Get updates and learn from the best

More To Explore

Python

Image Captioning with HuggingFace

Image captioning with AI is a fascinating application of artificial intelligence (AI) that involves generating textual descriptions for images automatically.

Python

Intro to Chatbots with HuggingFace

In this tutorial, we will show you how to use the Transformers library from HuggingFace to build chatbot pipelines. Let’s