Notes on codes, projects and everything

Magic SQL

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?

leave your comment

name is required

email is required

have a blog?

This blog uses scripts to assist and automate comment moderation, and the author of this blog post does not hold responsibility in the content of posted comments. Please note that activities such as flaming, ungrounded accusations as well as spamming will not be entertained.

Click to change color scheme