On Thu, Feb 18, 2016 at 7:27 PM, R Smith <rsmith at rsweb.co.za> wrote:
>
>> While I won't spoil your calendar fun, I have to ask, why not simply use
> the functionality SQLite already has to know exactly which months has which
> days?
>
Good question: the presentation is specifically about CTEs and i want to
show some things which people don't normally consider SQL a solution for.
It's "purely academic." In fact, our work projects, almost exclusively,
Oracle, but, aside from Oracle just generally being a pain in my side,
sqlite's just a better option for my presentation environment (and the
visitors won't have to hear me swear like a sailor at Oracle SQL Developer).
> For reference, consider this simple CTE and its months output for the
> current year:
>
> WITH CAL(mthDays) AS (
> SELECT strftime('%Y','now')||'-02-01 00:00:00'
> UNION ALL
> SELECT datetime(mthDays,'+1 month') FROM CAL LIMIT 12
> )
> SELECT date(mthDays,'-1 day') AS CalDate FROM CAL;
>
i've got similar functionality in place now, and have just collected the
day-of-week, so that i can calculate the display offsets and know where to
do line breaks.
(Btw: SQLite will work for dates below 1752 with a few caveats, the entire
> World didn't quite agree on dates before then - in fact, I'm not sure they
> do now!).
Every calendar known to man sucks rocks in some regard or other, so i'm not
gonna sweat it. This is just a demo, and i've got a few hours of budget
left on it, so i'm working on this as the finale. (The Mandelbrot CTE will
be first, just to kind of blow their minds, before we back way up and ease
into it.)
[stephan at host:~/tmp]$ sqlite3 < cal.sql
month|monthNum|day|dayOfWeek
Feb|2|1|1
Feb|2|2|2
Feb|2|3|3
Feb|2|4|4
Feb|2|5|5
Feb|2|6|6
Feb|2|7|7
Feb|2|8|1
Feb|2|9|2
Feb|2|10|3
Feb|2|11|4
Feb|2|12|5
Feb|2|13|6
Feb|2|14|7
Feb|2|15|1
Feb|2|16|2
Feb|2|17|3
Feb|2|18|4
Feb|2|19|5
Feb|2|20|6
Feb|2|21|7
Feb|2|22|1
Feb|2|23|2
Feb|2|24|3
Feb|2|25|4
Feb|2|26|5
Feb|2|27|6
Feb|2|28|7
Feb|2|29|1
(Note that i adjust day-of-week to Sunday=7 (instead of 0) because that's
just how we roll in Germany.)
--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
