Should I use the datetime or timestamp data type in MySQL?

I like a Unix timestamp, because you can convert to numbers and just worry about the number. Plus you add/subtract and get durations, etc. Then convert the result to Date in whatever format. This code finds out how much time in minutes passed between a timestamp from a document, and the current time.

$date = $item['pubdate']; (etc ...)
$unix_now = time();
$result = strtotime($date, $unix_now);
$unix_diff_min = (($unix_now - $result) / 60);
$min = round($unix_diff_min);

The below examples show how the `TIMESTAMP` date type changed the values after changing the `time-zone to 'america/new_york'` where `DATETIME`is unchanged.

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name | Value |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone | Asia/Calcutta |
+------------------+---------------------+

mysql> create table datedemo(
-> mydatetime datetime,
-> mytimestamp timestamp
-> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime | mytimestamp |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime | mytimestamp |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

I've converted my answer into article so more people can find this useful, *[MySQL: Datetime Versus Timestamp Data Types][1]*.

[1]:

[To see links please register here]

I found unsurpassed usefulness in TIMESTAMP's ability to auto update itself based on the current time without the use of unnecessary triggers. That's just me though, although TIMESTAMP is UTC like it was said.

It can keep track across different timezones, so if you need to display a relative time for instance, UTC time is what you would want.

I always use a Unix timestamp, simply to maintain sanity when dealing with a lot of datetime information, especially when performing adjustments for timezones, adding/subtracting dates, and the like. When comparing timestamps, this excludes the complicating factors of timezone and allows you to spare resources in your server side processing (Whether it be application code or database queries) in that you make use of light weight arithmetic rather then heavier date-time add/subtract functions.

Another thing worth considering:

If you're building an application, you never know how your data might have to be used down the line. If you wind up having to, say, compare a bunch of records in your data set, with, say, a bunch of items from a third-party API, and say, put them in chronological order, you'll be happy to have Unix timestamps for your rows. Even if you decide to use MySQL timestamps, store a Unix timestamp as insurance.

It is worth noting in MySQL you can use something along the lines of the below when creating your table columns:

on update CURRENT_TIMESTAMP

This will update the time at each instance you modify a row and is sometimes very helpful for stored last edit information. This only works with timestamp, not datetime however.

From my experiences, if you want a date field in which insertion happens only once and you don't want to have any update or any other action on that particular field, go with **date time**.

For example, consider a `user` table with a **REGISTRATION DATE** field. In that `user` table, if you want to know the last logged in time of a particular user, go with a field of **timestamp** type so that the field gets updated.

If you are creating the table from [phpMyAdmin][1] the default setting will update the **timestamp** field when a row update happens. If your timestamp filed is not updating with row update, you can use the following query to make a **timestamp** field get auto updated.

ALTER TABLE your_table
MODIFY COLUMN ts_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

[1]:

[To see links please register here]

The timestamp data type stores date and time, but in UTC format, not in the current timezone format as datetime does. And when you fetch data, timestamp again converts that into the current timezone time.

So suppose you are in USA and getting data from a server which has a time zone of USA. Then you will get the date and time according to the USA time zone. The timestamp data type column always get updated automatically when its row gets updated. So it can be useful to track when a particular row was updated last time.

For more details you can read the blog post *[Timestamp Vs Datetime ][1]*.

[1]:

[To see links please register here]

Beware of timestamp changing when you do a UPDATE statement on a table. If you have a table with columns 'Name' (varchar), 'Age' (int), and 'Date_Added' (timestamp) and you run the following DML statement

UPDATE table
SET age = 30

then every single value in your 'Date_Added' column would be changed to the current timestamp.

[Reference taken from this Article:][1]

**The main differences:**

TIMESTAMP used to track changes to records, and update every time when the record is changed.
DATETIME used to store specific and static value which is not affected by any changes in records.

TIMESTAMP also affected by different TIME ZONE related setting.
DATETIME is constant.

TIMESTAMP internally converted current time zone to UTC for storage, and during retrieval converted back to the current time zone.
DATETIME can not do this.

TIMESTAMP supported range:
‘1970-01-01 00:00:01′ UTC to ‘2038-01-19 03:14:07′ UTC
DATETIME supported range:
‘1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59′


[1]:

[To see links please register here]

A `TIMESTAMP` requires 4 bytes, whereas a `DATETIME` requires 8 bytes.