17

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?

Improve this question
5
  • 1
    Prior to MySQL 5.6.4 fractional values weren't supported. There may be no way to do what you want. See this reference
    – user1864610
    Commented Feb 25, 2014 at 21:32
  • 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
    – Landon
    Commented Feb 25, 2014 at 21:37
  • It`s weird that you can store microseconds on 5.6.13
    – Mihai
    Commented Feb 25, 2014 at 21:49
  • @Landon Oh well, can that idea, then. Looks like PHP is the culprit. Maybe PHP5.6 will help...
    – user1864610
    Commented Feb 25, 2014 at 21:54
  • 3
    @Mihai why is that weird when mysql has supported fractional seconds since 5.6.4
    – Landon
    Commented Feb 25, 2014 at 21:55

6 Answers 6

Reset to default
8

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

Improve this answer
0
5

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
Improve this answer
3
  • 3
    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.
    – malhal
    Commented Aug 10, 2015 at 21:15
  • 1
    Small typo for the time part ... %H:%i:%s.%f
    – Richard A Quadling
    Commented Jan 16, 2017 at 14:09
  • 1
    @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
    – Brian Leishman
    Commented Jan 19, 2018 at 22:44
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.

Improve this answer
3

This bug was finally fixed in PHP v7.3.0.

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

Improve this answer
-2

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

Improve this answer
7
  • 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.
    – Landon
    Commented Feb 26, 2014 at 19:28
  • did you try print_r instead of echo? it seems odd its not jsut spitting out whats in the DB
    – ATechGuy
    Commented Feb 26, 2014 at 21:56
  • It is very odd. I tried print_r and it was still not returning the fractional seconds
    – Landon
    Commented Feb 26, 2014 at 22:12
  • 1
    for debug i would try using old style mysqli mysqli_query() etc. Just to see if you get the same result.
    – ATechGuy
    Commented Feb 26, 2014 at 22:18
  • 2
    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/…
    – ATechGuy
    Commented Feb 26, 2014 at 22:47
-2

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

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.