determining is-leap-year in sqlite

classic Classic list List threaded Threaded
37 messages Options
12
Reply | Threaded
Open this post in threaded view
|

determining is-leap-year in sqlite

Stephan Beal-3
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@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)!)

Have fun!

--
----- 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Eric Rubin-Smith
>
>
>   select 2, 28 + (CAST(strftime("%j", c.year||"-12-31") AS INTEGER) % 365)
>

Here you assume that all years have either 365 or 366 days.  Would that it
were so!

Look at the year 1752 -- you may notice something odd happened that
September. :-)

Eric
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Stephan Beal-3
On Thu, Feb 18, 2016 at 6:59 PM, Eric Rubin-Smith <[hidden email]> wrote:

> >
> >
> >   select 2, 28 + (CAST(strftime("%j", c.year||"-12-31") AS INTEGER) %
> 365)
> >
>
> Here you assume that all years have either 365 or 366 days.  Would that it
> were so!
>
> Look at the year 1752 -- you may notice something odd happened that
> September. :-)
>

Yeah, i should have mentioned that i'm simplifying to the range of dates
"sometime within my lifetime." Anything else is irrelevant for my
presentation ;).

--
----- 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Richard Hipp-3
In reply to this post by Eric Rubin-Smith
On 2/18/16, Eric Rubin-Smith <[hidden email]> wrote:

>>
>>
>>   select 2, 28 + (CAST(strftime("%j", c.year||"-12-31") AS INTEGER) %
>> 365)
>>
>
> Here you assume that all years have either 365 or 366 days.  Would that it
> were so!
>
> Look at the year 1752 -- you may notice something odd happened that
> September. :-)
>

Only in Great Britain and her colonies.  The transition from the
Julian calendar to the Gregorian calendar happened at different times
in different countries over a span of nearly four centuries.

SQLite only understand Gregorian calendar dates.  If you ask for a
date prior to 1752 (or 1582) then SQLite gives you the "proleptic
Gregorian calendar" date.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

R Smith
In reply to this post by Stephan Beal-3


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@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!).

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Stephan Beal-3
On Thu, Feb 18, 2016 at 7:27 PM, R Smith <[hidden email]> 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@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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

R Smith


On 2016/02/18 8:34 PM, Stephan Beal wrote:
> 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.)

Oh nice - nothing like some mind-blowing, and CTE is still one of my
favourite additions to SQL. :)
May I offer this CTE from the tutorials in SQLitespeed in case you have
a Math library linked.
(your math function names for cos(), sin() and degtorad() may differ):


with graph(gWidth, aInc, gAngle, gCos, gCosA, gSin, gSinA) AS (
     SELECT 20, 10, -90, 0, 0, 0, 0
   UNION ALL
     SELECT gWidth,aInc,gAngle+aInc,
            printf('%d',                  round( cos( degtorad( gAngle +
aInc ) ) * gWidth + gWidth + 1 ) ),
            printf('%d', gWidth * 2 + 2 - round( cos( degtorad( gAngle +
aInc ) ) * gWidth + gWidth + 1 ) ),
            printf('%d',                  round( sin( degtorad( gAngle +
aInc ) ) * gWidth + gWidth + 1 ) ),
            printf('%d', gWidth * 2 + 2 - round( sin( degtorad( gAngle +
aInc ) ) * gWidth + gWidth + 1 ) )
       FROM graph
      WHERE gAngle < 720
)
SELECT printf( '%4d', gAngle ) AS Angle,
        printf( '.%'||gCos||'s%'||gCosA||'s', '+', '.' ) AS Cosine,
        printf( '.%'||gSin||'s%'||gSinA||'s', '+', '.' ) AS Sine
   FROM graph
  WHERE gAngle >= 0;


   -- Angle | Cosine | Sine
   -- ----- | --------------------------------------------- |
---------------------------------------------
   --   0   | .                                        +. |
.                    +                    .
   --   10  | .                                        +. |
.                       +                 .
   --   20  | .                                       + . |
.                           +             .
   --   30  | .                                     +   . |
.                              +          .
   --   40  | .                                   +     . |
.                                 +       .
   --   50  | .                                 +       . |
.                                   +     .
   --   60  | .                              +          . |
.                                     +   .
   --   70  | .                           +             . |
.                                       + .
   --   80  | .                       +                 . |
.                                        +.
   --   90  | .                    +                    . |
.                                        +.
   --  100  | .                 +                       . |
.                                        +.
   --  110  | .             +                           . |
.                                       + .
   --  120  | .          +                              . |
.                                     +   .
   --  130  | .       +                                 . |
.                                   +     .
   --  140  | .     +                                   . |
.                                 +       .
   --  150  | .   +                                     . |
.                              +          .
   --  160  | . +                                       . |
.                           +             .
   --  170  | .+                                        . |
.                       +                 .
   --  180  | .+                                        . |
.                    +                    .
   --  190  | .+                                        . |
.                 +                       .
   --  200  | . +                                       . |
.             +                           .
   --  210  | .   +                                     . | .          
+                              .
   --  220  | .     +                                   . | .      
+                                 .
   --  230  | .       +                                 . | .    
+                                   .
   --  240  | .          +                              . | .  
+                                     .
   --  250  | .             +                           . | .
+                                       .
   --  260  | .                 +                       . |
.+                                        .
   --  270  | .                    +                    . |
.+                                        .
   --  280  | .                       +                 . |
.+                                        .
   --  290  | .                           +             . | .
+                                       .
   --  300  | .                              +          . | .  
+                                     .
   --  310  | .                                 +       . | .    
+                                   .
   --  320  | .                                   +     . | .      
+                                 .
   --  330  | .                                     +   . | .          
+                              .
   --  340  | .                                       + . |
.             +                           .
   --  350  | .                                        +. |
.                 +                       .
   --  360  | .                                        +. |
.                    +                    .
   --  370  | .                                        +. |
.                       +                 .
   --  380  | .                                       + . |
.                           +             .
   --  390  | .                                     +   . |
.                              +          .
   --  400  | .                                   +     . |
.                                 +       .
   --  410  | .                                 +       . |
.                                   +     .
   --  420  | .                              +          . |
.                                     +   .
   --  430  | .                           +             . |
.                                       + .
   --  440  | .                       +                 . |
.                                        +.
   --  450  | .                    +                    . |
.                                        +.
   --  460  | .                 +                       . |
.                                        +.
   --  470  | .             +                           . |
.                                       + .
   --  480  | .          +                              . |
.                                     +   .
   --  490  | .       +                                 . |
.                                   +     .
   --  500  | .     +                                   . |
.                                 +       .
   --  510  | .   +                                     . |
.                              +          .
   --  520  | . +                                       . |
.                           +             .
   --  530  | .+                                        . |
.                       +                 .
   --  540  | .+                                        . |
.                    +                    .
   --  550  | .+                                        . |
.                 +                       .
   --  560  | . +                                       . |
.             +                           .
   --  570  | .   +                                     . | .          
+                              .
   --  580  | .     +                                   . | .      
+                                 .
   --  590  | .       +                                 . | .    
+                                   .
   --  600  | .          +                              . | .  
+                                     .
   --  610  | .             +                           . | .
+                                       .
   --  620  | .                 +                       . |
.+                                        .
   --  630  | .                    +                    . |
.+                                        .
   --  640  | .                       +                 . |
.+                                        .
   --  650  | .                           +             . | .
+                                       .
   --  660  | .                              +          . | .  
+                                     .
   --  670  | .                                 +       . | .    
+                                   .
   --  680  | .                                   +     . | .      
+                                 .
   --  690  | .                                     +   . | .          
+                              .
   --  700  | .                                       + . |
.             +                           .
   --  710  | .                                        +. |
.                 +                       .
   --  720  | .                                        +. |
.                    +                    .



The first CTE sets up some parameters in the first 3 fields used to draw
the graph - play with those parameters for fun.
(I hope the mail system don't mess up the format too much...)

Cheers!
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Stephan Beal-3
On Thu, Feb 18, 2016 at 7:53 PM, R Smith <[hidden email]> wrote:

> May I offer this CTE from the tutorials in SQLitespeed in case you have a
> Math library linked.
> (your math function names for cos(), sin() and degtorad() may differ):



> with graph(gWidth, aInc, gAngle, gCos, gCosA, gSin, gSinA) AS (
>     SELECT 20, 10, -90, 0, 0, 0, 0
>   UNION ALL
>     SELECT gWidth,aInc,gAngle+aInc,
>            printf('%d',                  round( cos( degtorad( gAngle +
> aInc ) ) * gWidth + gWidth + 1 ) ),
>            printf('%d', gWidth * 2 + 2 - round( cos( degtorad( gAngle +
> aInc ) ) * gWidth + gWidth + 1 ) ),
>            printf('%d',                  round( sin( degtorad( gAngle +
> aInc ) ) * gWidth + gWidth + 1 ) ),
>            printf('%d', gWidth * 2 + 2 - round( sin( degtorad( gAngle +
> aInc ) ) * gWidth + gWidth + 1 ) )
>       FROM graph
>      WHERE gAngle < 720
> )
> SELECT printf( '%4d', gAngle ) AS Angle,
>        printf( '.%'||gCos||'s%'||gCosA||'s', '+', '.' ) AS Cosine,
>        printf( '.%'||gSin||'s%'||gSinA||'s', '+', '.' ) AS Sine
>   FROM graph
>  WHERE gAngle >= 0;
>

Okay, that's going in the presentation.

HOLY COW!

Contrast with my modest:

...
select year, monthName, group_concat(dayOfMonth, ' ')
       from monthDays where year=2016
       group by monthNum
       order by monthNum;

year|monthName|group_concat(dayOfMonth, ' ')
2016|Jan|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Feb|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29
2016|Mar|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Apr|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30
2016|May|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Jun|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30
2016|Jul|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Aug|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Sep|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30
2016|Oct|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31
2016|Nov|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30
2016|Dec|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31

Next to figure out indentation/line breaks based on the dayOfWeek (which i
have but isn't shown above).


> The first CTE sets up some parameters in the first 3 fields used to draw
> the graph - play with those parameters for fun.
>

i wouldn't even know what to do with them :/.


> (I hope the mail system don't mess up the format too much...)
>

Nope - came across loud and clear.

--
----- 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

R Smith


On 2016/02/18 9:31 PM, Stephan Beal wrote:
>> The first CTE sets up some parameters in the first 3 fields used to draw
>> the graph - play with those parameters for fun.
>>
> i wouldn't even know what to do with them :/.

Change them of course! :) - to be specific, the first 3 values from the
line:

SELECT 20, 10, -90, 0, 0, 0, 0

can be adjusted. The first value (20) determines the width of the graph
(in characters),
the second value (10) determines the degrees increment or "Step" if you
will, and
the third value (-90) the start angle in degrees.

So to make the graph double as wide, stepping in 15 degree increments
and starting at 45 degrees... you can do:

SELECT 40, 15, 45, 0, 0, 0, 0


etc.
Nice job on the calendar and good luck with the presentation!

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Stephan Beal-3
On Thu, Feb 18, 2016 at 8:59 PM, R Smith <[hidden email]> wrote:

> etc.
> Nice job on the calendar and good luck with the presentation!


Okay, i've hit a small stump and i'm looking for a hint without giving it
away:

January and February 2016:

[stephan@host:~/tmp]$ sqlite3 < cal.sql
              1  2  3
  4  5  6  7  8  9 10
 11 12 13 14 15 16 17
 18 19 20 21 22 23 24
 25 26 27 28 29 30 31
  1  2  3  4  5  6  7
  8  9 10 11 12 13 14
 15 16 17 18 19 20 21
 22 23 24 25 26 27 28
 29

What's missing, obviously, is the month separators/labels. Trying to figure
that out now.

The indentation wasn't half as problematic as i expected.

--
----- 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Jose F. Gimenez
In reply to this post by Stephan Beal-3
Stephan,

> Yeah, i should have mentioned that i'm simplifying to the range of dates
> "sometime within my lifetime." Anything else is irrelevant for my
> presentation ;).

then, you only need to calculate ( year % 4 ). This gives you a window
from 1900-3-1 to 2100-2-28 (two complete centuries!). I belive none of
us (people in this mailingist) will still be alive in 2100  ;-)

Regards,
Jose F. Gimenez
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

R Smith
In reply to this post by Stephan Beal-3


On 2016/02/18 10:16 PM, Stephan Beal wrote:

> On Thu, Feb 18, 2016 at 8:59 PM, R Smith <[hidden email]> wrote:
>
>> etc.
>> Nice job on the calendar and good luck with the presentation!
>
> Okay, i've hit a small stump and i'm looking for a hint without giving it
> away:
>
> January and February 2016:
>
> [stephan@host:~/tmp]$ sqlite3 < cal.sql
>                1  2  3
>    4  5  6  7  8  9 10
>   11 12 13 14 15 16 17
>   18 19 20 21 22 23 24
>   25 26 27 28 29 30 31
>    1  2  3  4  5  6  7
>    8  9 10 11 12 13 14
>   15 16 17 18 19 20 21
>   22 23 24 25 26 27 28
>   29
>
> What's missing, obviously, is the month separators/labels. Trying to figure
> that out now.
>
> The indentation wasn't half as problematic as i expected.
>

Not sure how exactly you need the output to look, but you already know
which week lines to indent, those same lines must be preceded with a
Month Label, and the Month name index is just a strftime('%m',...) away,
perhaps joined to another CTE with:
  MTHName(mth, name) AS (
   SELECT 01, 'Januar' UNION ALL
   SELECT 02, 'Februar' UNION ALL
   SELECT 03, 'März' UNION ALL
   ...
   SELECT 12, 'Dezember'
)

- Which serendipitously illustrates another use of a CTE.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Stephan Beal-3
In reply to this post by Stephan Beal-3
On Thu, Feb 18, 2016 at 9:16 PM, Stephan Beal <[hidden email]> wrote:

> Okay, i've hit a small stump and i'm looking for a hint without giving it
> away:
>
> January and February 2016:
>
> [stephan@host:~/tmp]$ sqlite3 < cal.sql
>               1  2  3
>   4  5  6  7  8  9 10
>  11 12 13 14 15 16 17
>  18 19 20 21 22 23 24
>  25 26 27 28 29 30 31
>


[stephan@host:~/tmp]$ sqlite3 < cal.sql
---------------------\n              1  2  3
  4  5  6  7  8  9 10
 11 12 13 14 15 16 17
 18 19 20 21 22 23 24
 25 26 27 28 29 30 31
---------------------\n  1  2  3  4  5  6  7
  8  9 10 11 12 13 14
 15 16 17 18 19 20 21
 22 23 24 25 26 27 28
 29

doh! How do i get newlines?

@Jose: good point. %4 would have sufficed for this purpose.

@R. Smith: i'm already storing the month names in a CTE:

...
monthData(name, monthNum, year, days) as (
     select 'Jan', 1, y.year, 31 from years y
     union all
     select 'Feb', 2, y.year,
     28 + (CAST(strftime("%j", y.year||"-12-31") AS INTEGER) % 365)
     from years y union all
     select 'Mar', 3, y.year, 31 from years y union all
     select 'Apr', 4, y.year, 30 from years y union all
     select 'May', 5, y.year, 31 from years y union all
     select 'Jun', 6, y.year, 30 from years y union all
     select 'Jul', 7, y.year, 31 from years y union all
     select 'Aug', 8, y.year, 31 from years y union all
     select 'Sep', 9, y.year, 30 from years y union all
     select 'Oct', 10, y.year, 31 from years y union all
     select 'Nov', 11, y.year, 30 from years y union all
     select 'Dec', 12, y.year, 31 from years y
),


--
----- 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

R Smith


On 2016/02/18 10:38 PM, Stephan Beal wrote:

> On Thu, Feb 18, 2016 at 9:16 PM, Stephan Beal <[hidden email]> wrote:
>
>> Okay, i've hit a small stump and i'm looking for a hint without giving it
>> away:
>>
>> January and February 2016:
>>
>> [stephan@host:~/tmp]$ sqlite3 < cal.sql
>>                1  2  3
>>    4  5  6  7  8  9 10
>>   11 12 13 14 15 16 17
>>   18 19 20 21 22 23 24
>>   25 26 27 28 29 30 31
>>
>
> [stephan@host:~/tmp]$ sqlite3 < cal.sql
> ---------------------\n              1  2  3
>    4  5  6  7  8  9 10
>   11 12 13 14 15 16 17
>   18 19 20 21 22 23 24
>   25 26 27 28 29 30 31
> ---------------------\n  1  2  3  4  5  6  7
>    8  9 10 11 12 13 14
>   15 16 17 18 19 20 21
>   22 23 24 25 26 27 28
>   29
>
> doh! How do i get newlines?

Use the Mandelbrot set CTE for a cheat-sheet...
CHAR(13)||CHAR(10)... etc.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Richard Hipp-3
On 2/18/16, R Smith <[hidden email]> wrote:
>
> Use the Mandelbrot set CTE for a cheat-sheet...
> CHAR(13)||CHAR(10)... etc.
>

Simpler:  char(13,10).  The char() function takes one *or more*
arguments and generates one character for each.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Stephan Beal-3
In reply to this post by R Smith
On Thu, Feb 18, 2016 at 9:42 PM, R Smith <[hidden email]> wrote:

> Use the Mandelbrot set CTE for a cheat-sheet...
> CHAR(13)||CHAR(10)... etc.


Doh!

i could do with the \r, but CHAR(10) does indeed do the trick:

select str from strMonth
  where year=2016 and monthNum in (1,2)

looks like...

[stephan@host:~/tmp]$ sqlite3 < cal.sql
----------------------
      Jan 2016
              1  2  3
  4  5  6  7  8  9 10
 11 12 13 14 15 16 17
 18 19 20 21 22 23 24
 25 26 27 28 29 30 31
----------------------
      Feb 2016
  1  2  3  4  5  6  7
  8  9 10 11 12 13 14
 15 16 17 18 19 20 21
 22 23 24 25 26 27 28

Thank you!!!

Not half bad, if i may say so :).


i will post the complete solution (for a given definition of "solution")
once i've cleaned it up notably... and figure out how the last part of it
actually works. :/ Look for it over the weekend.

Thanks again!

--
----- 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Stephan Beal-3
On Thu, Feb 18, 2016 at 9:50 PM, Stephan Beal <[hidden email]> wrote:

> i could do with the \r, but CHAR(10) does indeed do the trick:
>

withOUT the \r...


>
> ----------------------
>       Feb 2016
>   1  2  3  4  5  6  7
>   8  9 10 11 12 13 14
>  15 16 17 18 19 20 21
>  22 23 24 25 26 27 28
>
> Thank you!!!
>
> Not half bad, if i may say so :).
>

Except that Feb. has 29 days this year (and my WITH-bits know that). Hmm.

--
----- 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Stephan Beal-3
On Thu, Feb 18, 2016 at 9:52 PM, Stephan Beal <[hidden email]> wrote:

> ----------------------
>>       Feb 2016
>>   1  2  3  4  5  6  7
>>   8  9 10 11 12 13 14
>>  15 16 17 18 19 20 21
>>  22 23 24 25 26 27 28
>>
>> Thank you!!!
>>
>> Not half bad, if i may say so :).
>>
>
> Except that Feb. has 29 days this year (and my WITH-bits know that). Hmm.
>

Just a rendering problem. The very last line of the very last month, no
matter how many months i'm generating, gets sliced off and i'm not sure
why. Oh, well. Time to walk the dog.

Thanks again to all for the feedback and suggestions!

--
----- 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Richard Hipp-3
On 2/18/16, Stephan Beal <[hidden email]> wrote:
>
> Thanks again to all for the feedback and suggestions!
>

After your talk, can we publish your calendar CTE as another example
in the SQLite documentation?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: determining is-leap-year in sqlite

Stephan Beal-3
On Thu, Feb 18, 2016 at 10:19 PM, Richard Hipp <[hidden email]> wrote:

> On 2/18/16, Stephan Beal <[hidden email]> wrote:
> >
> > Thanks again to all for the feedback and suggestions!
> >
>
> After your talk, can we publish your calendar CTE as another example
> in the SQLite documentation?
>

i would be humbled. No need to wait for the presentation (still no time
slot scheduled - might be 4-6 weeks). i'll shoot it off to you once i've
added some comments about what each part is for.

i got the last-line-truncated problem solved (missing a clause in a
group-by), so it currently looks like:

...
select str from strMonth sm
  where sm.year=2016 and sm.monthNum in (1,2,3)
;

[stephan@host:~/tmp]$ sqlite3 < cal.sql
----------------------
      Jan 2016
              1  2  3
  4  5  6  7  8  9 10
 11 12 13 14 15 16 17
 18 19 20 21 22 23 24
 25 26 27 28 29 30 31

----------------------
      Feb 2016
  1  2  3  4  5  6  7
  8  9 10 11 12 13 14
 15 16 17 18 19 20 21
 22 23 24 25 26 27 28
 29

----------------------
      Mar 2016
     1  2  3  4  5  6
  7  8  9 10 11 12 13
 14 15 16 17 18 19 20
 21 22 23 24 25 26 27
 28 29 30 31


NOW to try to mark the current day... no... i'll leave that as an exercise
for the attendees!

--
----- 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12