Day of Quarter in Redshift

Admin 1
Viewed 1752

I'm trying to create a calculation based on days in a quarter. Problem is, I just can't find anything on how to do it that's similar to how you would do day of year.

SELECT
event_date,
day_of_quarter
FROM table_a
WHERE event_date BETWEEN '2018-10-01' AND '2019-03-31'

So in the above example for day_of_quarter, 2018-10-01 would retun 1, 2018-12-31 would return 92 and say, 2019-03-05 would return 5

amazon-redshift
1 Answers

Here is the SQL for this

SELECT
event_date,
(event_date - date_trunc('quarter', event_date)::date)+1 day_of_quarter
FROM table_a
WHERE event_date BETWEEN '2018-10-01' AND '2019-03-31'

just event_date - date_trunc('quarter', event_date) gives a day before, thus +1

user_16288120
Related