MySQL TIMESTAMP Type

November 10, 2022 | by Arround The Web | No comments

“This tutorial will cover the basics of working with TIMESTAMP data type in MySQL.

The TIMESTAMP data type allows you to store date and time values in the format of TIMESTAMP as YYYY-MM-DD HH:MM:SS.

The value of a timestamp data type in MySQL ranges from 1970-01-01 00:00:01 to 2038-01-19 03:14:07 in UTC.

When storing a timestamp value, MySQL will convert the current time to UTC and store it in the target table. MySQL will convert the value from UTC to the current time during retrieval. It is good to remember that this feature only applies to the timestamp data type.

Therefore, since the time zone can be set on a given session, My SQL allows you to retrieve the same value stored as long as the time zone remains constant.”

MySQL Timezone Example

Let us cover an example of how to work with the timestamp data type in MySQL. Consider the table below with a timestamp column.

create database tempdb;
use tempdb;
create table sample(
id int auto_increment primary key,
t timestamp
);
set time_zone ='+03:00';

The query above creates a sample database and table with a timestamp column. We also set the time zone for the session using the time_zone parameter.

We can then insert date and time values into the column as shown in the query below:

insert into sample(t)
values ('2022-10-10 23:45:23'),
('2020-12-10 13:43:32'),
('1990-10-10 16:23:32');

We can then select the timestamp values from the table as shown:

select * from sample;

Result:

Demonstrating the Use of Time Zone in Timestamp

As mentioned, if the time zone is constant, we can get the same value we saved in the table, as demonstrated above.

Let us see what values we get if we change the time zone value:

set time_zone = '-7:00';
select * from sample;

If we run the statement above, we should get a table with the value as shown:

Notice we get different values from what we saved. This occurs due to the change in the timezone.

Using Automatic Initialization Using the Timestamp Data Type

We can automatically initialize a given column by setting the default constraint to the current_timestamp.

In such as case, MySQL will insert the current timestamp value to a given row, as shown in the example below:

create table sample(
id int auto_increment primary key,
t timestamp default current_timestamp
);

We can then insert values into the table as shown:

insert into sample(id)
values (1),(2),(3);

As you can notice, we do not provide for the t column. This should force MySQL to use the default value as shown:

select * from sample;

Output:

As you can see, all rows share the same timestamp value denoting the insert time. This feature is referred to as automatic initialization.

Conclusion

In this example, we discussed the fundamentals of working with the TIMESTAMP data type in MySQL.

Source: linuxhint.com