CODEWITHSUNDEEP
sqlamazon-redshift
Adella
Adella

Reputation: 31

create table with dates - sql

I have a query that can create a table with dates like below:

with digit as (
select 0 as d union all 
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9        
),

seq as (
select a.d + (10 * b.d) + (100 * c.d) + (1000 * d.d) as num
from digit a
    cross join
    digit b
    cross join
    digit c
    cross join
    digit d
order by 1        
)

select (last_day(sysdate)::date - seq.num)::date as "Date"
from seq;

How could this be changed to generate only dates

Thanks

Upvotes: 2

Views: 290

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

This answers the original version of the question.

You would use generate_series():

select gs.dte
from generate_series(date_trunc('month', now()::date),
                     date_trunc('month', now()::date) + interval '1 month' - interval '1 day',
                     interval '1 day'
                    ) gs(dte);

Here is a db<>fiddle.

Upvotes: 0

S-Man
S-Man

Reputation: 23766

demo:db<>fiddle

WITH dates AS (
    SELECT 
        date_trunc('month', CURRENT_DATE) AS first_day_of_month,
        date_trunc('month', CURRENT_DATE) + interval '1 month -1 day' AS last_day_of_month   
)
SELECT
    generate_series(first_day_of_month, last_day_of_month, interval '1 day')::date
FROM dates
  • date_trunc() truncates a type date (or timestamp) to a certain date part. date_trunc('month', ...) removes all parts but year and month. All other parts are set to their lowest possible values. So, the day part is set to 1. That's why you get the first day of month with this.
  • adding a month returns the first of the next month, subtracting a day from this results in the last day of the current month.
  • Finally you can generate a date series with start and end date using the generate_series() function

Edit: Redshift does not support generate_series() with type date and timestamp but with integer. So, we need to create an integer series instead and adding the results to the first of the month:

db<>fiddle

WITH dates AS (
    SELECT 
        date_trunc('month', CURRENT_DATE) AS first_day_of_month,
        date_trunc('month', CURRENT_DATE) + interval '1 month -1 day' AS last_day_of_month   
)
SELECT
    first_day_of_month::date + gs
FROM 
    dates,
    generate_series(
        date_part('day', first_day_of_month)::int - 1, 
        date_part('day', last_day_of_month)::int - 1
    ) as gs

Upvotes: 1

Related Questions