Datatypes in MySQL - III | DATE, TIME, DATETIME, TIMESTAMP

DATE data type in MySQL

MySQL provides a DATE data type for storing date values. The format for storing a date value is YYYY-MM-DD. Any format other than this is not acceptable in MySQL. For example - MM-DD-YYYY, this format will not be accepted by MySQL. By using the DATE_FORMAT  function, we can format the date by our wish.

The range of date values is 1000-01-01 to 9999-12-31. To store any other date value which is not in this range, we need to use integer data type and by storing date, month, and year in three different columns.

The two-digit year value can also be used like YY, but we should avoid using this as it is ambiguous. This is because if the year value is from 00 to 69, it is converted to 2000 to 2069 and if the year value lies from 70 to 99, it is converted to 1970 to 1999.

Example of DATE type

Let's create a table named friends.  

CREATE TABLE friends (
  
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
d_o_b DATE NOT NULL
);

It will create a table with three columns.

Now, insert some values in it.

INSERT INTO friends(first_name,last_name,d_o_b)
VALUES('Ashish','Kumar','1998-07-24'),
	  ('Vivek','Kumar','1999-07-31');

After the execution of the above statements, the table will look like this:

DATE functions

There are some DATE functions provided by MySQL which are very useful. They are:

1. CURDATE() - It is used to get the current date of the system.

SELECT CURDATE();

The output of the above statement will be like this:

2. DATE_FORMAT() - It is used to format the date using different date format patterns.

SELECT DATE_FORMAT(CURDATE(), '%d/%m/%y');

Here, the date pattern used is %d/%m/%y. The date will be formatted in this pattern. The output will be like this:

3. NOW() - It is used to get the current date and time.

SELECT NOW();

The output will be like this:

4. SELECT DATE(NOW) - It is used to get only the date part from date and time.

SELCT DATE(NOW());

The output will be like this:

5. DATEDIFF - It is used to get the number of days between two date values.

SELECT DATEDIFF('2021-12-24','2000-06-15') AS no_of_days;

The output will be:

6. DAY() - It is used to get the day value from a DATE.

SELECT DAY('2021-12-24');

The output will be like this:

Similarly, like DAY() we can use MONTH(), QUARTER(), YEAR() to get the month, quarter, and year value respectively from a given date.

7. WEEKDAY() - It is used to get the weekday of a given date value.

SELECT WEEKDAY('2021-12-24');

The output of the above will be:

Similarly like WEEKDAY(), we can use WEEK(), WEEKOFYEAR() to get the week number and calendar week respectively of a given date value.

TIME data type in MySQL

The time data type is used for storing time value. The format of display of time is ‘HH:MM:SS’.

The syntax for defining a column with TIME datatype is:

column_name TIME;

In the above statement, the data type of the column named column_name is TIME.

The time value can have fractional seconds part and its precision can be up to microseconds.

We can specify the digits of fractional seconds using this.

column_name TIME(N);

Here, N represents the number of digits in fractional seconds.

Example of TIME data type:

Let's create a table having TIME data type for a few columns.

CREATE TABLE match_details (


match_name VARCHAR(100),

start_time TIME
);

A table named match_details is created using the above statements.

Now, let's insert some rows in it.

INSERT INTO match_details(match_name, start_time)
VALUES ('INDvsAUS','09:30:00');
       ('INDvsSA','02:30:00');

The table will now look like this:

MySQL also allows the time format without delimiter (:). For HH:MM:SS, HHMMSS is also acceptable.

Let's insert a row using this format.

INSERT INTO match_details(match_name, start_time)
VALUES ('INDvsPAK', '073000');

Now, the table will look like this:

There are some TIME functions provided by MySQL which are very useful. They are:

1. CURRENT_TIME() - It is used to get the current time value.

SELECT CURRENT_TIME();

The output of the above will be like this:

2. TIMEDIFF() - It is used to get the difference between two time values.

SELECT TIMEDIFF('10:00:00','09:00:00');

The output will be:

3. TIME_FORMAT - It is used to format the time value using different patterns.

SELECT TIME_FORMAT(CURRENT_TIME(), '%h:%i %p');

Here, ‘h’ represents the hour, ‘i’ represents the minute, ‘p’ represents AM or PM.

The output of the above will be like this:

4. HOUR(), MINUTE(), SECOND() - The HOUR() function is used to display the hour from a time value. Similarly, MINUTE() is used for the minute and SECOND() is used for the second.

SELECT HOUR(CURRENT_TIME()) AS hour, MINUTE(CURRENT_TIME()) AS minute, SECOND(CURRENT_TIME()) as second;

The output of the above will be like this:

TIMESTAMP() data type in MySQL

The TIMESTAMP() data type is used to store the combination of both date and time values. The format of a TIMESTAMP is YYYY-MM-DD HH:MM:SS.

When a TIMESTAMP value is inserted, it is converted to the UTC time zone by MySQL and when we query a TIMESTAMP value it is converted back to the MySQL server's time zone.

Example of TIMESTAMP

Let's create a table named demo_table.

CREATE_TABLE demo_table(
t_s TIMESTAMP
);

Now, we can set the time zone as shown below:

SET time_zone='+2:00';

Now, let's insert a TIMESTAMP value.

INSERT INTO demo_table(t_s)
VALUES('2021-12-24 09:57:00');

The table will now look like this:

It can be seen from the table that time is converted to the time zone set above and +02:00 is added to this.

Automatic Initialization and Updating of TIMESTAMP

MySQL provides a feature of automatic initialization and updating for a TIMESTAMP value.

For example:

Let's create a table named demo_table.

CREATE TABLE demo_table(
name VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
); 

The table is created named demo_table. In the above table, the creation time of the row is inserted using the clause DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clause inserts the timestamp at which the row is updated last time. 

Let's insert some values in this.

INSERT INTO demo_table(name)
VALUES('abc');

The table will now look like this:

Let's update the row now.

UPDATE demo_table 
SET name='xyz'
WHERE name='abc';

The table will now look like this:

It can be seen that the last _updated is updated with the last updated timestamp value.

DATETIME data type in MySQL

The DATETIME data type is used to store both date and time. The format of the stored value is YYYY:MM:DD HH:MM:SS.

The range of DATETIME values is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

 The TIMESTAMP value is stored in the UTC time zone and the DATETIME value is stored without timezone. For example:

Let's create a table named demo_table.

CREATE TABLE demo_table(
t_s TIMESTAMP,
d_t DATETIME
);

Let's insert some values in it.

INSERT INTO demo_table(t_s,d_t)
VALUES(NOW(),NOW());

Now, set the time zone to ‘+02:00’ as shown below:

SET time_zone='+02:00';
SELECT * FROM demo_table;

The output of the above statements will be:

It can be seen in the above table, there is a difference between the value of t_s and d_t of '02:00'. This is because TIMESTAMP is affected by the time zone and DATETIME is not.

DATETIME functions

There are some DATETIME functions that are very useful. 

The hour, minute, second, day, week, month, year can be extracted and displayed from a TIMESTAMP value using the HOUR(), MINUTE(), SECOND(), DAY(), WEEK(), MONTH(), YEAR() functions respectively.

For example -

SELECT DAY(NOW());

The output of the above will be like this: