MySQL DATETIME explained

MySQL DATETIME explained

If you just stepped into web development niche, you might not be well aware of MySQL DATETIME. MySQL is an open source language for database management. Normally, web developers make use of MySQL with web development scripting languages. In this tutorial, we will see how MySQL can be implemented in PHP.

Database access is a prime part of web applications. PHP scripts have some date and time data-type. However, in order to store this data you must be very sure as to which MySQL data-type you should choose. Future accesses to these stored database values must be very fluent and trouble free. Otherwise, you web application or web portal might face issues while retrieving these data in multiple instances.

MySQL DATETIME – basics

DATETIME, as the name suggests, contains both date and time components. DATETIME is used when you want to store a particular date and time for future use. For example, you have a shop online. Your customers regularly buy items through the online cart and they have a generated receipt for every buy. Your work as an administrator is to monitor these sales and receive reports. Even if you do not generate reports, you must keep track of the date and time of each sale. Keeping track and printing dates and time of product sale are a prime part of a shop.

MySQL DATETIME offers date and time to be stored in the format “2013-08-11 14:21:12.999999”. This representation goes as “YYYY-MM-DD hh:mm:ss”. The 6 digits after the seconds’ digits are the fractions of seconds for that current time. The format must be maintained whenever you insert DATETIME value in MySQL. Hour representation is in 24-hours format.

Other data types of MySQL date and time include DATE and TIMESTAMP. You can learn more about them in my article Working with MySQL date type.

MySQL DATETIME with PHP

PHP is a server side scripting language. As I mentioned earlier, that a scripting language detects current time and sends it to the database for storage. Now, PHP time function returns the current timestamp for your usage. Whether you store the current time or some other time, PHP has some functions to create the DATE object. Usually, we use the date() function in PHP to create a DATETIME object. The date() function looks for the format in which the date is to be created and then accepts a time component in PHP timestamp format.

In case we need to make a MySQL DATETIME value to be created in PHP, we must keep certain things in mind. First of all, the format in which the DATETIME will be created. MySQL DATETIME structure must be provided as the source for the PHP date() function format. So, “Y-m-d h:i:s” is provided as the format for the date() function. Still not clear enough. I have a dedicated tutorial on MySQL DATE and PHP. Read PHP-MySQL date format.

MYSQL DATETIME and TIMESTAMP      

With the introduction of MySQL 5.6.4, MySQL DATETIME and TIMESTAMP types have been given the capacity to take in Default and Update values automatically. So, even if a value is not provided during the query, the fields automatically take in the current date and time value. For a MySQL insert statement or update statement, these data types take automatic default values if specified as ON UPDATE CURRENT_TIMESTAMP.

MYSQL DATETIME and TIMESTAMP have something in common. Both of their formats are same. In fact, the fractional seconds count is also applicable for both. So, what difference do they have? Well, there are some minute yet important differences in the two data types. I have portrayed their duel in my article MySQL DATETIME versus TIMESTAMP.  One of the most striking differences is that DATETIME has a larger range than TIMESTAMP. For example, TIMESTAMP is until the year 1970 in the lower margin while DATETIME year values can be as low as until 1000.

Comments are closed.