Date and Time is one of the most important and most used data types for web developers. Since time storage and retrieval becomes an integral part, manipulating their values perfectly also becomes an important issue. MySQL date types have three basic components to be used and all three have some unique features to be applied. Before we step into the details, let us just have a look at the basics of the DATE structure.
MySQL date type – why they are used
Suppose you are to create an application system on your website like banking, education or customer supplies system. In order to accomplish all functionalities, you must be well verse with date and time components of MySQL. Suppose you want to store the time at which your use logged on and display it during the next visit. In this case, your visitor’s access time is captured by the backend script and transferred to the database for storage. The time gets stored against the user’s unique id and is displayed on his next visit to the website.
What you need in the database attribute is a TIMESTAMP or a DATETIME type field for storing this value. Once you are sure what you need about the data, you can decide upon the type of MySQL field you require. TIMESTAMP or DATETIME gives you full length of data including the date and time. However, you might just require the DATE of the user’s login time. You can just catch the timestamp in PHP, for example, and feed in the DATE component to the database.
Make sure your date value matches the MySQL syntax. I have detailed that in my article PHP-MySQL date format. Just have a look in case you have any difficulty.
At this point, you would have to decide in between DATE and DATETIME. DATE is the third type of data type that MySQL offers. In this case, when you store only the date part using MySQL DATE type would be perfect.
MYSQL date type – the specifications
In order to get a good grip of MySQL data types, just look into the following paragraphs. I have detailed all minor specifications regarding the three types that MySQL supports.
MySQL date type implements three very simple logics regarding DATE and TIME components. It gives you a DATE type, DATETIME type and TIMESTAMP type. The first one is when you store just a DATE. Let’s say, you store “2013-08-11”. So, this does not contain a time component. Only the year, month and day gets stored in DATE type.
For storing DATE and TIME together, MySQL date type offers another option. The second one – DATETIME; will store your date and time component together. This data stores for example, “2013-08-11 12:51:42.999999”, value that has a date and time punched together. The above format has been introduced to store fractions of seconds along with second value. MySQL 5.6.4 stores fractions of seconds to keep the most precise value if you provide.
The third MySQL date type is the TIMESTAMP. Timestamps are a very common term and you might be well aware of its usage. Timestamps in MySQL are of the same format as DATETIME. For example, “2013-08-11 12:51:42.999999” is a timestamp.
TIMESTAMP and DATETIME both have the same format of data. Both of these data types have fraction seconds included and stored since the introduction in MySQL 5.6.4. However, one important difference lies in the fact that their range is different. This is one marked difference. To know more on that see MySQL TIMESTAMP and its usage and MYSQL DATETIME explained.
Hope this article helps you solve some data type deciding problems. Working with the correct data type is always a good programming practice.