[php] How to insert TIMESTAMP into my MySQL table?

In the MySQL table I have a field called date its type is called timestamp and the default is CURRENT_TIMESTAMP. However, if I leave the field blank in MySQL I get an error. When I try to insert something into it like time() I receive the date as 0000-00-00 00:00:00.

<?php

    $name         = "";
    $email        = "";
    $subject      = "";
    $comments     = "";
    $nameError    = "";
    $emailError   = "";
    $subjectError = "";
    $x            = 5;
    function filterData($data)
    {
        $data = htmlspecialchars($data);
        $data = stripslashes($data);
        return $data;
    }
    $connection = mysql_connect('host', 'user', 'pass');
    if (!$connection) {
        die('Could not connect: ' . mysql_error());
    }
    
    
    $select_database = mysql_select_db("contact");
    
    if (!$select_database) {
        echo "could not select database " . mysql_error();
        
    }
    if ($_SERVER["REQUEST_METHOD"] == "POST") {
        //handles the name 
        $name = filterData($_POST["name"]);
        if (empty($name)) {
            $nameError = "please don't leave the name field blank";
        }
        //handles the email
        $email = filterData($_POST["email"]);
        if (empty($email)) {
            $emailError = "please don't leave the email field blank";
        }
        
        //handles the subject
        $subject = filterData($_POST["subject"]);
        if (empty($subject)) {
            $subjectError = "please don't leave this field blank";
        }
        
        $comments = filterData($_POST["comments"]);
        
    }
    
    $insertation = "INSERT INTO contactinfo (name, email, subject, date, comments)
        VALUES ('$name', '$email', '$subject', '', '$comments')";
    
    $insertationQuery = mysql_query($insertation, $connection);
    
    if (!$insertationQuery) {
        echo "Could not process your information " . mysql_error();
    } else {
        echo "Thank you for submitting the information";
    }
    
?>

This question is related to php mysql

The answer is


Please try CURRENT_TIME() or now() functions

"INSERT INTO contactinfo (name, email, subject, date, comments)
VALUES ('$name', '$email', '$subject', NOW(), '$comments')"

OR

"INSERT INTO contactinfo (name, email, subject, date, comments)
VALUES ('$name', '$email', '$subject', CURRENT_TIME(), '$comments')"

OR you could try with PHP date function here:

$date = date("Y-m-d H:i:s");

The DEFAULT value of a column in MySql is used only if it isn't provided a value for that column. So if you

INSERT INTO contactinfo (name, email, subject, date, comments)
VALUES ('$name', '$email', '$subject', '', '$comments')

You are not using the DEFAULT value for the column date, but you are providing an empty string, so you get an error, because you can't store an empty string in a DATETIME column. The same thing apply if you use NULL, because again NULL is a value. However, if you remove the column from the list of the column you are inserting, MySql will use the DEFAULT value specified for that column (or the data type default one)


You can try wiht TIMESTAMP(curdate(), curtime()) for use the current time.


You do not need to insert the current timestamp manually as MySQL provides this facility to store it automatically. When the MySQL table is created, simply do this:

  • select TIMESTAMP as your column type
  • set the Default value to CURRENT_TIMESTAMP
  • then just insert any rows into the table without inserting any values for the time column

You'll see the current timestamp is automatically inserted when you insert a row. Please see the attached picture. enter image description here


If you have a specific integer timestamp to insert/update, you can use PHP date() function with your timestamp as second arg :

date("Y-m-d H:i:s", $myTimestamp)

$insertation = "INSERT INTO contactinfo (name, email, subject, date, comments)
VALUES ('$name', '$email', '$subject', CURRENT_TIMESTAMP(), '$comments')";

You can use this Query. CURRENT_TIMESTAMP

Remember to use the parenthesis CURRENT_TIMESTAMP()