Search code examples
sqldatetimeamazon-redshiftdate-arithmetic

How to get generate_series() function in Redshift to result in just dates NOT date-time

Is there a way to remove the timestamp on the result set?

This is the query I am running in Redshift:

SELECT CURRENT_DATE - (i * interval '1 day') as dates 
FROM generate_series(0,7) i 
ORDER BY 1;

This is the result:

1
2020-10-21T00:00:00.000Z
2
2020-10-22T00:00:00.000Z
3
2020-10-23T00:00:00.000Z
4
2020-10-24T00:00:00.000Z
5
2020-10-25T00:00:00.000Z
6
2020-10-26T00:00:00.000Z
7
2020-10-27T00:00:00.000Z
8
2020-10-28T00:00:00.000Z
Solution

  • Just cast:

    SELECT cast(current_date - i * interval '1 day' as date) as dates 
FROM generate_series(0, 7) i 
ORDER BY 1;

    Or:

    SELECT (current_date - i * interval '1 day')::date as dates 
FROM generate_series(0, 7) i 
ORDER BY 1;