Ever wanted to find the number of days between two dates without counting weekend (Saturdays and Sundays)? In PHP you typically needs to do a lot of calculation and a lot of factors needs to be considered. Therefore, in the end you will end up having a whole bunch of code that you will probably start asking yourself whether you are programming a web-calendar or something similar.
I am currently working on a projects that deals with a lot of time-related data. My mind went completely blank when I am asked to do the above calculation without PHP code but a simple SQL statement. I was like ‘What??! That’s impossible…’. However, it is very much possible and at least there seems to be a few ways to do it in almost all DBMS on market.
No, there will be no stored procedure involved. You will know how later.
At first I was trying to write a super-duper-extremely complex SQL. But after flipping through my old Database Systems (Connolly & Begg, 2005) textbook (yes, I brought my super heavy database textbook to the office and just left there as my reference, I cannot program without reference), I made the first conclusion — the question given was insane. But if my supervisor already has the solution, then there must be a way to do it….
First, I search through a famous search engine and found this, but it is for SQL server.
Then, I tried to do some calculation with timestamp data with postgresql based on the documentation.
However, there doesn’t really work because I cannot ‘port’ the SQL statement for SQL server to PostgreSQL. So I need to get a better way of doing that.
Then I need to find a way for me to generate a list in a column so I can use a count() aggregate function. But does it even exists, none that I know of. So I made another search and got this. By combining tutorials and understanding from the documentation on date calculation, I then got the first query that looks like follows:-
SELECT count(s.a + date '2008-8-26') AS days
FROM generate_series(0, (date '2008-9-1' - date '2008-8-26')) as s(a)
WHERE EXTRACT(isodow FROM s.a + date '2008-8-26') < 6;
Alright, this doesn’t look very understandable, by comparing the answer provided by my supervisor (he published right before I posted mine). I got this
SELECT COUNT(*) AS total_days
FROM (SELECT date '2008-8-26' + generate_series(0, (date '2008-9-1' - date '2008-8-26')) as all_days) AS calendar
WHERE EXTRACT(isodow FROM all_days) < 6;
This is a much cleaner and understandable answer (The diff between two queries). However, this is not an optimal answer and takes 2ms to count the number of days without weekends in a year (source).
So, isn’t this solution better than a typical PHP code?