What is timestamp data type in MySQL?

What is a data type?

In computer programming, a data type tells the compiler how the programmer is intended to use data in a program. The basic data types are integers, boolean, floating points, and characters.

What is a timestamp?

This temporal data type in SQLStructured Query Language is used to store time and date instances. The readable convention for the timestamp is YYYY-MM-DD HH:MM:SS. Moreover, the timestamp datatype has a length of 19 characters.

Note: When you insert a timestamp value into a table, the SQL server converts it to the UTCCoordinated Universal Time format instead of the respective time zone.

Syntax

CREATE TABLE Table_Name (

    variable_name  TIMESTAMP

);

Explanation

In this code snippet, we are heading to create a random table, which will contain an attribute of TIMESTAMP type. Furthermore, we will insert some random values to try out.

-- created a clock table with one attribute
CREATE TABLE clock (
    record  TIMESTAMP
);
-- set current session to '+00:00' UTC
SET time_zone='+00:00';
-- Insert values into clock table YYYY-MM-DD HH:MM:SS
INSERT INTO clock VALUES('2022-06-07 08:43:59');
INSERT INTO clock VALUES('2022-06-07');
-- fetch all timestamp records from table
SELECT record FROM clock;
  • Lines 2–4: We create an SQL table named clock with an attribute (record) of TIMESTAMP type.
  • Line 6: The SET time_zone=’+00:00’; timestamp sets the current SQL server environment instance time zone to '00:00' UTC. It will intact the data integrity that is going to be inserted.
  • Lines 8 and 9: We insert two timestamp values (according to the aforementioned format) into the above table.

Note: The format for the timestamp data type: YYYY-MM-DD HH:MM:SS. It consists of two parts, one is the date and the other is time. Where the date is required, while time is optional.

  • Line 11: It fetches all records from the clock table.

Free Resources

License: Creative Commons-Attribution NonCommercial-ShareAlike 4.0 (CC-BY-NC-SA 4.0)