Find the season for a date

Pieterjan De Clippel
2 min readSep 10, 2022

I know what you’re thinking right now…

Well, isn’t that easy stuff?

Spoiler: Trust me, it’s not

Situation

Your seasons are stored in the database like this:

Seasons stored in the database

Now you want to know for a specific date to which season it belongs. Take for example 2022/04/15. First thing you can try, is to set the year to 2000 (this is a good example since it’s a leap year). Any sane person knows rightaway that this is in Spring.

2000/03/21 <= 2000/04/15 <= 2000/06/20

So yeah, easy, April 15th is in Spring.

…………………………………………………………………..

Lets pick another example, 2022/12/25.

2000/12/21 <= 2000/12/25 <= 2001/03/20

Yes, easy, Christmas is in the Winter.

…………………………………………………………………..

Now for a change, lets pick 2022/01/06

2000/12/21 <= 2000/01/06 <= 2001/03/20

The above equation (which isn’t mathematically correct, I know…) cannot possibly be true, unless we would change the year to 2001 instead of 2000. But how can we know what number to pick? (remember that the seasons are coming from the database, are completely user-specific, and can be 11-months long if the user chooses to). If you’re into gambling, you can try and guess if you want to pick 2000 or 2001. But we need exact calculations…

An easy, primitive, workaround

This post on StackOverflow can give you a little hint on how you could solve this problem.

However, our seasons are coming from a database. But we can still apply what we see here:

  • Change all year numbers to 2000
  • Do the simple check:
season_start(2000) <= date(2000) <= season_end(2000)
  • If this doesn’t return us anything, take the season of which the year_start !== year_end

A more direct way

All the problems described above can be easily circumvented by just splitting up the season that intersects newyear (or where year_start !== year_end)

Seasons stored in the database <> Splitted seasons

Now we can have all year numbers set to 2000, and comparing the dates is as easy as it looks. 100% reliable.

Coding

Coding the above should be pretty straightforward, depending on what language/ORM you’re using.

SQL

Find season for date using Transact-SQL

Most likely, parts of this SQL-query can be written in a better way. However, the database system (that, which cannot be named) and version we’ve been using at my job at that time, was old and didn’t support a wide range of SQL commands.

C#

In .NET, using LINQ-to-SQL visitors, you could write the following LINQ-query. This query can be fully translated to SQL without problems.

So as explained above, we just check if the season crosses the newyear. If so, we split the season in a part before and after newyear. Change all year numbers to 2000, and simply compare the dates.

I’ve written a NuGet package for .NET, which implements this code snippet.

--

--