On 2016/02/18 7:46 PM, Stephan Beal wrote:
> Hi, all,
>
> i just found a useful trick i thought someone else might be able to use...
>
> As part of a presentation i'm preparing to introduce colleagues to CTEs,
> i'm attempting to build a calendar (with output similar to the Unix 'cal'
> command). (Please no spoilers - let me figure it out!)
>
> As part of that, i've of course got to determine if it's a leap year. My
> first attempt was something like this snippet:
>
> with
> config(year,month) AS(
> select strftime("%Y"), strftime("%m")
> ),
> daysPerMonth(month, days) as (
> select 1, 31
> union all
> select 2,
> case WHEN NOT c.year%4 AND c.year%100
> THEN 29
> ELSE CASE
> WHEN NOT c.year%4 AND NOT c.year%100 AND NOT c.year%400
> THEN 29
> ELSE 28
> END
> END
> ...
>
> (Not 100% sure that's right, but the replacement solution makes it a moot
> point.)
>
> Then it occurred to me that strftime() can tell us the day-of-year, which
> answers that question for us much more tersely because December has a fixed
> number of days:
>
> ...
> daysPerMonth(month, days) as (
> select 1, 31
> union all
> select 2,
> CASE WHEN '366'=strftime("%j", c.year||"-12-31") THEN 29 ELSE 28 END
> from config c
> ...
> )
>
> Note that string comparison is required, as +"2016" in sqlite does not
> coerce a string to an integer like -"2016" does (i'm a bit surprised by
> that, but expect it's an immutable compatibility constraint).
>
> Similar reformulations could also work, taking care to cast the strftime
> results to integers and performing a single math op:
>
> select 2, 28 + (CAST(strftime("%j", c.year||"-12-31") AS INTEGER) % 365)
>
> (i think that's about as terse as i can get it.)
>
> So far my calendar CTE outputs:
>
> [stephan at host:~/tmp]$ sqlite3 < cal.sql
> 2016|1|31
> 2016|2|29
> 2016|3|31
> 2016|4|30
> 2016|5|31
> 2016|6|30
> 2016|7|31
> 2016|8|31
> 2016|9|30
> 2016|10|31
> 2016|11|30
> 2016|12|31
>
> Obviously still lots to do here. (Again, _please_ don't post spoilers for
> calendar CTE solutions (in this thread)!)
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?
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;
-- CalDate
-- ------------
-- 2016-01-31
-- 2016-02-29
-- 2016-03-31
-- 2016-04-30
-- 2016-05-31
-- 2016-06-30
-- 2016-07-31
-- 2016-08-31
-- 2016-09-30
-- 2016-10-31
-- 2016-11-30
-- 2016-12-31
Which can easily be adjusted to supply whatever values you need in the
eventual calendar.
>
> Have fun!
>
Always!
(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!).
