About Me

Friday, 13 April 2012

MySQL Date - Formats

05:03  SecurityVoid  No comments

MySQL Date - Formats

MySQL comes with several data types for storing a date in its database system: DATETIME, DATE, TIMESTAMP, and YEAR. This lesson will show you the proper formats of each type, show their related MySQL functions, and give an INSERT example of each.


These date types are chosen for a column when you create a new table in MySQL. Often the most difficult part of using dates in MySQL is to be sure the format of the date you are trying to store matches the format of your table's date column. If you haven't already, try to create a new MySQL table with the date types we mentioned above.
We have assembled a "date playground" MySQL table that can be used to follow along with this lesson. dateplayground.sql. Also, the following acronyms are used in this lesson:
  • Y - year segment
  • M - month segment
  • D - day segment
  • H - hour segment
  • m - minute segment, note the lower case
  • S - sec segment

MySQL Date - DATE

The default way to store a date in MySQL is with the type DATE. Below is the proper format of a DATE.
  • YYYY-MM-DD
  • Date Range: 1000-01-01 to 9999-12-31
If you try to enter a date in a format other than the Year-Month-Day format then it might work, but it won't be storing them as you expect.
To insert the current date into your table you can use MySQL's built-in function CURDATE() in your query. Below we have created 2 dates, one manually and one using CURDATE().

PHP & MySQL Code:

<?php
//This assumes you have already created the 'dateplayground' table
//Connect to DB
$query_manual = "INSERT INTO dateplayground (dp_name, dp_date)
 VALUES ('DATE: Manual Date', '2020-2-14')";
$query_auto = "INSERT INTO dateplayground (dp_name, dp_date)
 VALUE ('DATE: Auto CURDATE()', CURDATE() )";

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

MySQL Date - YEAR

If you just need to store the year of an event, MySQL also has a date type just for that. YEAR's format is simply:
  • YYYY
  • Date Range: 1901 to 2155
It should be noted that the range of years that can be stored are from 1901 to 2155. If you need to store years outside that range then use DATE instead of YEAR.
Below we have created another manual and automatic example to show off YEAR's use. We have used CURDATE() again, even though it provides a lot more information than YEAR requires. All the date information, besides the year, is just ignored by YEAR.

PHP & MySQL Code:

<?php
$query_manual = "INSERT INTO dateplayground (dp_name, dp_year)
 VALUES ('YEAR: Manual Year', '2011')";
$query_auto = "INSERT INTO dateplayground (dp_name, dp_year)
 VALUE ('YEAR: Auto CURDATE()', CURDATE() )";

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

MySQL Date - DATETIME

DATETIME actually stores both the current date and time, meaning it has the ability to store the year, month, day, hour, minute, and second inside it. DATETIME's format is:
  • YYYY-MM-DD HH:mm:SS
  • Date Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59
The hyphen and the colon are the standard character to separate a date and time respectively, but MySQL allows for you to choose your own delimiters if you wish.
With DATETIME you can choose to store the date or the time and date together, but you cannot store just the time.
In our example below we have manually stored a complete DATETIME and also used three different MySQL functions: CURDATE(), CURTIME(), and NOW().

PHP & MySQL Code:

<?php
$query_manual = "INSERT INTO dateplayground (dp_name, dp_datetime)
 VALUES ('DATETIME: Manual DateTime', '1776-7-4 04:13:54')";
$query_autodate = "INSERT INTO dateplayground (dp_name, dp_datetime)
 VALUE ('DATETIME: Auto CURDATE()', CURDATE() )";
$query_autotime = "INSERT INTO dateplayground (dp_name, dp_datetime)
 VALUE ('DATETIME: Auto CURTIME()', CURTIME() )";  //This will fail
$query_autonow = "INSERT INTO dateplayground (dp_name, dp_datetime)
 VALUE ('DATETIME: Auto NOW()', NOW() )";

mysql_query($query_manual) or die(mysql_error());
mysql_query($query_autodate) or die(mysql_error());
mysql_query($query_autotime) or die(mysql_error());
mysql_query($query_autonow) or die(mysql_error());
?>

MySQL Date - TIMESTAMP

TIMESTAMP is a format popularized by the *NIX operating systems that stores the amount of time that has passed since January 1, 1970. If you want more versatility than this date type provides, remember to try DATETIME.
  • YYYY-MM-DD HH:mm:SS
  • Date Range: 1970-01-01 00:00:00 to 2037-12-31 23:59:59
The big difference between DATETIME and TIMESTAMP is the date ranges that can be stored. Below we have purposely entered an erroneous date, manually, so you can see what happens when you enter a date that is outside the boundaries of a this type.

PHP & MySQL Code:

<?php
//This will fail
$query_manual = "INSERT INTO dateplayground (dp_name, dp_timestamp)
 VALUES ('TIMESTAMP: Manual Timestamp', '1776-7-4 04:13:54')"; 
$query_autodate = "INSERT INTO dateplayground (dp_name, dp_timestamp)
 VALUE ('TIMESTAMP: Auto CURDATE()', CURDATE() )";
//This will fail
$query_autotime = "INSERT INTO dateplayground (dp_name, dp_timestamp)
 VALUE ('TIMESTAMP: Auto CURTIME()', CURTIME() )"; 
$query_autonow = "INSERT INTO dateplayground (dp_name, dp_timestamp)
 VALUE ('TIMESTAMP: Auto NOW()', NOW() )";

mysql_query($query_manual) or die(mysql_error());
mysql_query($query_autodate) or die(mysql_error());
mysql_query($query_autotime) or die(mysql_error());
mysql_query($query_autonow) or die(mysql_error());
?>

Viewing dateplayground in PHP

Below is a quick script that will spit out the MySQL table dateplayground in HTML.

PHP & MySQL Code:

<?php
$query = "SELECT * FROM dateplayground";
$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 dateplayground.sql Display:

dp_namedp_yeardp_datedp_datetimedp_timestamp
DATE: Manual Date00002020-02-140000-00-00 00:00:000000-00-00 00:00:00
DATE: Auto CURDATE()00002006-09-190000-00-00 00:00:000000-00-00 00:00:00
YEAR: Manual Year20110000-00-000000-00-00 00:00:000000-00-00 00:00:00
YEAR: Auto CURDATE()20060000-00-000000-00-00 00:00:000000-00-00 00:00:00
DATETIME: Manual DateTime00000000-00-001776-07-04 04:13:540000-00-00 00:00:00
DATETIME: Auto CURDATE()00000000-00-002006-09-19 00:00:000000-00-00 00:00:00
DATETIME: Auto CURTIME()00000000-00-000000-00-00 00:00:000000-00-00 00:00:00
DATETIME: Auto NOW()00000000-00-002006-09-19 16:56:560000-00-00 00:00:00
TIMESTAMP: Manual Timestamp00000000-00-000000-00-00 00:00:000000-00-00 00:00:00
TIMESTAMP: Auto CURDATE()00000000-00-000000-00-00 00:00:002006-09-19 00:00:00
TIMESTAMP: Auto CURTIME()00000000-00-000000-00-00 00:00:000000-00-00 00:00:00
TIMESTAMP: Auto NOW()00000000-00-000000-00-00 00:00:002006-09-19 16:56:56
Notice that the rows DATETIME: Auto CURTIME(), TIMESTAMP: Manual Timestamp, and TIMESTAMP: Auto CURTIME() have all zeros. This is because they were the INSERTs that were erroneous. When you enter dates that are out of the range or in the wrong format for a given date type, MySQL will often just enter in the default value of all zeros.

Posted in:

0 comments:

Post a Comment