MySQL Timestamp – Definition and Usage

In this tutorial we’ll learn to use a MySQL Timestamp, we’ll see the syntax, how to initialize a timestamp, auto-initialization, manually inserting the timestamp into a table, and so on.

Also check: MySQL UTC_TIME(), UTC_DATE() and UTC_TIMESTAMP() functions

Introduction

MySQL Timestamp is a data type for date and time format. It is used when we want to add a record that is associated with the date and time, mostly current. For example, if you are using a hospital management system and you are a receptionist, here you might be going to need to enter the time a patient came and the time they left.

This is where timestamps come in handy, as you can auto initialize that. Timestamps as the name suggests are also useful when you want to keep the time record of each and every execution of a query.

Syntax of MySQL Timestamp

CREATE TABLE tableName (
fieldname Timestamp(fsp) -- fsp is not mandatory
);Code language: SQL (Structured Query Language) (sql)

A basic timestamp format contains the date and time (with fraction precision, if mentioned), “YYYY-MM-DD HH:MM:SS.fsp”, here the fsp or fraction seconds ranges from 0-6 decimal points.

MySQL Timestamp Example

Let’s see the example for timestamp, we’ll first create a simple table named learn_timestamp, and we’ll create two fields, the first will be serial_col, and the second, will be timestamp_col. Let’s see the code below:

CREATE TABLE learn_timestamp (
serial_col INT,
timestamp_col TIMESTAMP
);Code language: SQL (Structured Query Language) (sql)

Let’s see how the table looks when we describe it.

Here we can see that there is that the default value is NULL, which in turn means that currently, the timestamp_col column is not auto initialized and we’ll have to insert the timestamp manually. Let’s now try and insert our first record in this table.

INSERT INTO learn_timestamp (serial_col, timestamp_col)
VALUES 
(1, '2022-07-13 08:31:20');Code language: SQL (Structured Query Language) (sql)

Let’s see the table using the SELECT statement:

Auto Initialization of MySQL Timestamp

Let’s try one more insertion without timestamp input, and see what the output will be. As you can see in the below image, the timestamp will be NULL, because it has not been entered manually.

This problem can be solved using auto-increment, we can do that by altering the learn_timestamp table. Let’s see how we can do that:

ALTER TABLE learn_timestamp MODIFY
    COLUMN timestamp_col
    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP;Code language: SQL (Structured Query Language) (sql)

Let’s describe the table and see what changes have occurred in the table.

Here we can see the default for timestamp_col is now changed to CURRENT_TIMESTAMP. Let’s insert a new record into the table, we will only insert the serial_col and the timestamp_col will take the current timestamp as the input.

INSERT INTO learn_timestamp (serial_col) VALUES (2);Code language: SQL (Structured Query Language) (sql)

Let’s see the output, and you’ll see how we can auto-update the timestamp fields without manually writing them.

MySQL Timestamp(fsp)

MySQL Timestamp(fsp or fraction seconds) is the decimal extension in correspondence to the time. It goes up to 6 decimal points and you can mention the same while creating a timestamp field in the MySQL table.

Let’s create a table that has one single field with timestamp(fsp) datatype. The fps will be 2 in this case, which means 2 decimal precision will be saved into the database.

-- Create timestamp table with 2 fractional seconds

CREATE TABLE table_timestamp(
timestamp_c TIMESTAMP(2)
);

--Insert timestamp value into the table 
INSERT INTO table_timestamp
VALUES
('2022-07-03 01:34:22.34');Code language: SQL (Structured Query Language) (sql)

As we have created the timestamp(fsp) table and inserted a record, let’s see the output:

Remember, if you have an already existing table that has a timestamp field but you want to add fractions as well then you will have to recreate that table again after dropping the table.

Similarities Between Datetime and Timestamp

Datetime and Timestamp have a lot of similarities:

• Both include the date and time.
• Both stores date in the same format ‘YYYY-MM-DD HH:MM:SS’.
• Both have a fractional second precision of 6.
• Both allow automatic initialization.

Summary

MySQL timestamp is useful when the current time is required while inserting records into a table. In this tutorial, we went in-depth to understand MySQL timestamp while learning about its working, we learned about its syntax and we saw a lot of examples related to it. In the end, we saw the similarities and differences between MySQL DateTime and Timestamp.

Reference:

To get to the official website, please click here.