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?

Prior to MySQL 5.6.4 fractional values weren't supported. There may be no way to do what you want. See [this reference](http://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html) — , Feb 25 '14 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, Feb 25 '14 at 21:37
@Landon Oh well, can that idea, then. Looks like PHP is the culprit. Maybe PHP5.6 will help... — , Feb 25 '14 at 21:54
@Mihai why is that weird when mysql has supported fractional seconds since 5.6.4 — Landon, Feb 25 '14 at 21:55

score 8 · Answer 1 · answered Apr 10 '14 at 14:28

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

score 5 · Answer 2 · edited Jan 26 '18 at 04:49

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

edited Jan 26 '18 at 04:49

ryanm

2,239
21
31

answered Apr 08 '14 at 13:57

Baine Sumpin

142
5

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 Aug 10 '15 at 21:15
1

Small typo for the time part ... %H:%i:%s.%f – Richard A Quadling Jan 16 '17 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 Jan 19 '18 at 22:44

score 3 · Answer 3 · answered Jan 19 '18 at 22:45

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.

answered Jan 19 '18 at 22:45

Brian Leishman

8,155
11
57
93

score 3 · Answer 4 · edited Jun 18 '21 at 13:23

3

This bug was finally fixed in PHP v7.3.0.

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

edited Jun 18 '21 at 13:23

Dharman

30,962
25
85
135

answered Jun 28 '19 at 21:31

Umair Ahmed

2,420
1
21
40

score -2 · Answer 5 · answered Feb 26 '14 at 18:06

-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

answered Feb 26 '14 at 18:06

ATechGuy

1,240
8
13

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 Feb 26 '14 at 19:28
did you try print_r instead of echo? it seems odd its not jsut spitting out whats in the DB – ATechGuy Feb 26 '14 at 21:56
It is very odd. I tried print_r and it was still not returning the fractional seconds – Landon Feb 26 '14 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 Feb 26 '14 at 22:18
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 – Landon Feb 26 '14 at 22:38
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 --> http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-data-structures.html – ATechGuy Feb 26 '14 at 22:47
@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? – Passerby Dec 03 '14 at 04:16

score -2 · Answer 6 · answered Apr 04 '14 at 14:03

-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

answered Apr 04 '14 at 14:03

Jassim Rahma

93
1
3
13

PHP How to return datetime(6) from Mysql?

6 Answers6

Linked

Related