Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert TIMESTAMP into my MySQL table?

Tags:

php

mysql

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";     }      ?> 
like image 869
johnbumble Avatar asked Jun 03 '16 14:06

johnbumble


People also ask

How do I add a timestamp to a MySQL table?

Here is the SQL you can use to add the column in: ALTER TABLE `table1` ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; This adds a column called 'lastUpdated' with a default value of the current date/time.

How is timestamp stored in MySQL?

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME, which is stored “as is”.) By default, the current time zone for each connection is the server's time.

How do I display a timestamp in SQL?

The CURRENT_TIMESTAMP function returns the current date and time, in a 'YYYY-MM-DD hh:mm:ss. mmm' format. Tip: Also look at the GETDATE() function.


2 Answers

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.

like image 169
shamsup Avatar answered Sep 22 '22 02:09

shamsup


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"); 
like image 20
SonDang Avatar answered Sep 23 '22 02:09

SonDang