18位Julian Date / TimeStamp PHP转换？

Question

I am currently working on a project for my work, it is using a lot of data from the mysql databases, alot of rows from the databases have a TimeStamp but they are 18 digits long.

I have a calculation that converts it into a friendly date in excel, DD-MM-YY HH:MM:SS.

The calculation is as follows (212262066420979000/86400000000) - 2415018.5 = value, and then format in excel into dd-mm-yy hh-mm-ss.

If I use php to do this calculation, I get the right value, but I can't set the format to look like a friendly date like you can in excel.

Is there any easy way of doing this in php?

Here is my code below

$sql = ("SELECT * FROM varmeas WHERE VARIABLE = 'WTSE';");

$result = mysqli_query($conn, $sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysqli_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

echo "<table border='1'>
<tr>
<th>ROUTING_POS</th>
<th>VARIABLE</th>
<th>VAR_MEASURE_1</th>
<th>DATETIME_STAMP</th>
<th>FRIENDLY_DATE</th>
</tr>";

while ($row = mysqli_fetch_assoc($result)) {

  echo "<tr>";
  echo "<td>" . $row['ROUTING_POS'] . "</td>";
  echo "<td>" . $row['VARIABLE'] . "</td>";
  echo "<td>" . $row['VAR_MEASURE_1'] . "</td>";
  echo "<td>" . $row['DATETIME_STAMP'] . "</td>";
  echo "</tr>";


}
echo "</table>";

?>

This would output a table like -

ROUTING_POS VARIABLE    VAR_MEASURE_1          DATETIME_STAMP   
FLIS        WTSE            22.20             212262066888394000    

Answer 1

(212262066420979000/86400000000) - 2415018.5 = value

Gives a value of 41718.379872442, which is an MS Excel timestamp value for 20/03/2014 09:07:01

To convert from an Excel timestamp to a Unix timestamp (based on the Windows 1900 calendar)

For a quick and dirty conversion from an Excel timestamp to a unix timestamp that can be used with PHP date functions:

$excelValue =  (212262066420979000/86400000000) - 2415018.5;
$unixValue = ($excelValue - 25569) * 86400;

Note that this will return a value in UST

date_default_timezone_set('UTC');
echo date('Y-m-d H:i:s', $unixValue);

or

$d = new DateTime('@' . (int)$unixValue, new DateTimeZone('UTC'));
echo $d->format('Y-m-d H:i:s');

EDIT

To integrate it with your existing code is as simple as adding:

$excelValue =  ($row['DATETIME_STAMP'] / 86400000000) - 2415018.5;
$unixValue = ($excelValue - 25569) * 86400;
$d = new DateTime('@' . (int)$unixValue, new DateTimeZone('UTC'));
echo "<td>" . $d->format('Y-m-d H:i:s') . "</td>";

immediately after

echo "<td>" . $row['DATETIME_STAMP'] . "</td>";