How to Insert the Current Date into a DATETIME Field in a MySQL Database using PHP

03 Aug 2012

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.

 dates  mysql  php

This post currently has 7 responses

  1. PETER MUNUO says:
    August 4, 2013 at 7:58 pm

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

  2. sigmato says:
    October 21, 2013 at 11:12 am

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

  3. Michael Zucker says:
    August 20, 2014 at 5:52 pm

    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:
      August 22, 2014 at 10:11 am

      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:
    April 30, 2015 at 12:42 pm

    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:
      May 1, 2015 at 3:53 pm

      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!

Related Posts