Why is June 21 the official start of summer? Let’s see how SQL can help us answer this question.

The Summer Solstice

Officially, June 21 is recognized as the summer solstice, the longest day of the entire year in terms of daylight. Why? Because on this day, the sun rises early and sets quite late.

People in the Northern Hemisphere celebrate the summer solstice with feasts, bonfires, picnics, and traditional dances and songs. In ancient times, the summer solstice was even used to organize calendars and served as an indicator of when farmers needed to plant and harvest crops. Traditionally, this time of year was also popular for weddings.

In this article, we’ll use a table called sunlight_hours with a year’s worth of data for three cities to explore the summer solstice with SQL. Below is a sample of the entire dataset.

Based on this table, we’ll write a query to obtain the longest day in terms of daylight for all cities and months. We’ll use the PARTITION BY clause to group days by month, ordered by daylight_time. Then, we’ll select the longest daylight_time day for each month by using the first_value() function. This query will return 12 records, one for each month.

 
WITH months AS (
   SELECT EXTRACT(MONTH FROM "day") as "Month",
          "day",
          sunset - sunrise AS "daylight_time"
   FROM sunlight_hours
   WHERE city = 'London'
)
SELECT distinct "Month",
          first_value("daylight_time") OVER
            (PARTITION BY "Month" order by "daylight_time" desc) as "Duration",
          first_value("day") OVER
            (PARTITION BY "Month" order by "daylight_time" desc) as "Day"
FROM months
ORDER BY "Month"

Here’s the result set for that query:

Briefly, let’s take a look to the other side of the coin: the winter solstice. This is the shortest day of the year, and it’s December 21 in the Northern Hemisphere. Personally, I feel quite happy on that day because I know that every day that follows it will be just a little bit longer.

Okay, so let’s generalize these ideas for practice with SQL. Let’s say that if tomorrow will be a longer day in terms of sunlight, then we have a “Happy” outlook today. On the other hand, if tomorrow will be a shorter day, we have a “Neutral” outlook.

First, we’ll use a CTE to create an intermediate table with the city, day, and daylight_timeduration columns. We’ll call this intermediate table sun_light_hours. Next, in the main query, we’ll refer to the intermediate CTE table and use the first_value() function we mentioned earlier. Let’s assemble these parts into a query:

 
WITH day_duration AS (
   SELECT city, 
          day,
          sunset - sunrise AS "daylight_time"
   FROM   sunlight_hours
)
SELECT 
   d1.city,
   d1.day,
   CASE WHEN d1.daylight_time < d2.daylight_time
THEN 'HAPPY' 
ELSE 'NEUTRAL'
END   AS feeling_based_on_tomorrow_sunlight
FROM  day_duration d1, day_duration d2
WHERE    d1.day = d2.day - 1 AND d1.city = d2.city
AND      d1.city = 'London'
AND      ((d1.day BETWEEN '2018-12-19' AND '2018-12-22') 
 OR 
 (d1.day BETWEEN '2018-06-19' AND '2018-06-22' ))
ORDER BY d1.day

For better visualization, we’ve filtered the results to those days closest to the winter and summer solstices. Notice that on December 21, we change from “Neutral” to “Happy.” This is because our next-day expectation changed from shorter to longer:

Summer Has Officially Started!

In this article, we mainly worked with two SQL constructions: window functions and CTEs. If you’re interested in learning more about these kinds of SQL constructions, I recommend you explore Vertabelo Academy–especially now, during the summer. Thanks to our SQL summer school, you’ll learn SQL in just three exciting months—from the very basics to more advanced concepts like those used in this article. Remember you can reach your personal as well as career goals thanks to your knowledge. Be confident on your knowledge and conquer the world!