Question 22020 in Mysql Question posted in
The official MySQL documentation can be found here.

Convert SQLite to MySQL datetime

I have SQLite DB one table contains datetime field

with datatype "timestamp" REAL value is 18696.0

attach image for table structure

So, I want this 18696.0 value to be converted into MySQL Y-m-d format and result should be 2021-03-10

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

SQLite timestamp converted into MySQL timestamp.

Tags:

3

Answers


  1. 0

    Try this:

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

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

    Login or Signup to reply.
  2. 0

    EDIT: Thankyou for updating your question with the correct number and what date it should represent.

    You can achieve what you need with a function that adds the days onto the Unix Epoch date:

    function realDateToYmd($real, $outputFormat='Y-m-d')
{
    $date = new DateTime('1970-01-01');
    $date->modify('+' . intval($real) . ' days');
    
    return $date->format($outputFormat);
}

echo realDateToYmd('18696.0');
// returns 2021-03-10

    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-%02d-%02d', $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?

    Login or Signup to reply.
  3. 0

    If this float number 18696.0 represents the number of days since 1970-01-01 then the date can also be calculated like this:

    $days = 18696.0;

$dt = date_create('@'.((int)($days * 86400)));
$mysqlDate = $dt->format('Y-m-d');  //"2021-03-10"

    background information

    Or simply with gmdate:

    $mySqlDate = gmdate('Y-m-d',$days*86400);

    The days are simply converted into seconds to get a valid timestamp for gmdate.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search