How to insert the current date into a DATETIME field in a MySQL database using PHP

This is a simple but effective tip that I picked up this week.

In the past, if I wanted to insert the current date into a table in a MySQL database from within a PHP script, I would always do something like this:

<?php 
  $current_date = date("Y-m-d H:i:s");
  ...
  $query = "INSERT INTO guestbook SET date = '$current_date'";
  $sql = mysql_query($query) or die(mysql_error());
?> 

Then it was pointed out to me, that this is in fact an arse-backwards way of doing things, as the variable $current_date will enter MySQL as a string, which MySQL will have to parse back into a DATETIME type.

A much better / faster / cleaner way to do things is using CURRENT_TIMESTAMP, which is a built-in MySQL function

<?php 
  $query = "INSERT INTO guestbook SET date = CURRENT_TIMESTAMP";
  $sql = mysql_query($query) or die(mysql_error());
?> 

You can take this even further by constructing the date field in your database table thus: 

date TIMESTAMP NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Which means that it is automatically populated with the current date, the minute a record is created or updated.

This post currently has 7 responses

  1. PETER MUNUO says:

    how to insert date in each column of a table for every month?

  2. sigmato says:

    Great one, Can we use the default current timestamp option available in php myadmin panel?

  3. how do I apply the EST timezone to something like this:

    <input type="hidden" name="hiddenField" id="hiddenField" value=<?php $query = "UPDATE ApparatusTrouble SET _submitted_ = CURRENT_TIMESTAMP ";
    • admin says:

      Don’t do this. You will open yourself to SQL injection attacks.

      You can alter the timezone to EST like so:

      ALTER SESSION SET TIME_ZONE = 'EST';
  4. xyz says:

    hi….i have a doubt in how to store the given date into the database instead of giving the current date…???

    Thanks in advance…:-)

    • admin says:

      Hi,

      Something like this:

      $date = preg_replace("([^0-9/])", "", $_POST['date']);
      $sql = "INSERT INTO table(date) VALUES (:date)";
      $stmt = $pdo->prepare($sql);
      $stmt->bindParam(':date', $date, PDO::PARAM_STR); 
      $stmt->execute();

Comments are closed!

I'm on Twitter:

Categories