Dates in PHP and MySQL using DATETIME fields

This post covers the basic date & time operations which most of us are using in PHP and MySQL. The manipulation of the date and date & time fields in PHP is difficult because PHP is not very rich in date & time functions. This is why in most situations we have to write additional functions to subtract dates, calculate time intervals, calculate the difference between datetime elements and the list goes on.

Unlike PHP, MySQL is very rich in functions and supports for datetime elements. Its very good that we can always find what we need but this comes with a price: having so many options make the decision difficult.

In PHP there are 4 functions which can be used to handle most of the datetime operations:

  • time() – returns the current time measured in the number of seconds since January 1 1970 00:00:00 GMT.
  • mktime($hour,$minute,$second,$month,$day,$year) – returns the time specified through arguments measured in the number of seconds since January 1 1970 00:00:00 GMT.
  • date($format,[$timestamp]) – transforms from a time variable produced by time or mktime into a formated string
  • strtotime – transforms from a string to a time variable.

Lets start this tutorial creating an mysql table containing a DATETIME field:

CREATE TABLE item(id integer not null auto_increment
	, name varchar(250)
	, created_on DATETIME
	, primary key(id))

Then we write the PHP code which builds the INSERT query with the current date:

// insert current date
$current_date = date ("Y-m-d H:i:s");
$query = "INSERT INTO item(name, created_on), VALUES('date example',".$current_date.")";
//... run query

Now let’s display all the results older that 30 days;

//retrieve items older than 30 days
$filter_date = date ("Y-m-d 00:00:00", time() - 86400 * 30);
$query = "SELECT * FROM item WHERE created_on < '".$filter_date."'";

You probably noticed how we obtain the date 30 days ago. We take the current time() which returns the time in seconds and subtracts 30 days expressed in seconds(a day has 86400 seconds) and then we convert the time to a string using the PHP function "date()".

We can use the same technique to subtract dates:

//date format: yyyy-mm-dd 
$days = diff_days("2009-01-02", "2009-02-01"); 
echo round($days) . " days"; 
 
//returns the difference between 2 dates in days as a float
function diff_days($begin_date, $end_date) 
{ 
	return (strtotime($end_date) - strtotime($begin_date)) / 86400; 
}

//returns the difference between 2 dates in hours as a float
function diff_hours($begin_date, $end_date) 
{ 
	return (strtotime($end_date) - strtotime($begin_date)) / 3600; 
}

//returns the difference between 2 dates in minutes as a float
function diff_hours($begin_date, $end_date) 
{ 
	return (strtotime($end_date) - strtotime($begin_date)) / 60; 
}

//returns the difference between 2 dates in seconds
function diff_hours($begin_date, $end_date) 
{ 
	return (strtotime($end_date) - strtotime($begin_date)); 
}

Subtracting Months or Years from Dates

The previous method can be used to subtract days, hours or seconds from date time components in php. The difficult part comes when you have to subtract Months or Years, because the duration of a month or year is not fixed. The months can be 28-31 days and an year can have 365 or 366 days. For subtracting moths from date variable a workaround can be used:

$current_time = time(); // read the current time
$two_months_before = strtotime ( '-2 month' , $current_time) ; // 2 months ago
echo date( Y-m-d H:i:s , $two_months_before );

$current_time = time(); // read the current time
$two_years_before = strtotime ( '-2 year' , $current_time) ; // 2 months ago
echo date( Y-m-d H:i:s , $two_years_before );

The same method can be used as well to subtract days and weeks from a datetime variable in php:

$current_time = time(); // read the current time
$two_days_before = strtotime ( '-2 day' , $current_time) ; // 2 days ago
echo date( Y-m-d H:i:s , $two_days_before );

$current_time = time(); // read the current time
$two_weeks_before = strtotime ( '-2 week' , $current_time) ; // 2 weeks ago
echo date( Y-m-d H:i:s , $two_weeks_before );
Did you enjoy this tutorial? Be sure to subscribe to the our RSS feed not to miss our new tutorials!
... or make it popular on

2 Comments

  1. I have to say, I have been struggling with dateTime stamps, storing it in a database, subtracting times etc… This article has helped me a lot to understand this stuff.
    Thanks
    Ben

  2. Nice, Explanation, but still am confused, i have a variable $a which has the date data in yyyy/mm/dd format, what is the php function to convert it to a unix timestamp

Leave a Comment.