MySQL TIMESTAMP Data Type

Introduction

In this chapter, we will learn about the TIMESTAMP data type in MySQL. The TIMESTAMP data type is used to store both date and time information and is particularly useful for tracking changes to records, such as creation and modification times. Unlike DATETIME, TIMESTAMP values are stored in UTC and converted to the current time zone upon retrieval. We will cover the syntax for defining TIMESTAMP columns, examples, and important considerations for using TIMESTAMP in MySQL.

Syntax

The basic syntax for defining a TIMESTAMP column in MySQL is:

TIMESTAMP

The TIMESTAMP data type stores both date and time information in the format YYYY-MM-DD HH:MM:SS.

Example

Here is an example of how to define a TIMESTAMP column in a table:

CREATE TABLE user_logins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In this example, login_time is defined as a TIMESTAMP column to store the date and time of user logins, with a default value of the current timestamp.

Full Example

Let’s go through a complete example where we create a database and a table with TIMESTAMP columns, insert data, and demonstrate their usage.

1. Create a Database:

CREATE DATABASE website;

2. Select the Database:

USE website;

3. Create the User Logins Table with TIMESTAMP Columns:

CREATE TABLE user_logins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    logout_time TIMESTAMP NULL
);

4. Insert Data into the User Logins Table:

INSERT INTO user_logins (username) VALUES
('rahul_sharma'),
('priya_singh'),
('amit_kumar'),
('neha_verma'),
('sahil_mehta');

5. Update Logout Time for a User:

UPDATE user_logins SET logout_time = '2023-07-05 14:00:00' WHERE username = 'rahul_sharma';

6. Query the User Logins Table:

SELECT * FROM user_logins;

Output

In this example, the login_time column is defined as TIMESTAMP with a default value of the current timestamp, and logout_time is a nullable TIMESTAMP column.

Using TIMESTAMP Values in Queries

You can use TIMESTAMP values in queries to filter, sort, and manipulate date and time information.

Example: Query Logins After a Specific Date and Time

SELECT * FROM user_logins WHERE login_time > '2023-07-05 12:00:00';

Output

Example: Query Logins Before a Specific Date and Time

SELECT * FROM user_logins WHERE login_time < '2023-07-05 12:30:00';

Output

Example: Query Logins on a Specific Date

SELECT * FROM user_logins WHERE DATE(login_time) = '2023-07-05';

Output

Important Considerations

• Storage Requirements: The TIMESTAMP data type uses 4 bytes of storage.

• Range: The TIMESTAMP data type supports a range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

• Time Zone: TIMESTAMP values are stored in UTC and converted to the current time zone upon retrieval. This makes it suitable for applications that need to be time zone-aware.

• Automatic Initialization and Updating: You can set TIMESTAMP columns to automatically initialize and update to the current timestamp.

  CREATE TABLE logs (
      id INT PRIMARY KEY AUTO_INCREMENT,
      message VARCHAR(255),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  );
  

Conclusion

The TIMESTAMP data type is essential for storing date and time information in MySQL, particularly for time zone-aware applications. This chapter covered the syntax for defining TIMESTAMP columns, provided a full example with different timestamp attributes, and discussed important considerations.