Introduction to MySQL TIMESTAMP Data Type

The TIMESTAMP data type in MySQL is used to store dates and times. It is similar to the DATETIME data type, but it is more convenient for storing and retrieving timestamp values.

Syntax

The syntax for the TIMESTAMP data type is as follows:

TIMESTAMP[(fractional_seconds)];

where fractional_seconds is optional and represents the number of digits for fractional seconds. If fractional_seconds is not specified, it defaults to 0.

Use Cases

The TIMESTAMP data type is commonly used in applications that need to store and retrieve date and time information. It is suitable for the following scenarios:

Storing timestamps for record creation or modification;
Tracking the occurrence time of events;
Storing scheduled times.

Examples

Here are two examples that demonstrate how to use the TIMESTAMP data type.

First, we create a table named orders that contains information about orders, including order ID, customer name, order date, and order status:

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(50) NOT NULL,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  order_status VARCHAR(20) NOT NULL
);

In this example, the order_date column uses the TIMESTAMP data type and is set with a default value of the current timestamp.

Next, we insert some sample data into the orders table:

INSERT INTO orders (customer_name, order_status)
VALUES ('John Smith', 'Pending'),
       ('Jane Doe', 'Complete'),
       ('Bob Johnson', 'Pending');

Now, we can retrieve the data from the orders table and view the values in the order_date column:

SELECT * FROM orders;

The result will be similar to the following:

+----------+---------------+---------------------+--------------+
| order_id | customer_name | order_date          | order_status |
+----------+---------------+---------------------+--------------+
|        1 | John Smith    | 2023-03-12 12:34:56 | Pending      |
|        2 | Jane Doe      | 2023-03-12 12:35:01 | Complete     |
|        3 | Bob Johnson   | 2023-03-12 12:35:05 | Pending      |
+----------+---------------+---------------------+--------------+

In this result, the order_date column displays the timestamp values for each order.

Next, we will use the TIMESTAMP data type to store the occurrence time of events. We create a table named events that includes the event name and event time:

CREATE TABLE events (
  event_id INT AUTO_INCREMENT PRIMARY KEY,
  event_name VARCHAR(50) NOT NULL,
  event_time TIMESTAMP
);

In this example, the event_time column uses the TIMESTAMP data type to store the occurrence time of events.

Then, we insert some sample data into the events table:

INSERT INTO events (event_name, event_time)
VALUES ('Event 1', '2023-03-12 12:40:00'),
       ('Event 2', '2023-03-12 12:45:00'),
       ('Event 3', '2023-03-12 12:50:00');

Now, we can retrieve all the data from the events table:

SELECT * FROM events;

This will return all data from the events table, including the values of the id, title, description, start_time, and end_time columns.

If we only want to retrieve certain columns, we can use the following statement:

SELECT id, title, start_time FROM events;

This will only return the values of the id, title, and start_time columns.

We can also use the WHERE clause to filter specific data. For example, the following statement will return all events that start before March 15, 2023:

SELECT * FROM events WHERE start_time < '2023-03-15';

This will only return events that satisfy the condition, and other events will be excluded.

In addition, we can use the ORDER BY clause to sort the retrieval results, for example:

SELECT * FROM events ORDER BY start_time DESC;

This will sort the events in descending order based on the start_time column, with the latest events listed first.

Conclusion

The TIMESTAMP data type in MySQL is used to store dates and times. It is similar to the DATETIME data type, but it is more convenient for storing and retrieving timestamp values.

Syntax

Use Cases

Examples

Conclusion

On this page