CODEWITHSUNDEEP
sqldatetimeamazon-redshiftdate-arithmetic
JoRoFo
JoRoFo

Reputation: 71

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

Upvotes: 0

Views: 91

Answers (1)

GMB
GMB

Reputation: 222402

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;

Upvotes: 1

Related Questions