How do i insert the current date and time to DATETIME data type column in MySQL table?

Question

How do i insert the value of date('d-m-Y h:i:s') in the clock column with DATETIME data type in MySQL table?

date_default_timezone_set("Asia/Kuala_Lumpur");
$date=date('d-m-Y h:i:s');

    $sql=mysqli_query($connection,"INSERT INTO time (clock)VALUES($date)");

i keep getting this error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '11:22:03)' at line 1

date fuction returns a formatted date string, please post insert code — Manzolo, Sep 08 '19 at 13:05
php date has no sa as formatting parameter https://www.php.net/manual/de/function.date.php — nbk, Sep 08 '19 at 13:13
why not using `$date=date('Y-m-d h:i:s');` ? and use format to show in am/pm when you need it? — Manzolo, Sep 08 '19 at 13:20
My sql wants its datetime data in only special formats, when you retrieve it, format as you like - — nbk, Sep 08 '19 at 13:22
See: https://stackoverflow.com/questions/15318368/mysqli-or-die-does-it-have-to-die — Dharman, Sep 08 '19 at 13:42
Also see: [when-to-use-single-quotes-double-quotes-and-backticks-in-mysql](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql). Better yet start using [prepared statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Paul Spiegel, Sep 08 '19 at 15:16
If you have errors, you should post them in your question. Don't let us guess. — Paul Spiegel, Sep 08 '19 at 15:17
@PaulSpiegel the data type for the `clock` column is DATETIME. — ELVIN MALIH FKI, Sep 08 '19 at 15:41

score 2 · Answer 1 · answered Sep 08 '19 at 13:24

2

According to the MySQL documentation, 11.3.1 The DATE, DATETIME, and TIMESTAMP Types, the DATETIME type only supports the format YYYY-MM-DD hh:mm:ss, which is equivalent to PHP formats.

Y-m-d h:i:s for 12 hour format.
Y-m-d H:i:s for 24 hour format.

Adding PM or AM will not work on MySQL.

answered Sep 08 '19 at 13:24

SilasKenneth

21
4

i tried this `$date=date('Y-m-d h:i:s');` and this error came out `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '09:59:30)' at line 1` – ELVIN MALIH FKI Sep 08 '19 at 14:00

score 0 · Answer 2 · answered Sep 08 '19 at 15:51

You have multiple issues in your code:

MySQL's DATETIME data type expects the inserted values to be of the format %Y-%m-%d %H:%i:%s (or Y-m-d H:i:s in PHP).
DATETIME values must be enclosed in (single) quotes. (This is the reason for your syntax error.)

Your code should be:

date_default_timezone_set("Asia/Kuala_Lumpur");
$date = date('Y-m-d H:i:s');

$sql = $connection->query("INSERT INTO time (clock) VALUES ('{$date}')");

However - Even if the value of $date doesn't come from user input, and contains no special SQL characters, it creates "code smell", which is having variables being interpolated in SQL queries. You should start now using parameterized prepared statements. Which would be:

$stmt = $connection->prepare("INSERT INTO time (clock) VALUES (?)");
$stmt->bind_param('s', $date);
$stmt->execute();

wow, this works great. it solved the error, Thank you so much sir. By the way, what about other values? do i need to type like `'{$date}'` as well or its just for DATETIME column? — ELVIN MALIH FKI, Sep 08 '19 at 16:10
You could also use `'$date'` in this case. Using `{$variable}` can be considered "good practice". In some cases you need it. Eg.: `"Time: {$value}ms"` works fine. But `"Time: $valuems"` will raise an error: "*unknown variable '$valuems'*". So I tend to always use `{}`, just to be consequent and avoid bugs. — Paul Spiegel, Sep 08 '19 at 16:20

How do i insert the current date and time to DATETIME data type column in MySQL table?

2 Answers2