MySQL TIMESTAMP and its usage

MySQL TIMESTAMP and its usage

MySQL TIMESTAMP is a very important section to be dealt with while coding web applications. In one of my articles – Working with MySQL date types, I have clearly explained how important the date factor is in case of PHP web applications. Web masters prefer DATETIME over TIMESTAMP for certain reasons. However, MySQL TIMESTAMP often proves to be handy under certain conditions.

MySQL TIMESTAMP and DATETIME

One of the biggest advantages of using TIMESTAMP is the ON UPDATE feature. Since the release of MySQL 5.6.4, DATETIME fields also have the facility to be assigned an ON UPDATE CURRENT TIMESTAMP feature. This feature enables the database field to take in the current date and time values from the server. The field does not remain NULL, instead each time an INSERT or UPDATE is done without values for this field, it automatically feeds a value for itself.

Having said that this feature works on both MySQL TIMESTAMP and DATETIME attributes, there is one advantage at this point for the TIMESTAMP data type. The ON UPDATE feature works for TIMESTAMP and DATETIME if applied manually, but for the first TIMESTAMP field in a table this feature works by default. So, if you have one TIMESTAMP field or multiple fields, the first field would get a default ON UPDATE feature. This default value can be removed in two ways. Either you can set a default value for the field, or you can set the NULL attribute for this field.

You can see the full documentation here. Some other differences also exist between MySQL TIMESTAMP and DATETIME. They have some advantages and drawbacks. Take a look at my article MySQL DATETIME versus TIMESTAMP to know more about their differences and how they could affect coding techniques.

MySQL TIMESTAMP and PHP timestamp

PHP timestamps have a different form. They are expressed in the form as the total number of seconds that has passed since Jan 1970 until now. This is somewhat close to MySQL TIMESTAMP because the lower mark in the latter’s case is also 1970 January. However, PHP’s timestamp is returned as integer value. It must be converted to the correct MySQL accepted format.

If you try to access value returned from MySQL in PHP you might face some common issues. One of the prime issues is that you might try to use the MySQL TIMESTAMP data value from the database directly as a PHP timestamp value for calculations. You will face a problem. Your code would not work. This is obvious because MySQL returns a date object and PHP’s timestamp value is integer. So, any calculations in PHP would go waste. What you need to do is use the function strtotime() in PHP. Just supply the MySQL TIMESTAMP value as parameters to this function and the return is a PHP timestamp.

MySQL value returned can be converted to a user friendly readable format also. In PHP, just change it to timestamp using strtotime() and then compose a date in a user friendly format using the date() function in PHP. Check my article PHP-MySQL date format for more help.

Comments are closed.