I found myself the problem of counting the occurrence of specific “days of the week” between two dates; for, of course, generating features for a predictive analysis task. For example, the number of Fridays and Saturdays between 2019-01-01 and 2019-01-15. And thankfully, good old PostgreSQL came to the rescue!

### Introducing *generate_series*

SELECT*FROMgenerate_series(1,5);

generate_series`1`

`2`

`3`

`4`

`5`

(5 rows)

And, if we want to spice it up by setting the interval, we can do that too! Let’s make a list of all the ODD numbers between 1 and 10 (inclusive).

SELECT*FROMgenerate_series(1,10,2);

generate_series`1`

`3`

`5`

`7`

`9`

(5 rows)

*generate_series* but with Dates

Let’s try just plugging in Date Strings.

SELECT*FROMgenerate_series('2019-01-01','2019-01-15');

[42725] ERROR: function generate_series(unknown, unknown) is not unique Hint: Could not choose a best candidate function. You might need to add explicit type casts.

Clearly, something is off. Ah, we didn’t cast those values as Dates. Of course. Let’s do it.

SELECT*FROMgenerate_series('2019-01-01'::date,'2019-01-15'::date);

[42883] ERROR: function generate_series(date, date) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 15

Okay that did identify our value as a date, but it still isn’t working. The thing is, we need to provide the interval explicitly. Let’s do it!

SELECT*FROMgenerate_series('2019-01-01'::date,'2019-01-15'::date,'1 day'::interval);

generate_series

2019-01-01 00:00:00+06

...

2019-01-15 00:00:00+06

(15 rows)

Alright! That worked like a charm! Of course, let’s make it a bit prettier.

SELECT s::dateFROMgenerate_series('2019-01-01','2019-01-15','1 day'::interval)s;

s

2019-01-01

...

2019-01-15

(15 rows)

Sweet! We can now generate the ranges required.

### The Week of Day

I’ll let the query and the results talk in this bit.

SELECT::

sdate,

extract(DOW from s),

to_char(s,'day')FROMgenerate_series('2019-01-01','2019-01-15','1 day'::interval)s;

`s | date_part | to_char`

------------+-----------+-----------

2019-01-01 | 2 | tuesday

2019-01-02 | 3 | wednesday

2019-01-03 | 4 | thursday

2019-01-04 | 5 | friday

2019-01-05 | 6 | saturday

2019-01-06 | 0 | sunday

...

2019-01-14 | 1 | monday

2019-01-15 | 2 | tuesday

(15 rows)

Starting to make sense now? We can get a numeric value for the day of the week by extracting `DOW`

from the interval. And getting the string representation is just as easy as calling `to_char`

. Let’s put a weekend constraint now. Check the mapping of days of the week on the table posted above. That’s how we got `5`

and `6`

; representing `Friday`

and `Saturday`

.

SELECT s::date,

extract(DOW from s),

to_char(s,'day')FROMgenerate_series('2019-01-01','2019-01-15','1 day'::interval)s

WHEREextract(DOW from s)in(5, 6);

`s | date_part | to_char`

------------+-----------+-----------

2019-01-04 | 5 | friday

2019-01-05 | 6 | saturday

2019-01-11 | 5 | friday

2019-01-12 | 6 | saturday

(4 rows)

That’s super cool! Now, let’s `COUNT`

!

SELECTCOUNT(*)FROMgenerate_series('2019-01-01','2019-01-15','1 day'::interval)s

WHEREextract(DOW from s)in(5, 6);

Voila!

### With a Table

SELECT id,

SUM(CASE WHENextract(dow from s)IN(5, 6)THEN1ELSE0END)FROMtblJOINgenerate_series(

tbl.starts_on,

tbl.ends_on,'1 day'::interval)s ON true;

GROUP BY id

So this was my clumsy implementation, that I came up with. I am pretty sure there are better ways to pull this off. Why don’t you leave your thoughts and solutions in the comments? And please don’t forget to share if you learned anything new! Thanks for dropping by.

i search exactly that thing no one saw but your master