Print datetime with microseconds from DB

Question

I am doing a SQL to my database and then get a datetime with microseconds in return. Now I tried printing it like this:

print date("Y-m-d H:i:s.u",strtotime($order["DateTimeEnteredMicro"]));

But this resulted in 2020-12-12 06:00:39.000000.

I found this anser on stackoverflow: https://stackoverflow.com/a/17909891/10673107

But I can't get his solution to work. I used the following code:

$t = microtime(true);
$micro = date("Y-m-d H:i:s",strtotime($order["DateTimeEnteredMicro"]));
$d = new DateTime( date('Y-m-d H:i:s.'.$micro, $t) );

print $d->format("Y-m-d H:i:s.u");

The error I get is the following:

Fatal error: Uncaught Exception: DateTime::__construct(): Failed to parse time string (2020-12-12 11:42:03.2020-12-12 06:00:39) at position 31 (0): Double time specification

How can I fix this?

What's not clear about that error? You have two complete dates concatenated together: `2020-12-12 11:42:03.2020-12-12 06:00:39`. You're doing something completely different than the linked answer. Also, it would help to know what your expected result is (or, in other words, which exact time is stored in the database). — El_Vanja, Dec 12 '20 at 10:54
No, the first date you say: 2020-12-12 11:42:03 is the datetime of the error.... Or am I thinking wrong? — A. Vreeswijk, Dec 12 '20 at 10:55
`$micro = date("Y-m-d H:i:s",strtotime($order["DateTimeEnteredMicro"]));` - `$micro` is a *complete* date. — El_Vanja, Dec 12 '20 at 10:56
But I dont understand his: `$micro = sprintf("%06d",($t - floor($t)) * 1000000);`. How can I use that for myself? — A. Vreeswijk, Dec 12 '20 at 10:57
Can you please specify the value in the database? I can't help if I'm guessing what you have. — El_Vanja, Dec 12 '20 at 10:58

score 2 · Accepted Answer · answered Dec 12 '20 at 11:13

Your database format is supported in DateTime::__construct(), so you can simply pass it as a parameter:

$date = new DateTime($order["DateTimeEnteredMicro"]);
print $date->format("Y-m-d H:i:s.u");

Note that microseconds work in PHP >= 5.2.2. Timezone issues may arise if MySQL and PHP don't have the same timezones set.

P.S. This is where you were going wrong in your code:

$t has the current time, so later when you use it as the second parameter to date(), it was being used instead of your db value.
$micro = date("Y-m-d H:i:s",strtotime($order["DateTimeEnteredMicro"])); - this made the $micro variable a full date, instead of just the microsecond part.
This didn't make much sense either: date('Y-m-d H:i:s.'.$micro, $t) since here you were trying to append a value to the format.

score 1 · Answer 2 · answered Dec 12 '20 at 11:19

Your mark in the question, date is selected from MySQL DB. I guess the MySQL data storing is the root of problem. As you need store datetime with microsecond you need to use datetime(6) as field type:

CREATE TABLE _table (
  d1 datetime, 
  d2 datetime(6)
);

INSERT INTO _table 
VALUES 
  (
    '2020-12-12 12:12:12.121212', '2020-12-12 12:12:12.121212'
  );

SELECT 
  d1, 
  d2 
FROM 
  _table;

In this case you can retrieve microceconds and display it by your PHP code:

<?php
$query = "SELECT d2 FROM  _table;";
$stmt = $pdo->prepare($query);
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC);

printf('Microsecond time is: %s', $row['d2']);

Result:

Microsecond time is: 2020-12-12 12:12:12.121212

Print datetime with microseconds from DB

2 Answers2