Postgresql I want to exclude weekends from year, month, and day data based on today (1 year's w-CodePudding

SELECT to_char(date_trunc('day', (current_date - days)), 'YYYY-MM-DD')
AS date 
FROM generate_series(0, 365, 1) 
AS days

result

2021-11-12
2021-11-11
2021-11-10
2021-11-09
2021-11-08
2021-11-07 .....

2020-11-16
2020-11-15
2020-11-14
2020-11-13
2020-11-12 ..... end

How do I exclude weekend data from the year's worth of data?

CodePudding user response：

SELECT to_char(running_day, 'YYYY-MM-DD') AS date 
FROM (select current_date - generate_series(1, 365, 1)) AS t(running_day)
WHERE extract('isodow' from running_day) between 1 and 5
ORDER BY running_day;

CodePudding user response：

Just add a WHERE clause:

SELECT to_char(day, 'YYYY-MM-DD') AS date 
FROM generate_series(current_date - interval '1 year',
                     current_date, 
                     interval '1 day') AS g(day)
where extract(isodow from g.day) not in (6,7)

I used the ISO definition of the day numbering with Monday = 1, Saturday = 6 and Sunday = 7.

You can use extract(dow ...) if you prefer Sunday = 0