Question 20370 in Mysql Question posted in
The official MySQL documentation can be found here.

PHP, how to format DateTimeInterface for mysql?

When I write a php DateTimeInterface to mysql timestamp field, I always do:

$datetime = (new DateTime())->format('Y-m-d H:i:s');
$sql = "... when_inserted = '$datetime'";

I know that DateTimeInterface::format supports Full Date/Time formatting (c, r), but both of them produce a warning on mysql

c: 2023-03-22T11:33:40+01:00

mysql warning: Data truncated for column 'when_inserted' at row 1

r: Wed, 22 Mar 2023 11:33:40 +0100

mysql warning: Data truncated for column 'when_inserted' at row 1

Is there a shorter way or I must format the date with the boring and verbose Y-m-d H:i:s?

Tags:

3

Answers


  1. 0

    Using the format 'Y-m-d H:i:s' is correct for MySQL. Unfortunately, there is no built-in method or constant to get this format.

    To optimize your workflow, you could define the format as a constant yourself and use it throughout your project.

    Login or Signup to reply.
  2. 0

    Use the __toString() magic method if you don’t wish to write those format statements everytime and wish to inject a string representation of an object automatically when the object gets used in a string context.

    To do this, create your own Custom class and make it a child by extending the DateTime class and implement __toString() method in it and use it in your sql variable like below:

    Snippet:

    <?php

class CustomDateTime extends DateTime{
    public function __toString(){
        return $this->format('Y-m-d H:i:s');
    }
}

$datetime = new CustomDateTime();
$sql = "... when_inserted = '$datetime'";

echo $sql;

    Online Demo

    Login or Signup to reply.
  3. 0

    The Mysql NOW() function may be your solution.

    $sql = "... when_inserted = NOW()";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search