Milliseconds in datetime omitted in linux but not windows

[RikSomers]

Hi!

I've encountered a small bug with datetimes when working on some tests for our application.

I wrote a test that makes sure a certain time can be pulled from the database correctly and within the right format. The test in question looks something like this:

/** @test */
public function it_can_get_the_latest_wiptime()
{
	factory(Barcode::class)->create(['LASTWIPTIME' => '2017-10-21 01:52:51.000']);

	/** @var BarcodeRepository $repo */
	$repo = $this->app->make(BarcodeRepository::class);

	$result = $repo->findLastWIPTime();

	$this->assertEquals('2017-10-21 01:52:51.000', $result);
}

The weird thing is, this test runs fine on a local apache server using msphpsql on a Windows machine using PHP 7.0.
However, on our continuous integration, running a Linux docker image (Ubuntu 16.04) with the same PHP version reports the result as being 2017-10-21 01:52:51. Notice the lack of milliseconds.

Both of the webservers (local and docker) connect to the same database, which is a normal SQL Server. So therefor configuration settings on the database end should not be the problem.

Now, we do not necessarily need the precision of milliseconds, but the way it is now, the test either work on the local machine by providing the milliseconds in the assertion, or they run on our CI by omitting the milliseconds in the assertion.

Am I overlooking something, or is there a discrepency between the linux and windows variants of the driver?

[v-dareck]

Hi @RikSomers. From your test we can't tell how to repro your error. Can you provide a repro script that uses msphpsql?

Is the issue with sqlsrv or pdo?
What is the data type of the LASTWIPTIME column?
When you call findLastWIPTime is it retrieving the data as a string or a datetime?

[RikSomers]

Hi,

I'll try to see if I can create a standalone script that reproduces the error.

As for your other questions;
I'm not sure with what the issue is. We use a framework (Laravel) for our code, which handles all the connection management.
The datatype of the LASTWIPTIME column is a nullable datetime field.
As far as I am aware it is retrieved as a string. Adding another assertion in PHPUnit confirms this;
$this->assertInternalType('string', $result); // assertion passes

[v-dareck]

Hi @RikSomers, I tried the script below on Windows 10 and Ubuntu 16.04 against SQL Server 2008R2 and 2016. In all cases the return value was
string(23) "2017-10-21 01:52:51.023"

If I changed the insert value to have 0 milliseconds (2017-10-21 01:52:51.023) then the return value was
string(23) "2017-10-21 01:52:51.000".

$conn = new PDO("sqlsrv:server=$serverName", $username, $password);
// Create table
$query = "CREATE TABLE $tableName (col1 datetime)";
$stmt = $conn->exec($query);
$query ="INSERT INTO $tableName values ('2017-10-21 01:52:51.023')";
$stmt = $conn->exec($query);
$query = "SELECT col1 FROM $tableName";
$stmt = $conn->query($query);
$value = $stmt->fetchColumn();
var_dump ($value);

I'm unsure how to proceed as I cannot repro the error.
Maybe check in SQL Server Management Studio that the correct date time value with Milliseconds was inserted into the table. That would eliminate the insert being the cause.

Also you could turn on ODBC tracing?
The trace file should tell you if the right value is being returned from SQL Server.
Edit odbcinst.ini:

[ODBC]
Trace=Yes
TraceFile=/path/to/sql.log

[RikSomers]

Hi,

It might be an issue with the framework we are using then. We are in the middle of some other datetime format issues.
I'll keep you updated.

[Hadis-Knj]

@RikSomers Any update on this issue?

[RikSomers]

Hey,

Have been under the weather the last couple of days, so didn't have any time yet to test it out. But it might be a framework related issue rather than a driver issue.

[Hadis-Knj]

Thanks @RikSomers I'll close the issue, feel free to reopen it

[ducktype]

i can confirm this behaviour...using:

• php_pdo_sqlsrv-4.1.8preview-7.1-nts-vc14-x64.zip
• php-7.1.4-nts-Win32-VC14-x64.zip
• odbc driver v13
• windows small business server 2011 standard
• Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
• plain PDO (no frameworks/layers)

quering with pdo returns milliseconds for datetime columns es: 2017-04-04 15:55:54.000,
and sqlserver does not have a session option to specify a custom date format es: SET DATEFORMAT 'Y-m-d H:i:s',
so is desiderable to have this php driver have an option to remove millisecs in dates
possibly on by default!

[RikSomers]

@ducktype If you think it is a bug maybe it's better to create a new issue with the info you provided here and point to this issue.

[ownmaster]

@RikSomers I have same problem with Laravel. Seems like this is not driver issue. But can you give some ideas about what can lead to it? Maybe some connection parameters, or something else?