-2

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.

programmer desk
  • 3
  • 4
  • 1
    Please provide a reproducible example of how to get a value of 17889.0 using the TIMESTAMP data type. I can't make it. https://3v4l.org/FYRq9 – jspit Nov 29 '22 at 10:15
  • The field named timestamp is of type Real and not of type TIMESTAMP. You write any number in it and you get it back when you read it. It is unclear whether this number has any reference to a date. – jspit Nov 29 '22 at 13:17
  • What have you tried so far? Where are you stuck? Why is this question tagged with PHP without any such code? – Nico Haase Nov 30 '22 at 07:56

3 Answers3

1

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?

Jacob Mulquin
  • 3,458
  • 1
  • 19
  • 22
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.

jspit
  • 7,276
  • 1
  • 9
  • 17
-1

Try this:

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

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

Coderio
  • 429
  • 2
  • 9