MySQL Timestamp Data Type

The MySQL TIMESTAMP data type is used to store date and time values. It has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

TIMESTAMP values are stored in UTC (Coordinated Universal Time) format, regardless of the time zone of the MySQL server or the client application. When you insert a TIMESTAMP value into a table, MySQL converts it from your connection's time zone to UTC for storing. When you query a TIMESTAMP value, MySQL converts the UTC value back to your connection's time zone.

The TIMESTAMP data type has the following properties:

• It is a fixed-length data type with a length of 19 characters.
• The format of a TIMESTAMP value is YYYY-MM-DD HH:MM:SS.
• TIMESTAMP values can be NULL.
• TIMESTAMP columns can have a default value.
• TIMESTAMP values can be used in indexes.

TIMESTAMP columns can be used to store the following information:

• The creation and modification dates of database records.
• The timestamps of events, such as transactions or login attempts.
• The expiration dates of data.

The following is an example of how to create a table with a TIMESTAMP column:

SQL

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

The created_at and updated_at columns will automatically be set to the current date and time when a new record is inserted or an existing record is updated.

You can use the following MySQL functions to manipulate TIMESTAMP values:

• CURRENT_TIMESTAMP(): Returns the current date and time in UTC format.
• NOW(): Returns the current date and time in the server's time zone.
• FROM_UNIXTIME(): Converts a UNIX timestamp to a TIMESTAMP value.
• UNIX_TIMESTAMP(): Converts a TIMESTAMP value to a UNIX timestamp.

The following is an example of how to use the CURRENT_TIMESTAMP() function to insert a new record into the users table:

SQL

INSERT INTO users (username, email) VALUES ('johndoe', 'john.doe@example.com');

The created_at and updated_at columns will automatically be set to the current date and time when the new record is inserted.

The following is an example of how to use the FROM_UNIXTIME() function to convert a UNIX timestamp to a TIMESTAMP value:

SQL

SELECT FROM_UNIXTIME(1665692425);

This query will return the following result:

2023-10-13 16:50:25

The following is an example of how to use the UNIX_TIMESTAMP() function to convert a TIMESTAMP value to a UNIX timestamp:

SQL

SELECT UNIX_TIMESTAMP('2023-10-13 16:50:25');

This query will return the following result:

1665692425

The TIMESTAMP data type is a powerful tool that can be used to store and manipulate date and time values in MySQL. It is often used to store the creation and modification dates of database records, the timestamps of events, and the expiration dates of data.