PHP - Time part not being returned in custom datetime conversion function

Question

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?

Answer 1

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