# What is the difference between DATETIME and TIMESTAMP data type in MySQL?

By Bobby Iliev, 2021-12-04. https://bobbyiliev.com/post/what-is-the-difference-between-datetime-and-timestamp-data-type-in-mysql/

# Introduction

If you have ever worked with MySQL and had to create your table structure, you have probably noticed that the `DATETIME` and `TIMESTAMP` data types are quite similar at a first glance as they have a lot of similarities. 

In this tutorial, we will go over each one of them and learn the main differences between both so that you could know when you should use them.

https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql

# `DATETIME`

The `DATETIME` type is used for values that contain both the date and the time.

For example, when retrieving the value, MySQL will display it in the following format: `YYYY-MM-DD hh:mm:ss`.

An important distinction is that the range that is currently supported is `1000-01-01 00:00:00` to `9999-12-31 23:59:59`.

You can think of `DATETIME` as a literal value, as it stores the date and the time but has no reference to a specific timezone. So when you store the value of `NOW()`, for example, the server time zone will be used. Once inserted, the value of the `DATETIME` will remain the same regardless of any current sessions.

This is an important distinction that will get a bit clearer once you go over the example below.

# `TIMESTAMP`

Just as `DATETIME`, the `TIMESTAMP` data type contains both the date and the time in the following format `YYYY-MM-DD hh:mm:ss`.

However, unlike `DATETIME`, the `TIMESTAMP` data type has a fixed range between `1970-01-01 00:00:01` UTC to `2038-01-19 03:14:07` UTC. You should always consider this limit as it might not be suitable for all applications and use cases. A [Unix timestamp converter](https://devops-daily.com/tools/timestamp-converter) shows the dates at both ends of that range. For more information on why this specific range, make sure to read more about the [Unix Time](https://en.wikipedia.org/wiki/Unix_time).

An important thing to keep in mind is that when you display the values of a `TIMESTAMP` column, they would be converted to the current time zone. What happens is that MySQL would convert the `TIMESTAMP` values from the current time zone to `UTC` when storing them, but then it would convert the values back from UTC to the current time zone of the retrieval. 

Let's go through the following example to make this a bit more clear!

# Example

Let's create a very simple table with just two columns for each of the two data types:

```
CREATE TABLE demo_table(
    datetime_column DATETIME,
    timestamp_column TIMESTAMP
);
```

Then let's insert the same values in both using `NOW()`:

```
INSERT INTO demo_table VALUES ((NOW()),(NOW()));
```

Then check the values of both columns:

```
SELECT * FROM demo_table;

// Output
+---------------------+---------------------+
| datetime_column     | timestamp_column    |
+---------------------+---------------------+
| 2021-12-04 17:42:16 | 2021-12-04 17:42:16 |
+---------------------+---------------------+
```

So far the values of both columns are the same. But then let's check the current time zone:

```
SHOW VARIABLES LIKE '%time_zone%';

// Output
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
```

Next, let's change the time zone for our current session:

```
SET time_zone="America/New_york";
```

Note: if you get the following error: `ERROR 1298 (HY000): Unknown or incorrect time zone: 'America/new_york'` you can run the following command in another terminal window:

```
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql
```

After that let's see how the output values change:

```
SELECT * FROM demo_table;

// Output
+---------------------+---------------------+
| datetime_column     | timestamp_column    |
+---------------------+---------------------+
| 2021-12-04 17:42:16 | 2021-12-04 12:42:16 |
+---------------------+---------------------+
```

As you can see, the displayed value of the `datetime_column` remained the same but the `timestamp_column` value changed and is now referencing our current time zone.

# Conclusion

This is all! Now you should have a better idea of what the main differences between `datetime` and `timestamp` are!

In case that you want to learn more about SQL in general, I would recommend the following free eBook:

 - [Introduction to SQL open-source eBook](/post/opensource-introduction-to-sql-ebook/)

In case that you are already using SQL on daily basis, and are looking for a way to drastically reduce the latency of your data analytics, make sure to out [Materialize](https://materialize.com/)!

![Materialize - a streaming database](https://imgur.com/52d9a6h.png)

Materialize is a Streaming Database for Real-time Analytics. It is a reactive database that delivers incremental view updates and it helps developers easily build with streaming data using standard SQL.