HOW TO USE MYSQL TIMESTAMP DATATYPE

Recently I was working in one application and in this I found one field in database is created date for user account and i found that no default value is set for that so i talked with my team member that we can take Timestamp as default field for createddate because it’s a field that contain information about user registration date and time which can automatically insert with TIMESTAMP when insert query is performed in table.

Let’s see about TIMESTAMP datatype in MySQL.It’s newly added from MySQL 4.1.The TIMESTAMP data type is the only way to have MySQL automatically set the time when a row was inserted and/or updated. DATETIME columns can’t set automatically.This type was created to give you a flexible way to store date and time information.

What is TimeStamp?

A TimeStamp field contains a number in the format of YYYYMMDDHHMMSS (such as 20120221105435 for 10:54:35 AM on Feb 21, 2012). It is a common date/time combination in Unix-based systems and user-understandable form so the user can easily understand and manipulate it.

However, PHP provides a date() function that allows us to format a TIMESTAMP to meet your requirement for the date.

How to declare date() function?

1 2 3

date($format, [$intTimestamp])

With the use of date() function, we can get back the information held by TIMESTAMP or in a TIMESTAMP field.

How to declare TimeStamp

1 2 3

column_name TIMESTAMP [DEFAULT default]

The default value for TIMESTAMP auto-initialization is CURRENT_TIMESTAMP.

If you don’t specify a value for a timestamp column when you insert a row, MySQL automatically inserts the current timestamp. This is convenient for storing the time that a user registered with an application or that a purchase was made because you don’t have to deal with getting the current time and storing it so it’s all done automatically.The NOT NULL option isn’t necessary with TIMESTAMP fields.I recommend you read about How to use Triggers in MySQL.

If you use the timestamp data type,it will automatically UPDATE and INSERT to the current time.Usually it depends on the database structure,you can replace datetime with timestamp field to achieve the creation and modification time and this can be controlled more accurately.The Major benefit is,it will automatically update time if any modification done in table.

NOTE:

The timestamp MySQL gave us is a MySQL timestamp and the timestamp PHP’s date() function is a more standard Unix-style timestamp.

Remember that The MySQL TIMESTAMP type and $intTimestamp in date function both are not the same. When we retrieve date with the SELECT query , you can use one of MySQL’s functions( like MySQL’s UNIX_TIMESTAMP function ) to convert the MySQL timestamp to something PHP can handle.

NOTE: You can take more than one TIMESTAMP data type in a table whereas only ONE TIMESTAMP column in a table can be set for Auto-Update or Auto-Initialization because it’s a limitation of MySQL.