PHP MySQL date

PHP MySQL date

PHP date type is a very useful data-type for programmers. Social networks, banking portals, shopping carts and many more of such websites, use PHP MySQL date for timespan calculations. However, the webmasters do not always use the default time or date format. In this article, I will guide you through the steps of working with both PHP MySQL date format.

PHP MySQL date in web scripting

Web applications often require the use time variables. Programmers often find it very difficult to convert date formats from a particular syntax to the other. One of the most common problems is saving timestamps and time variables from PHP to MySQL. I am sure any web developer would have got stuck at least once with this problem. So, I’ll share what I found out when I was stuck. PHP timestamps are very easy to work with. The ‘time’ function gives the UNIX timestamp for the current moment. Suppose you want to add some minutes or hours or even weeks to this current time, just write it in string format and change it to time. PHP has some powerful inbuilt methods for that.

PHP MySQL date is the pattern in which we transfer a date or time object to the MySQL database from a PHP script. Suppose you write the following code

$timenow = time();
$anhourlater = strtotime(“+1 hour”, time());
$anhourlater_indate = date(“d m Y, h:i s”, $anhourlater);
// the above code adds one hour to the current timestamp.
// so variable anhourlater is the next hour from now. 
// Store it in database…
$query = “INSERT INTO demo_table(thetime) VALUES(‘$anhourlater_indate’)”;

The above piece of code is good until the query. This query would fail to run. The error you get is because of the format I which you compose the date for MySQL. PHP MySQL date should be composed in MySQL default format. Here goes what should be the correct procedure.

$timenow = time();
$anhourlater = strtotime(“+1 hour”, time());
$anhourlater_indate = date(“Y-m-d h:i:s”, $anhourlater);
// the above code adds one hour to the current timestamp.
// so variable anhourlater is the next hour from now. 
// Store it in database…
$query = “INSERT INTO demo_table(thetime) VALUES(‘$anhourlater_indate’)”;

This gives a format like – 2013-08-09 11:40:38. This is the exact format that is used in MySQL DATETIME field. You can find more notes on DATETIME in my article MySQL DATETIME explained.

PHP MySQL date for advanced programmers

PHP date function allows a time to be converted directly to MySQL format any other formats if you want. For programmers who wish to implement the variety formats of date printing, I would suggest they refer to the documentation. The combinations are easy to use and implement. You just need to understand what the syntax does and what you need. Return type of the date function is ‘String’. Here are a few examples

echo date(“Y-m-d h:i s”, $anhourlater);  // format 2013-08-07 11:40 38
echo date(“D j M y”, $anhourlater);  // format Fri 7 Aug 13
echo date(“l j F Y”, $anhourlater);  // format Friday 7 August 2013
echo date(“h:i s A”, $anhourlater);  // format 11:40 38 PM

Another important PHP method is the PHP mktime function. This function lets you create custom timestamp of any particular day you want. Within the given UNIX date range of PHP you are free to mention any date and time of the day. You get that timestamp in return. So, enjoy manipulating and playing with PHP MySQL date.