Convert SQLite to MySQL datetime-CodePudding

I have SQLite DB one table contains datetime field

with datatype "timestamp" REAL value is 17889.0

So, I want this 17889.0 value to be converted into MySQL Y-m-d

I have didn't found any solution online. any help would be appreciated.

SQLite timestamp converted into MySQL timestamp.

CodePudding user response：

Try this:

<?php
    echo date('Y-m-d H:i:s', 17889);
?>

Output: 1970-01-01 04:58:09

CodePudding user response：

SQLite dates stored in REAL data type stores dates as a Julian Day.

From https://www.sqlite.org/datatype3.html

REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

PHP has a jdtogregorian function, in which one comment has a handy function to convert to ISO8601 dates:

function JDtoISO8601($JD) {
    if ($JD <= 1721425) $JD  = 365;
    list($month, $day, $year) = explode('/', jdtogregorian($JD));
    return sprintf('% 05d-d-d', $year, $month, $day);
}

echo JDtoISO8601('17889.0');
// Results in -4664-11-16

The results don't exactly look right, is it definitely 17889.0 in SQLite?