Adding current date and time records to MySQL table field

Inserting present date and time to a field of mysql table

Adding ( or inserting ) date to a mysql date field is one of the common requirement. Some time we have to add current date and time while inserting the record. With this every time a record is added, the present date and time also added along with the record. Before adding date or date and time we have to format the data in a format acceptable to the mysql date field. MySQL date field will accept only valid dates and times so proper formatting is a must and we will discuss here how a record with a date field and a date and time field is added to a mysql table. We have one date field and one date time field in our table so we will try to format for both the fields. Here are the formats.

For a date field it should be

YYYY-mm-dd

Example: 2005-12-26

For a date and time field it should be

YYYY-mm-dd H:i:s

Example: 2005-12-26 23:50:30

So with this here is one simple SQL query to add a record with a date and date & time field.

INSERT INTO dt_tb (dt,dt2) VALUES ('2004-05-05 23:56:25', '2005-06-12');

PHP date function to changing date formats from one type to other

Adding present ( current ) date and time while inserting a record

Now let us try to add default date and time to the table. That is when ever a record is to be added, it will insert present date and time to the table. So we will format the present date and then present date with time. Let us take two variables $dt1 ( storing the present date value ) and $dt2 ( storing the present date and time ).

$dt1=date("Y-m-d");

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

Now our sql query with these two date values will be like this

INSERT INTO dt_tb(dt,dt2) VALUES ('$dt2', '$dt1');

PHP Script to add date and time to MySQL table

You need to connect to database by using config.php file. The code for config.php file is here.

<?Php
require "config.php"; // Database Connection 
$dt1=date("Y-m-d");
$dt2=date("Y-m-d H:i:s");

$sql="INSERT INTO dt_tb(dt,dt2) VALUES ('$dt2', '$dt1')";
$sql=$dbo->prepare($sql);
if($sql->execute()){
echo " Date stored in table  ";
}
else{
echo " Not able to add record  ";
print_r($sql->errorInfo()); 
}

?>

To create your table here is the sql dump.

CREATE TABLE IF NOT EXISTS `dt_tb` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dt2` date NOT NULL DEFAULT '0000-00-00',
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `id_2` (`id`),
  UNIQUE KEY `id_3` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

str_to_date() function can be used to converting string data to date & time format to insert in mysql table

PHP PDO insert command is used with SQL to store data in date fields

Further Improvements

However this way we are adding date and time to a field through a sql query. First we are calculating the current date and time value by using PHP and then storing that value in a field.

MySQL can automatically insert the current date and time value to a field once the record is added. Not only that it can store the last updated time of the record automatically with out using any specific sql query. We can learn the above two points on how automatically MySQL stores last record insertion / updating date and time in a DATETIME field
.

This article is written by plus2net.com team.

plus2net.com

▼ More on Date & Time functions in SQL