Re: Design-stage advice and opinions welcomed

From: Stefan Ram <ram_at_zedat.fu-berlin.de>
Date: 28 Sep 2015 04:29:37 GMT
Message-ID: <easter-20150928052605_at_ram.dialup.fu-berlin.de>

Geoff Muldoon <geoff.muldoon_at_trap.gmail.com> writes: >Easter.

  There is a formula for easter, and I tried to convert it into MySQL.   MySQL, when it's best, is like LISP. (It's only annoying that every   derived table must have it's own alias, even when one does not need   this alias, and that subqueries cannot be used in VIEWs IIRC.)

mysql> CREATE TABLE YEAR ( YEAR INTEGER );
mysql> INSERT INTO YEAR ( YEAR ) VALUES


    -> ( '1980' ), ( '1981' ), ( '1982' ), ( '1983' ), ( '1984' ),

    -> ( '1985' ), ( '1986' ), ( '1987' ), ( '1988' ), ( '1989' ),

    -> ( '1990' ), ( '1991' ), ( '1992' ), ( '1993' ), ( '1994' ),

    -> ( '1995' ), ( '1996' ), ( '1997' ), ( '1998' ), ( '1999' ),

    -> ( '2000' ), ( '2001' ), ( '2002' ), ( '2003' ), ( '2004' ),

    -> ( '2005' ), ( '2006' ), ( '2007' ), ( '2008' ), ( '2009' ),

    -> ( '2010' ), ( '2011' ), ( '2012' ), ( '2013' ), ( '2014' ),

    -> ( '2015' ), ( '2016' ), ( '2017' ), ( '2018' ), ( '2019' ),

-> ( '2020' ), ( '2021' ), ( '2022' ), ( '2023' ), ( '2024' ),
-> ( '2025' ), ( '2026' ), ( '2027' ), ( '2028' ), ( '2029' );

mysql> SELECT Y AS YEAR, N AS MONTH,( H - M + R + N + 19 )% 32 AS DAY

-> FROM ( SELECT Y, ( H - M + R + 90 )DIV 25 AS N, H, M, R
-> FROM ( SELECT Y,( 2 * E + 2 * J - K - H + M + 32)% 7 AS R, H, M
-> FROM ( SELECT Y,( A + 11 * H )DIV 319 AS M, E, J, K, H
-> FROM ( SELECT Y, A, C, E, ( 19 * a + b - d - g + 15 )% 30 AS H, C DIV 4 AS J, C % 4 AS K
-> FROM ( SELECT Y, A, B, C, B DIV 4 AS D, B % 4 AS E,( 8 * B + 13 )DIV 25 AS G
-> FROM ( SELECT Y, Y % 19 AS A, Y DIV 100 AS B, Y % 100 AS C
-> FROM ( SELECT YEAR AS Y FROM YEAR ) AS T0 ) AS T1 ) AS T2 ) AS T3 ) AS T4 ) AS T5 ) AS T6;
+------+-------+------+
| YEAR | MONTH | DAY |
+------+-------+------+

| 1980 |     4 |    6 |
| 1981 |     4 |   19 |
| 1982 |     4 |   11 |
| 1983 |     4 |    3 |
| 1984 |     4 |   22 |
| 1985 |     4 |    7 |
| 1986 |     3 |   30 |
| 1987 |     4 |   19 |
| 1988 |     4 |    3 |
| 1989 |     3 |   26 |
| 1990 |     4 |   15 |
| 1991 |     3 |   31 |
| 1992 |     4 |   19 |
| 1993 |     4 |   11 |
| 1994 |     4 |    3 |
| 1995 |     4 |   16 |
| 1996 |     4 |    7 |
| 1997 |     3 |   30 |
| 1998 |     4 |   12 |
| 1999 |     4 |    4 |
| 2000 |     4 |   23 |
| 2001 |     4 |   15 |
| 2002 |     3 |   31 |
| 2003 |     4 |   20 |
| 2004 |     4 |   11 |
| 2005 |     3 |   27 |
| 2006 |     4 |   16 |
| 2007 |     4 |    8 |
| 2008 |     3 |   23 |
| 2009 |     4 |   12 |
| 2010 |     4 |    4 |
| 2011 |     4 |   24 |
| 2012 |     4 |    8 |
| 2013 |     3 |   31 |
| 2014 |     4 |   20 |
| 2015 |     4 |    5 |
| 2016 |     3 |   27 |
| 2017 |     4 |   16 |
| 2018 |     4 |    1 |
| 2019 |     4 |   21 |
| 2020 |     4 |   12 |
| 2021 |     4 |    4 |
| 2022 |     4 |   17 |
| 2023 |     4 |    9 |
| 2024 |     3 |   31 |
| 2025 |     4 |   20 |
| 2026 |     4 |    5 |
| 2027 |     3 |   28 |
| 2028 |     4 |   16 |
| 2029 |     4 |    1 |

+------+-------+------+
50 rows in set (0.00 sec) Received on Mon Sep 28 2015 - 06:29:37 CEST