mike winston mike winston - 1 month ago 7
SQL Question

How to retrieve all unique (Year, Quarter) combinations from a list of dates?

This works as a charm to retrieve all unique (Year, Month) combinations from my table:

SELECT
STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(Date), '%Y-%m') as month,
FROM
[Table]


It returns 2016-05, 2016-06, 2016-07, etc

Want to do the same thing for (Year, Quarter) but have found nothing. Any tips? I know quarter is very tricky on sql. Thanks!

Answer

Combine the QUARTER function with the YEAR function:

select concat(string(year(timestamp)),'-Q',string(quarter(timestamp))) as mquerter
from [table]
group by 1
Comments