Back to Top

How to use MySQL TimeStamp datatype

MySQL's TimeStamp Type

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?

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

How to declare TimeStamp

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Most Popular Posts

How you can write Custom Queries in Modx

Posted on 5 years ago

Bhumi

Really! PHP touches the 200 Million Mark

Posted on 5 years ago

John Pitt

How to Create a Custom WordPress Widget

Posted on 5 years ago

Bhumi

How to get started with AngularJS

Posted on 2 years ago

Bhumi

The Reader’s Poll – June 2015

Posted on 2 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 3 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 4 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 4 years ago

Bhumi