Inserting present date and time to MySQL table column

MySQL date field will accept only valid dates and times so proper formatting of input data is a must.

INSERTING PHP Current Date and time to MySQL table using YYYY-mm-dd format and using STR_TO_DATE()


Date Insert 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

SQL Date References Converting string to Date for storing in MySQL table
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    Puneet Verma

    31-12-2009

    Is there a way to provide condition check while using INSERT in sql querry
    John

    03-02-2010

    Sorry, but I can't get it to work. Why don't you include this as part of a practical database example. I've been all over the net just to stick a date field in my database. Nothing works - it can't be that hard to do a practial working example of this.
    Matt McCarty

    25-04-2010

    @John... I have a variable list (one is a date to insert, i.e. $dtCreated=date('Y-m-d'); Then I have my INSERT statement with that variable (using PHP) $query = "INSERT INTO myTable VALUES ('$dtCreated')"; If you have more than one variable, order matters!
    chan

    20-08-2010

    I need aprocedure which generates a table with columns week,start_dt,End_dt. Week should have all 52 weeks,start_dt should every weeks start day ,end_dt should have everyweeks end date. Please help Thanks
    madhavi

    24-04-2014

    I need to alter table with current date and time in mysql
    smo

    14-11-2014

    One practical example using PHP is added to this tutorial.
    sadia

    24-08-2015

    Very helpful
    deepa

    30-05-2016

    insted of php code i want html or javascript code..can u give us>
    Shital Gamaji Patil

    05-01-2017

    how to add from and to date in a single column. or in single column how to add two dates.
    smo1234

    19-02-2017

    You can store dates as string in a single column. You can use any delimiter to separate the dates, while retrieving you can use the same delimiter to separate them. But what is the problem in storing them in two different columns ?

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer