MySQL Temporal Data Types

Overview

In this tutorial, we will learn about MySQL Temporal Data Types. Temporal data types are data types that refer to date and time. MySQL provides data types for storing different kinds of temporal information.

Temporal data types

Temporal data types that are supported by MySQL are as follows:

TIME
DATE
DATETIME
TIMESTAMP
YEAR

TIME

The TIME data type can express the time of day or duration, and has a range of ‘-838:59:59’ to ‘838:59:59’. MySQL displays TIME values in ‘HH:MM:SS’ format, but TIME columns can be assigned using either strings or numbers.

DATE

The supported range is ‘100001-01’ to ‘9999-12-31’
MySQL retrieves DATE values in ‘YYYY-MM-DD’ format, but DATE columns can be assigned using either strings or numbers. For example, the date January 21,2001 would look like: ‘2001-01-21’

DATETIME

This data type allows date and time combinations. The supported range is ‘1000-01-01’ to ‘9999-12-31 23:59:59’.
MySQL, retrieves DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format.

TIMESTAMP

This data type can be used to express datetime values ranging from ‘1970-01-01 00:00:00’ to partway through the year 2037. The TIMESTAMP returns a string in the format ‘YYYY-MM-DD HH:MM:SS’ with a display width fixed at 19 characters. To obtain the values as a number, add +0 to the timestamp column. A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation.

YEAR

The YEAR data type is assigned using the syntax:

YEAR [(2|4)]

where the year can be in a two-digit o four-digit format.

The default is the four-digit format. In four-digit format, the allowable values are 70 to 69, representing years from 1970 to 2069. MySQL retrieves YEAR values in YYYY format.

The following terms stand for:

YYYY – year
MM – month
DD – day of the month
hh – hour
mm – minute
ss – second

—