MySQL Time - Formats

There are three different types of time data types in MySQL: TIME, DATETIME, and TIMESTAMP. If you would like to learn more about DATETIME and TIMESTAMP, then check out our MySQL Date section, as we've covered them there. This lesson will just be covering the basics of using TIME.

MySQL Time - TIME

First you need to create a MySQL table with a TIME type. We have one already created if you want to use it: timeplayground.sql.

The TIME data type can be used to store actual times as well as the amount of time between two points in time (like the time between now and the weekend) that may sometimes be larger than 23 hours. H - Hour; M - Minute; S - Second.

• Standard format: HH:MM:SS
• Extended hour format: HHH:MM:SS
• Time Range: -838:59:50 to 838:59:59

When manually entering a time into MySQL it is highly recommended that you use the exact format show above. MySQL allows for many different ways to enter a time, but they don't always behave as you would expect. Using the standard/extended format we have shown above will help you avoid annoying problems.

Below we have entered 3 manual times into MySQL. The first is done in the recommended format, the second is a shorthand version of the first and the final example is outside the allowed time range.

<?php
//This assumes you have already created the 'dateplayground' table
//Connect to DB
$query_manual1 = "INSERT INTO timeplayground (dp_name, dp_time)
	VALUES ('TIME: Manual Time', '12:10:00')"; //perfectly done
$query_manual2 = "INSERT INTO timeplayground (dp_name, dp_time)
	VALUES ('TIME: Manual Time', '1210')"; // will this shorthand work?
$query_manual3 = "INSERT INTO timeplayground (dp_name, dp_time)
	VALUES ('TIME: Manual Time', '978:31:12')"; //how about this?

mysql_query($query_manual1) or die(mysql_error());
mysql_query($query_manual2) or die(mysql_error());
mysql_query($query_manual3) or die(mysql_error());
?>

MySQL Time - NOW()

To get the current time, use MySQL's built in function NOW(). NOW() contains both the date and time information, but MySQL is smart enough to just use the data needed for TIME.

<?php
$query_auto = "INSERT INTO timeplayground (dp_name, dp_time)
	VALUE ('TIME: Auto NOW()', NOW() )";

mysql_query($query_auto) or die(mysql_error());
?>

MySQL timeplayground.sql Displayed

Below is a small PHP script to spit out a rough version of our timeplayground.sql table.

<?php
$query = "SELECT * FROM timeplayground";
$result = mysql_query($query) or die(mysql_error());

echo "<table border='1'><tr>";
for($i = 0; $i < mysql_num_fields($result); $i++){
	echo "<th>".mysql_field_name($result, $i)."</th>";
}
echo "</tr>";
while($row = mysql_fetch_array($result)){
	echo "<tr>";
	for($i = 0; $i < mysql_num_fields($result); $i++){
		echo "<td>". $row[$i] ."</td>";
	}
	echo "</tr>";
}

echo "</table>";
?>

Finished timeplayground.sql Display:

Our first manual time was handled just fine, but our second one did not. MySQL interpreted 1210 as MM:SS instead of HH:MM as we assumed. This is why it's best to use the formats we've described at the beginning.

The third manual entry was changed from 978:31:12 to 838:59:59, so that it would be within TIME's range.

• Go Back
• Continue

Download Tizag.com's MySQL Book

If you would rather download the PDF of this tutorial, check out our MySQL eBook from the Tizag.com store. You may also be interested in getting the PHP eBook

Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!