Using PHP, I need to extract encoded datetime values from a SQLite database for display on a web page. The datetime are encoded in a custom format, as described in this question.

However, my code is only extracting the date part and not the time part.

This is my code:

function convert_datetime_database_to_php ($database_datetime)
{
    $php_datetime = bcsub(bcdiv($database_datetime, 864000000000), 693593);
    $php_datetime = bcmul(bcsub($php_datetime, 25569), 86400);

    $php_datetime = gmdate('Y-m-d g:i A', $php_datetime);

    return $php_datetime;
}

Below are the actual datetime values stored in the database, and the equivalent date/time value after conversion (from Delphi code):

1. 637911704796343035 = 6/18/2022 5:34:39 PM
2. 637911809759649501 = 6/18/2022 8:29:35 PM

However the PHP code will return "2022-06-18 12:00 AM" for both above values, skipping the conversion of the time part.

What is wrong with my PHP code and how can I correctly extract the time part?

CodePudding user response：

You're losing precision at the division step and then multiplying the number back, so it ends up with significant precision loss. See https://www.php.net/manual/en/function.bcdiv.php for the scale arg.

From convert time_t to ticks the nanoseconds from 01/01/0001 to 01/01/1970 is 621355968000000000 (Please verify, it may not be accurate).

You can then simplify to:

function convert_datetime_database_to_php ($database_datetime)
{
    // Nano 01/01/0001 to 01/01/1970 (.NET)
    $php_datetime = bcsub($database_datetime, 621355968000000000);

    // Nano to sec
    $php_datetime = bcdiv($php_datetime, 10000000);

    return gmdate('Y-m-d g:i:s A', $php_datetime);
}

Which returns your expected output of:

2022-06-18 5:34:39 PM
2022-06-18 8:29:35 PM