PHP How to return datetime(6) from Mysql?

Question

When I query a dateTime(6) PHP is truncating my 6 fractional seconds.

Here is an example of my php code:

$sql = 'SELECT date FROM tbl';
        $statement = $connection->prepare($sql);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($date);
        while ($statement->fetch()) 
        {
        echo $date."\n";
        }

This returns 2014-01-08 21:31:15 instead of 2014-01-08 21:31:15.995000 which is what is stored in the table. How can I get what is actually stored in the table?

Syllogistic

Prior to MySQL 5.6.4 fractional values weren't supported. There may be no way to do what you want. See this reference – Deciduous

I have no problem storing the DATETIME(6) values in my table with fractional seconds. I can even make the queries directly in mysql and the fractional seconds are returned. It is only within PHP that the milliseconds are left off. I am also using MySQL 5.6.13 – Syllogistic

It`s weird that you can store microseconds on 5.6.13 – Screed

@Syllogistic Oh well, can that idea, then. Looks like PHP is the culprit. Maybe PHP5.6 will help... – Deciduous

@Screed why is that weird when mysql has supported fractional seconds since 5.6.4 – Syllogistic

Answer 1

The problem is with the PHP Adapter you are using to communicate with Mysql. As far as I can see you are using PDO adapter to fetch the query result. However, this adapter does not support fractional seconds (YYYY-MM-DD HH:MM:SS.F).

The PDO adapter supports YYYY-MM-DD HH:MM:SS and automatically formats the value of datetime columns to this format.

This is a bug in PDO adapter itself. Please find the link below for reference: http://grokbase.com/t/php/php-bugs/11524dvh68/php-bug-bug-54648-new-pdo-forces-format-of-datetime-fields

Fatshan

Answer 2

You can format it to include it. The %f includes the six-digit microseconds.

 SELECT DATE_FORMAT(date, '%Y-%m-%d %H:%i:%s.%f') FROM tbl

Skolnik

Or just do CONCAT(date) to save you having to supply formatting. The default DATETIME formatting is to include the fractional seconds after a period. – Herv

Small typo for the time part ... %H:%i:%s.%f – Lobotomy

@malhal, you're a genius, thanks for that! Silly that it's 2018 and none of the built in PHP functions (PHP 7.1, mind you) can't handle fractional seconds correctly from MySQL – Urga

Answer 3

It's crazy this still isn't officially supported by mysqli in PHP 7.1, but anyway, here's a work around.

select concat(`DateTime6`)`DateTime6` from ...

Basically casting it as a string worked for me, as ugly as that is.

Urga

Answer 4

This bug was finally fixed in PHP v7.3.0.

For related bug details: https://bugs.php.net/bug.php?id=76386

Crucifer

Answer 5

DateTime does not support split seconds (microseconds or milliseconds etc.)

http://www.php.net/manual/en/class.datetime.php

https://bugs.php.net/bug.php?id=51950

Keramic

I don't think this applies here because when you select data from mysql using PHP the datatype is always returned as a string regardless of the datatype defined in the table. – Syllogistic

did you try print_r instead of echo? it seems odd its not jsut spitting out whats in the DB – Keramic

It is very odd. I tried print_r and it was still not returning the fractional seconds – Syllogistic

for debug i would try using old style mysqli mysqli_query() etc. Just to see if you get the same result. – Keramic

wow so I just ran it as a regular unprepared statement and it returned the date time with fractional seconds! I am not sure if this is a bug with prepared statements or an issue with fetch() but I will look into it. It didn't seem to matter whether or not the code was procedural or OO which I first thought you meant when I read old style – Syllogistic

sorry yeah. there is an old bug with prepared statements but it was supposed to have been fixed long ago. here maybe, at the bottom of the page --> dev.mysql.com/doc/refman/5.0/en/… – Keramic

@keaner PHP 5.5.9 with MySQL 5.6.21, mysqlnd 5.0.11, and this bug still exists. Maybe you could update your comment into your answer (and gather some upvote) so other unlucky programers and notice that too? – Charleton

Answer 6

in your case you might save the value in MySQL as VARCHAR and case / convert it to datetime when reading it.

I had the same problem and nothing helped me except converting

Ferri