Friday the 13th 2009: Cluster of Terror

This blog entry is participating in the very first T-SQL Tuesday, hosted this month by Adam Machanic. You are invited to visit his blog to join this rollicking blog party and read more blogs participating in this month’s theme: Date/Time Tricks.

Approximately 715,298 articles have been written over the past 20 years or so regarding the handling of dates and times in T-SQL. So, instead of fruitlessly trying to come up with an earth-shattering new clever way of handling dates that no one’s ever seen before, I just thought I’d have some fun with some analysis of a very specific date that we all know and fear love.

By the way, before we proceed, as long as we’re talking about date-related stuff, I should remind you that the deadline for TSQL Challenge #18 (Generate Text-Formatted Month Calendars) is on December 14th, so check it out and give it a try.

Okay, on with our fun stuff…

---

Is it just my imagination, or have we experienced a bunch of Friday the 13th’s lately?

For those of you who are not up-to-speed on superstitions, the number 13 is an unlucky number in many cultures, and, similarly, Friday is an unlucky day of the week, and so Friday the 13th is double bad luck. People who have a genuine fear of Friday the 13th suffer from paraskevidekatriaphobia, which is a concoction of Greek words for Friday (Paraskeví), thirteen (dekatreís), and fear (phobía).

Let’s do a little investigating into Friday the 13th. First, we’ll build a Calendar table of every single date in the 1247-year range of January 1, 1753 (the minimum date of the T-SQL datetime datatype) to December 31, 2999 (just a reasonable stopping point). That comes out to 455,457 dates, but our friend T-SQL can create that table in less than a second:

if object_id('tempdb..#Calendar') is not null drop table #Calendar
go
with 
  L0(N) as (select 0 union all select 0 union all select 0) /* 3 Rows */
 ,L1(N) as (select 0 from L0 A cross join L0 B)             /* 9 Rows (3x3) */
 ,L2(N) as (select 0 from L1 A cross join L1 B)             /* 81 Rows (9x9) */
 ,L3(N) as (select 0 from L2 A cross join L2 B)             /* 6561 Rows (81x81) */
 ,L4(N) as (select 0 from L3 A cross join L3 B)             /* 43,046,721 Rows */
 ,Numbers(Num) as (select row_number() over (order by (select 0)) from L4)
select [Date]
      ,[Year]=year([Date])
      ,[Month]=month([Date])
      ,[Day]=day([Date])
      ,DayOfWeek=datename(weekday,[Date])
into #Calendar
from Numbers 
cross apply (select [Date]=convert(datetime,dateadd(day,Num-1,'17530101'))) F1
where Num between 1 and datediff(day,'17530101','29991231')+1
 

Now we can check out the 13th and see how often it falls on the 7 days of the week:

select DayOfWeek
      ,NumOccurrences=count(*)
from #Calendar
where [Day]=13
group by DayOfWeek
order by NumOccurrences desc
/*
DayOfWeek NumOccurrences
--------- --------------
Friday              2146
Wednesday           2142
Sunday              2142
Tuesday             2137
Monday              2133
Saturday            2132
Thursday            2132
*/

Hmmm… interesting. The 13th occurs on Friday more than any other day of the week. That’s kind of scary, don’t you think?

In fact, as you can see below, Friday the 13th is in the family of most frequently occurring dates in history (and the future):

with HighestOccurrences as
(
  select 
  top 1 
  with ties DayOfWeek
           ,[Day]
  from #Calendar
  group by DayOfWeek,[Day]
  order by count(*) desc
)
select [MostFrequentlyOccurringDay]
          =DayOfWeek
          +' the '
          +ltrim(str([Day]))
          +case
             when [Day] in (1,21,31) then 'st'
             when [Day] in (2,22) then 'nd'
             when [Day] in (3,23) then 'rd'
             else 'th'
           end
from HighestOccurrences
order by [Day],DayOfWeek
/*
MostFrequentlyOccurringDay
--------------------------
Sunday the 1st
Monday the 2nd
Tuesday the 3rd
...
Wednesday the 11th
Thursday the 12th
Friday the 13th
Saturday the 14th
Sunday the 15th
...
Thursday the 26th
Friday the 27th
Saturday the 28th
*/

Of course, logically, we can see that this also means that Thursday the 12th and Saturday the 14th and so on down to Sunday the 1st and up to Saturday the 28th occur the most frequently, but still… it’s kind of disconcerting that the most-feared day of all occurs the most often.

Let’s build a table of just the Friday the 13th dates:

if object_id('tempdb..#Friday13s') is not null drop table #Friday13s
go
select [Date]
      ,[Year]
      ,[Month]
into #Friday13s
from #Calendar
where DayOfWeek='Friday' and [Day]=13
 

That table is now populated with all the Friday-the-13th dates from 1753 to 2999… There are 2146 of them.

Wait a minute…

If you add the digits of 2146, you get 2 + 1 + 4 + 6 = 13.

Now that’s an interesting (and somewhat unsettling) coincidence, isn’t it? Ha-ha-ha.

Ahem… Oh well, never mind… Let’s move on…

Using that table, we can see that a Friday the 13th occurs every single year without exception:

select Result=case
                when (select count(distinct [Year]) from #Friday13s)=2999-1753+1
                then 'Yep... it occurs every single year'
                else 'Nope... there are some years when it doesn''t happen'
              end
/*
Result
----------------------------------
Yep... it occurs every single year
*/

Apparently there’s no escape!

And here’s an interesting tidbit: Every century (except the 1700s because they are only partially represented in our table) contains the exact same number of Friday the 13th’s:

select Century
      ,NumOccurrences=count(*)
from #Friday13s
cross apply (select Century=str([Year]/100*100,4)+'s') F1
where [Year]>=1800
group by Century
order by Century
/*
Century NumOccurrences
------- --------------
1800s              172
1900s              172
2000s              172
2100s              172
2200s              172
2300s              172
2400s              172
2500s              172
2600s              172
2700s              172
2800s              172
2900s              172
*/

Fascinating…

Now I mentioned earlier that I thought that we had experienced a bunch of Friday the 13th’s lately. Was I right? How many did we have this year (2009)?:

select [Date]
from #Friday13s
where [Year]=2009
order by [Date]
/*
Date
-----------------------
2009-02-13 00:00:00.000
2009-03-13 00:00:00.000
2009-11-13 00:00:00.000
*/

Three of them! A Triple! We just had one last month in November, and we had two months in a row with February and March! Omigosh!

Now that makes me wonder… What years contain the most occurrences of Friday the 13th?:

with HighestOccurrences as
(
  select 
  top 1 
  with ties [Year]
           ,NumOccurrences=count(*)
  from #Friday13s
  group by [Year]
  order by NumOccurrences desc
)
select [Year]
      ,NumOccurrences
from HighestOccurrences
order by [Year]
/*
Year NumOccurrences
---- --------------
1761              3
1764              3
1767              3
...
1931              3
1942              3
1953              3
1956              3
1959              3
1970              3
1981              3
1984              3
1987              3
1998              3
2009              3
2012              3
2015              3
2026              3
2037              3
2040              3
2043              3
...
2989              3
2992              3
2995              3
*/

Yikes! Sure enough… Three times is the most it can occur in a year, and this year (2009) is one of those! And we haven’t experienced a Triple like that since 1998!

Wait a minute… Look at that list again. It looks like we’re going to experience another Triple in just 3 short years… in 2012! And wait! We’ll have another Triple just 3 years after that… in 2015!

Whoa! I thought we had experienced many Friday the 13th’s lately, but it looks like we are only at the beginning of a really scary cluster of them! It’s a Triple-Triple-Triple (3-3-3) Cluster! Three Friday the 13th’s each occurring in three different years that are three years apart from each other.

This calls for more investigation. Let’s look at all the years that have a Triple… and then cluster them chronologically in groups of three… and then find the Clusters that span the least amount of time (i.e. the tightest, densest, scariest Clusters):

with TripleFri13Years as
(
  select [Year]
        ,RowNum=row_number() over (order by [Year])
  from #Friday13s
  group by [Year]
  having count(*)=3
)
,TripleFri13YearClusters as
(
  select FirstTriple=A.[Year]
        ,SecondTriple=B.[Year]
        ,ThirdTriple=C.[Year]
        ,YearSpan
        ,YearSpanRank=rank() over (order by YearSpan)
  from TripleFri13Years A
  join TripleFri13Years B on A.RowNum+1=B.RowNum
  join TripleFri13Years C on B.RowNum+1=C.RowNum
  cross apply (select YearSpan=C.[Year]-A.[Year]+1) F1
)
select FirstTriple
      ,SecondTriple
      ,ThirdTriple
      ,YearSpan
from TripleFri13YearClusters 
where YearSpanRank=1  /* The Shortest Span */
order by FirstTriple
/*
FirstTriple SecondTriple ThirdTriple YearSpan
----------- ------------ ----------- --------
       1761         1764        1767        7
       1789         1792        1795        7
       1801         1804        1807        7
       1829         1832        1835        7
       1857         1860        1863        7
       1885         1888        1891        7
       1925         1928        1931        7
       1953         1956        1959        7
       1981         1984        1987        7
       2009         2012        2015        7
       2037         2040        2043        7
       2065         2068        2071        7
       2093         2096        2099        7
       ...
       2933         2936        2939        7
       2961         2964        2967        7
       2989         2992        2995        7
*/

Oh no!

It looks like the 3-3-3 Cluster is the shortest cluster time span possible, and therefore, we are now on the verge of the worst possible Triple Cluster of Triple Friday-the-13th Years! The dreaded 3-3-3 cluster of 2009-2012-2015! The last time this kind of dense 3-3-3 cluster happened was the 7 year span of 1981-1984-1987.

Wait… Let me think… Did anything terrible happen from 1981 to 1987? Well, AIDS was first reported in 1981. And… uh-oh… (GULP)… The H1N1 Swine Flu Pandemic started this very year in 2009!

OH… MY… GAWD! It can’t be a coincidence!

So, let me make sure I got this straight… it seems that 2009 has the highest possible occurrences of Friday the 13th’s and it’s the beginning of a dreaded 3-3-3 Cluster. This doesn’t look good.

Wait!… I’ve gotta check this out… We already saw that every century has the same amount of Friday the 13th’s, but what about 3-3-3 Clusters?:

with TripleFri13Years as
(
  select [Year]
        ,RowNum=row_number() over (order by [Year])
  from #Friday13s
  group by [Year]
  having count(*)=3
)
,TripleFri13YearClusters as
(
  select FirstTriple=A.[Year]
        ,SecondTriple=B.[Year]
        ,ThirdTriple=C.[Year]
        ,YearSpan
        ,Ranking=rank() over (order by YearSpan)
  from TripleFri13Years A
  join TripleFri13Years B on A.RowNum+1=B.RowNum
  join TripleFri13Years C on B.RowNum+1=C.RowNum
  cross apply (select YearSpan=C.[Year]-A.[Year]+1) F1
)
select Century
      ,Num333Clusters=count(*)
from TripleFri13YearClusters 
cross apply (select Century=str(FirstTriple/100*100,4)+'s') F1
where Ranking=1 and FirstTriple>=1800
group by Century
order by Century
/*
Century Num333Clusters
------- --------------
1800s                4
1900s                3
2000s                4
2100s                4
2200s                4
2300s                3
2400s                4
2500s                4
2600s                4
2700s                3
2800s                4
2900s                4
*/

This current century has the most possible 3-3-3 Clusters as well!… There are going to be 4 of them. And it… Hold on a minute… Four 3-3-3 Clusters… Uh-oh… 4 + 3 + 3 + 3 = 13!

NOOOOO! It’s a sign! It looks like 2009 is the beginning of the end… Maybe the Mayans and Columbia Pictures are wrong about 2012 being the end of the world. All indicators point to NOW.

I don’t know about you, but now I’m really getting panicky… In fact, I think that…

OH NO!… IT CAN’T BE!

Are you sitting down?

Look at what happens if you add the ASCII values of the characters of the ominous string “FRIDAY THIRTEENTH 333 CLUSTER”:

select SumOfAsciiChars=sum(ascii(substring(OminousString,Number,1)))
from (select OminousString='FRIDAY THIRTEENTH 333 CLUSTER') X
join master..spt_values on Type='P' and Number between 1 and len(OminousString)
/*
SumOfAsciiChars
---------------
           2009
*/

It’s an omen! We’re doomed! MAKE IT STOP!! AAAAAIIIIIEEEEEEEE…

---

We regret to inform you that Mr Schulz has recently been admitted to the Institute for the Calendrically Insane. His planned release is on Friday, August 13th, 2010.