MySQL date and time DataTypes Overview: DATE, TIME, DATETIME, TIMESTAMP, YEAR & Zero Values #Part2.1

Swashata3
This entry is part 2 of 5 in the series MySQL DataTypes
wp-content/uploads/2012/06/mysql-datatype-series-logo.png
MySQL DataTypes

In our previous tutorial we have talked about several MySQL data types like CHAR, VARCHAR, INT, TEXT etc… Today we shall discuss the very important data type of MySQL tables. It is Date and Time. There are various forms of date-time data types. It becomes very much important to learn each of them as date and time are the unique identity for each of your records.

MySQL date and time datatypes

In this Part of Data Types (2.1) we shall see the available date time data available on MySQL and how to use them. After reading this tutorial you will learn:

  • How to make a table with date-time datatypes.
  • How to work with different types of date-time datatype and enter values into them;
  • The range of available datatypes and their Zero values;

So let us begin…

Acronyms used:

We have used the following acronyms in our tutorial…

  • Y : Year segment
  • M : Month segment
  • D : Date segment
  • H : Hour segment
  • m : Minute segment (In lowercase)
  • S : Second segment

So a notation of YYYY-MM-DD HH:mm:SS means the date is written in YEAR(4 digit)-Month(2 digit)-Date(2 digit) format and the time in Hour(2 digit):Minute(2 digit):Second(2 digit) manner.

MySQL date-time datatype: DATE –

DATE as the name suggests is a date storing datatype. Following are its details:

Format:

YYYY-MM-DD

Example:

2010-01-15 is the format of today’s date.

Range:

The date ranges from 1000-01-01 to 9999-12-31.

Notes and usage guide:

You can only store date with datatype. You can not store time with this. If you have entered a value which does not resembles its format then it will put its Zero values. Below is a manual date insertion code:

INSERT INTO date_time_table (date)
VALUES (‘2010-04-12’);
This will insert the date 2010-04-12  to the database named date_time_table into the column named date. Obviously we have set the datatype of the “date” column as “DATE”.

MySQL data-type DATE

Click on the Picture to Enlarge

More on setting datatypes coming at the end of this tutorial. Note that the input data can be either string or number (when used along with PHP)

MySQL date-time datatype: TIME –

This is just the TIME. It stores the time in a specific format. Details are below:

Format:

HH:mm:SS

Example:

10:34:35 means that the time is 10 hours 34 minutes and 35 seconds.

Range:

It ranges from ‘-838:59:59’ to ‘838:59:59’

Notes and Usage Guide:

You can store time values with this datatype. You can not store date with it. If you have entered something which is not according to the proper format or out of the supported range then it will return to the Zero Value. Below is an example of working with TIME datatype column.

UPDATE date_time_table
SET time=”10:34:35″
WHERE date=”2010-04-12″;

This will update the “time” column of the date_time_table table and set it to “10:34:35” of the row which has a “date” column having value “2010-04-12”

MySQL datatype TIME

Click on the picture to enlarge

The above picture shows the table before and after executing the command.

MySQL date-time datatype: DATETIME –

DATETIME is combination of both DATE and TIME. It has the following specifications:

Format:

YYYY-MM-DD HH:mm:SS

Range:

‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’

Example:

Typically 2010-01-15 10:34:40 means that it is 10 hours 34 mins and 40 sec and the date is 15th Jan, 2010.

Notes and Usage Guide:

Effective when storing both date and time together. Enter the value as Number or String. Following is an example:

UPDATE date_time_table
SET date_time=”2010-04-12 10:34:35″
WHERE date=”2010-04-12″;

MySQL datatype DATETIME Click on the Picture to Enlarge

The above image shows the command on action when entered into the MySQL terminal. Also this will return to the Zero value if the input value is erroneous.

MySQL data-type: TIMESTAMP –

This an advanced version of DATETIME datatype. It has the same format as before. Unlike DATETIME, TIMESTAMP stores the current date and time on the creation of the table and on every update automatically. You do
not need to change its value every time.

It is very much useful to automatically keep date/time records of a table. You can also set its value manually. Following are its details:

Format:

YYYY-MM-DD HH:mm:SS

Range:

The actual and exact range of TIMESTAMP is

‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC

Note that it is NOT ‘1970-01-01 00:00:00’

Note and usage:

Following is the example to set the TIMESTAMP manually.

UPDATE date_time_table
SET time_stamp=”2010-04-12 10:40:40″
WHERE date=”2010-04-12″;

MySQL datatype TIMESTAMP

Click on the Picture to Enlarge

You can also set the current date/time by setting a NULL value to the field. Here is the code:

UPDATE date_time_table
SET time_stamp=NULL
WHERE date=”2010-04-12″;

MySQL datatype TIMESTAMP 2

Click on the Picture to Enlarge

The above two example shows typical usage of MySQL TIMESTAMP datatype.

MySQL date-time datatype: YEAR –

This is for storing YEAR values. It can be either 2 or 4 character length. The default is 4 character. Following are its details:

Format:

YYYY for 4 character year and YY for 2 character.

Range:

  • In 4 digit format the allowable values are 1901 to 2155 and 0000;
  • In 2 digit the value range is 70 to 69, representing years from 1970 to 2069

Note and Usage Guide:

To CREATE/ALTER a table column we use the following command, where we set the datatype accordingly.

ALTER TABLE table_name MODIFY column_name YEAR(N);

OR

CREATE TABLE table_name (column_name YEAR(N), column_name2 datatype);

Here YEAR(N) can be either YEAR(2) or YEAR(4) for 2 or 4 digit year datatype respectively. If only YEAR is used then it will be set as a 4 digit year.

To assign a value to a YEAR column, the following command may be used.

UPDATE date_time_table
SET year=”2010″
WHERE date=”2010-04-12″;

MySQL datatype YEAR

Click on the Picture to Enlarge

It will set the Year column 2010.

Creating a Table with the preferred datatype:

Now I am just going to show you the code which we need to set the columns of a particular table to a particular datatype. Do go through the previous article where we have discussed how to create table with data-types. This is just like before:

CREATE TABLE date_time_table
(
date DATE, time TIME, date_time DATETIME, time_stamp TIMESTAMP, year YEAR
);

creating the date_time_table database Click on the Picture to Enlarge

This will create a table named date_time_table with the following preferences…

Column NAME Column DataType
date DATE
time TIME
date_time DATETIME
time_stamp TIMESTAMP
year YEAR

So the basic command for setting the data type is

CREATE TABLE table_name
(
col1 DATE, col2 TIME, col3 DATETIME, col4 TIMESTAMP, col5 YEAR
);

Where col1, col2… are the names of the columns. It can also be used with ALTER TABLE with the relevant command syntaxes.

Zero Values:

Zero values are inserted into the column when there is an error in the input string. Following are the Zero values of the discussed datatypes:

>

Column DataType Zero Values
DATE 0000-00-00
TIME 00:00:00
DATETIME 0000-00-00 00:00:00
TIMESTAMP 0000-00-00 00:00:00
YEAR 0000

 

Consider the following example:

INSERT INTO date_time_table
(date, time, date_time, year)
VALUES (“2010-01-15”, “13:04:30”, “20100115 13043”, “2010”);

Clearly the value of date_time column, 20100115 13043 is erroneous. It should have been, “2010-01-15 13:04:30“. So the Zero Value of the DATETIME column has been inserted to it. Below is the output of this command…

MySQL datetype Zero Value for datetime Click on the Picture to Enlarge

So that was end of this discussion. I hope this has cleared your concepts on various MySQL date and time datatypes. Do give your feedback on this article.If you have any query feel free to ask here…

You can also check the official MySQL date and time datatype guide here.

Series Navigation

« MySQL data types: CHAR, VARCHAR, INT, TEXT #Part-1Automatically insert Current Date and Time in MySQL table #Part – 2.2 »

3 comments

  1. Pingback: MySQL: Working with date time arithmetic #Part 2.3.1 | InTechgrity

  2. prasanth

    this is very useful for me thanks

  3. Sathya

    i have to give an academic year like 1990-1991 in a single field , so what datatype can i use for this?

Comments are closed.