title: MySQL function for emulating Java's LocalDate.fromEpochDay()
body: <p>as I have some legacy values in my MySQL database that stand for a java.time.LocalDate value, I wanted to create a conversion function to get back the correct MySQL <code>DATE</code> value. The initial values were generated via <code>LocalDate.toEpochDay()</code>. So I looked at the corresponding <code>LocalDate.ofEpochDay(long epochDay)</code> method (see <a href="http://hg.openjdk.java.net/jdk8/jdk8/jdk/file/687fd7c7986d/src/share/classes/java/time/LocalDate.java#l313" rel="nofollow noreferrer">here</a>) and tried to translate it. But my translation produces negative days which I cannot interpret.</p>
  <p>Does anyone have an idea why the following function does not work?</p>
  <pre><code>create function fromEpochDays(days bigint)
  RETURNS date DETERMINISTIC
  --RETURNS varchar(50) DETERMINISTIC
  BEGIN
      DECLARE dom bigint;
      DECLARE month bigint;
      DECLARE year bigint;
      DECLARE zeroDay bigint;
      DECLARE adjust bigint;
      DECLARE daysPerCycle bigint;
      DECLARE adjustCycles bigint;
      DECLARE yearEst bigint;
      DECLARE doyEst bigint;
      DECLARE marchDoy0 bigint;
      DECLARE marchMonth0 bigint;
      DECLARE dateToParse CHAR(100);
      DECLARE valToCheck CHAR(100);
  
      SET daysPerCycle = 146097;
      SET adjustCycles = 0;
      SET adjust = 0;
  
      SET zeroDay = days + 719528 - 60;
      IF zeroDay &lt; 0 THEN
          SET adjustCycles = (zeroDay + 1) / daysPerCycle - 1;
          SET adjust = adjustCycles * 400;
          SET zeroDay = zeroDay + (-adjustCycles * daysPerCycle);
      END IF;
  
      SET yearEst = (400 * zeroDay + 591) / daysPerCycle;
      SET doyEst = zeroDay - (365 * yearEst + yearEst / 4 - yearEst / 100 + yearEst / 400);
      IF doyEst &lt; 0 THEN
          SET yearEst = yearEst - 1;
          SET doyEst = zeroDay - (365 * yearEst + yearEst / 4 - yearEst / 100 + yearEst / 400);
      END IF;
      
      SET yearEst = yearEst + adjust;
      SET marchDoy0 = doyEst;
      SET marchMonth0 = (marchDoy0 * 5 + 2) / 153;
      SET month = MOD((marchMonth0 + 2),12) + 1;
      SET dom = marchDoy0 - (marchMonth0 * 306 + 5) / 10 + 1;
      SET year = yearEst + marchMonth0 / 10;
      SET dateToParse = concat(cast(dom as char(20)),',',cast(month as char(20)),',',cast(year as char(20)));
      return STR_TO_DATE(dateToParse,'%d,%m,%Y');
  END;
  </code></pre>
  <p>If I change the function to return only the variable <code>dateToParse</code> and call it with different values I get the following:</p>
  <pre><code>select
   fromEpochDays_AsChar(12),
   fromEpochDays_AsChar(13),
   fromEpochDays_AsChar(14),
   fromEpochDays_AsChar(15),
   fromEpochDays_AsChar(16),
   fromEpochDays_AsChar(29),
   fromEpochDays_AsChar(30),
   fromEpochDays_AsChar(31),
   fromEpochDays_AsChar(32)
  
  </code></pre>
  <pre><code>fromEpochDays_AsChar(12)    13,1,1970
  fromEpochDays_AsChar(13)    14,1,1970
  fromEpochDays_AsChar(14)    15,1,1970
  fromEpochDays_AsChar(15)    -15,2,1970
  fromEpochDays_AsChar(16)    -14,2,1970
  fromEpochDays_AsChar(29)    -1,2,1970
  fromEpochDays_AsChar(30)    0,2,1970
  fromEpochDays_AsChar(31)    1,2,1970
  fromEpochDays_AsChar(32)    2,2,1970
  
  </code></pre>
  <p>Values from 0 to 14 yield correct values but the following look obviously weird ... Looks like only the first half of the month is computed correctly.</p>