ORACLENERD: Fun with SQL

This year is kind of fun, my birthday is on November 12th (next Tuesday, if you want to send gifts). That means it will fall on 11/12/13. Even better perhaps, katezilla's birthday is December 13th. 12/13/14. What does this have to do with SQL?

Someone mentioned to me last night that this wouldn't happen again for 990 years. I was thinking, "wow, I'm super special now (along with the other 1/365 * 6 billion people)!" Or am I? I had to do the math. Since date math is hard, and math is hard, and I'm good at neither, SQL to the rescue.

select 
  to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
  to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
  to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
  sysdate + ( rownum - 1 ) actual
from dual
  connect by level <= 100000

(In case you were wondering, 100,000 days is just shy of 274 years. 273.972602739726027397260273972602739726 to be more precise.)

That query gives me this:

MONTH_OF DAY_OF YEAR_OF ACTUAL   
-------- ------ ------- ----------
11       06     13      2013/11/06 
11       07     13      2013/11/07 
11       08     13      2013/11/08 
11       09     13      2013/11/09 
11       10     13      2013/11/10 
11       11     13      2013/11/11 
...

So how can I figure out where DAY_OF is equal to MONTH_OF + 1 and YEAR_OF is equal to DAY_OF + 1? In my head, I thought it would be far more complicated, but it's not.

select *
from
(
  select 
    to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
    to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
    to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
    sysdate + ( rownum - 1 ) actual
  from dual
    connect by level <= 100000
)
where month_of + 1 = day_of
  and day_of + 1 = year_of
order by actual asc

Which gives me:

MONTH_OF DAY_OF YEAR_OF ACTUAL   
-------- ------ ------- ----------
11       12     13      2013/11/12 
12       13     14      2014/12/13 
01       02     03      2103/01/02 
02       03     04      2104/02/03 
03       04     05      2105/03/04 
04       05     06      2106/04/05 
05       06     07      2107/05/06 
...

OK, so it looks closer to 100 years, not 990. Let's subtract. LAG to the rescue.

select
  actual,
  lag( actual, 1 ) over ( partition by 1 order by 2 ) previous_actual,
  actual - ( lag( actual, 1 ) over ( partition by 1 order by 2 ) ) time_between
from
(
  select 
    to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
    to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
    to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
    sysdate + ( rownum - 1 ) actual
  from dual
    connect by level <= 100000
)
where month_of + 1 = day_of
  and day_of + 1 = year_of
order by actual asc

Which gives me:

ACTUAL     PREVIOUS_ACTUAL TIME_BETWEEN
---------- --------------- ------------
2013/11/12                              
2014/12/13                          396 
2103/01/02                        32161 
2104/02/03                          397 
2105/03/04                          395 
2106/04/05                          397 
2107/05/06                          396 
2108/06/07                          398 
2109/07/08                          396 
2110/08/09                          397 
2111/09/10                          397 
2112/10/11                          397 
2113/11/12                          397 
2114/12/13                          396 
2203/01/02                        32161

So, it looks like every 88 years it occurs and is followed by 11 consecutive years of matching numbers. The next time 11/12/13 and 12/13/14 will appear is in 2113 and 2114. Yay for SQL!

6 comments:

StewNovember 10, 2013 at 9:08 AM
Fun stuff! Happy birthday next Tuesday!
Kim Berg HansenNovember 14, 2013 at 3:57 AM
Fun to play with dates. Consider:

01/02/xx03
02/03/xx04
...
11/12/xx13
12/13/xx14 <==

The last line is only valid in MM/DD/YYYY - not in DD/MM/YYYY.
So in this case americans have one extra such day compared to the standardized world, where a special date is where day is one less than month and not the other way around ;-)

PS.

December 13th is Saint Lucy's Day: http://en.wikipedia.org/wiki/Saint_Lucy's_Day

On that day there will be hundreds of Santa Lucia processions in Scandinavia with singing girls in white dresses holding candles. Like this one: http://www.youtube.com/watch?v=aqp6il_QVbA

Imagine they are all singing for Katezilla :-)
oraclenerdNovember 14, 2013 at 10:27 AM
Don't give me a hard time about America's peculiarities. :)

Posted to katezilla's timeline.
Jack PorterNovember 19, 2013 at 11:48 PM
This is great! I really like to try this out. I'll be sure to tell my friend about this. Thanks for sharing.
Tom WurzbachJanuary 5, 2014 at 5:34 PM
Nice. I love when things can be proven incorrect. One of my least favorite internet memes is the "this month has a blue moon and blue moons only occur once every bazillion years" or somesuch. They're just not that rare... And that, too, would be an interesting query.
Chris HuntJanuary 14, 2014 at 8:04 AM
No SQL required really.

If dates are presented m/d/y, d=m+1, y=m+2, and m is a number between 1 and 12, the only possible answers are:

01/02/03
02/03/04
03/04/05
04/05/06
05/06/07
06/07/08
07/08/09
08/09/10
09/10/11
10/11/12
11/12/13
12/13/14

Wednesday, November 6, 2013

Fun with SQL - My Birthday

6 comments: