How to insert TIMESTAMP into my MySQL table?

Question

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";
    }
    
?>

**Note:** `mysql_*` functions are deprecated since PHP 5.5 and removed in PHP 7, use `mysqli_*` functions or PDO instead. — Panda, Jun 03 '16 at 14:17
See [this answer](http://stackoverflow.com/a/12120457/3101532) regarding formatting dates for MySQL — shamsup, Jun 03 '16 at 14:22

shamsup · Accepted Answer · 2017-06-30T18:54:27.670

55

In addition to checking your table setup to confirm that the field is set to NOT NULL with a default of CURRENT_TIMESTAMP, you can insert date/time values from PHP by writing them in a string format compatible with MySQL.

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

This will give you the current date and time in a string format that you can insert into MySQL.

edited Jun 30 '17 at 18:54

answered Jun 03 '16 at 14:28

shamsup

1,952
14
18

score 28 · Answer 2 · answered Jun 03 '16 at 14:32

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");

score 10 · Answer 3 · edited Jun 08 '20 at 13:37

10

$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()

edited Jun 08 '20 at 13:37

Community

1
1

answered Jun 03 '16 at 14:38

Hamza Zafeer

2,360
13
30
42

score 3 · Answer 4 · answered Jun 27 '19 at 04:38

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.

score 3 · Answer 5 · answered Aug 27 '20 at 16:10

3

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)

answered Aug 27 '20 at 16:10

Samuel Dauzon

10,744
13
61
94

score 2 · Answer 6 · answered Jun 28 '19 at 09:06

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)

score 1 · Answer 7 · edited Apr 14 '21 at 14:18

1

You can try on CURRENT_TIMESTAMP() function while passing DML query.

edited Apr 14 '21 at 14:18

Kevin M. Mansour

2,915
6
18
35

answered Apr 14 '21 at 03:57

Odeonus.com

11
2

score 0 · Answer 8 · answered Jun 03 '16 at 14:51

0

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

answered Jun 03 '16 at 14:51

MikeGsus

170
1
2
12

How to insert TIMESTAMP into my MySQL table?

8 Answers8