如何在我的MySQL表中插入时间戳？

Question

在MySQL表中，我有一个名为date的字段，其类型称为时间戳，默认为CURRENT_TIMESTAMP。但是，如果在MySQL中将字段保留为空白，则会得到一个错误。当我试图插入像time()这样的东西时，我会以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";
    }
    
?>

Answer 1

除了检查表设置以确认字段设置为NOT NULL (默认值为CURRENT_TIMESTAMP )外，还可以通过以与MySQL兼容的字符串格式写入日期/时间值来插入来自MySQL的日期/时间值。

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

这将为您提供可以插入到MySQL中的字符串格式的当前日期和时间。

Answer 2

请尝试CURRENT_TIME()或now()函数

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

或

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

或者您可以尝试使用PHP date函数这里。

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

Answer 3

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

您可以使用此查询。时间戳

记住使用括号CURRENT_TIMESTAMP()