Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

In MySQL 5 and above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.)

By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis, as described in MySQL Server Time Zone Support.

I always use DATETIME fields for anything other than row metadata (date created or modified).

As mentioned in the MySQL documentation:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

...

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

You're quite likely to hit the lower limit on TIMESTAMPs in general use -- e.g. storing birthdate.

The main difference is that DATETIME is constant while TIMESTAMP is affected by the time_zone setting.

So it only matters when you have — or may in the future have — synchronized clusters across time zones.

In simpler words: If I have a database in Australia, and take a dump of that database to synchronize/populate a database in America, then the TIMESTAMP would update to reflect the real time of the event in the new time zone, while DATETIME would still reflect the time of the event in the au time zone.

A great example of DATETIME being used where TIMESTAMP should have been used is in Facebook, where their servers are never quite sure what time stuff happened across time zones. Once I was having a conversation in which the time said I was replying to messages before the message was actually sent. (This, of course, could also have been caused by bad time zone translation in the messaging software if the times were being posted rather than synchronized.)

I make this decision on a semantic base.

I use a timestamp when I need to record a (more or less) fixed point in time. For example when a record was inserted into the database or when some user action took place.

I use a datetime field when the date/time can be set and changed arbitrarily. For example when a user can save later change appointments.

TIMESTAMP is 4 bytes Vs 8 bytes for DATETIME.

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

But like scronide said it does have a lower limit of the year 1970. It's great for anything that might happen in the future though ;)

Neither. The DATETIME and TIMESTAMP types are fundamentally broken for generic use cases. MySQL will change them in the future. You should use BIGINT and UNIX timestamps unless you have a specific reason to use something else.

Special cases

Here are some specific situations where your choice is easier and you don't need the analysis and general recommendation in this answer.

• Date only — if you only care about the date (like the date of the next Lunar New Year, 2022-02-01) AND you have a clear understanding of what timezone that date applies (or don't care, as in the case of Lunar New Year) then use the DATE column type.

• Record insert times — if you are logging the insert dates/times for rows in your database AND you don't care that your application will break in the next 17 years, then go ahead and use TIMESTAMP with a default value of CURRENT_TIMESTAMP().

Why is TIMESTAMP broken?

The TIMESTAMP type is stored on disk in UTC timezone. This means that if you physically move your server, it does not break. That's good ✅. But timestamps as currently defined will stop working entirely in the year 2038 ❌.

Every time you INSERT INTO or SELECT FROM a TIMESTAMP column, the physical location (i.e. timezone configuration) of your client/application server is taken into account. If you move your application server then your dates break ❌.

(Update 2022-04-29 MySQL fixed this in 8.0.28 but if your production environment is on CentOS 7 or many other flavors your migration path will be a long time until you get this support.)

Why is VARCHAR broken?

The VARCHAR type allows to unambiguously store a non-local date/time/both in ISO 8601 format and it works for dates past 2037. It is common to use Zulu time, but ISO 8601 allows to encode any offset. This is less useful because while MySQL date and time functions do support string as input anywhere date/time/both are expected, the result is incorrect if the input uses timezone offsets.

Also VARCHAR uses extra bytes of storage.

Why is DATETIME broken?

A DATETIME stores a DATE and a TIME in the same column. Neither of these things have any meaning unless the timezone is understood, and the timezone is not stored anywhere ❌. You should put the intended timezone as a comment in the column because the timezone is inextricably linked to the data. So few people use column comments, therefore this is mistake waiting to happen. I inherited a server from Arizona, so I always need to convert all timestamps FROM Arizona time and then TO another time.

(Update 2021-12-08 I restarted the server after years of uptime and the database client (with upgrades) reset to UTC. That means my application needs to handle dates before and after the reset differently. Hardcode!)

The only situation a DATETIME is correct is to complete this sentence:

Your year 2020 solar new year starts at exactly DATETIME("2020-01-01 00:00:00").

There is no other good use for DATETIMEs. Perhaps you will imagine a web server for a city government in Delaware. Surely the timezone for this server and all the people accessing this server can be implied to be in Delaware, with Eastern Time Zone, right? Wrong! In this millennium, we all think of servers as existing in "the cloud". So it is always wrong to think of your server in any specific timezone, because your server will be moved some day.

Note: MySQL now supports time zone offsets in DATETIME literals (thanks @Marko). This may make inserting DATETIMEs more convenient for you but does not address the incomplete and therefore useless meaning of the data, this fatal issue identifies ("❌") above.

How to use BIGINT?

Define:

CREATE TEMPORARY TABLE good_times (
    a_time BIGINT
)

Insert a specific value:

INSERT INTO good_times VALUES (
    UNIX_TIMESTAMP(CONVERT_TZ("2014-12-03 12:24:54", '+00:00', @@global.time_zone))
);

Or of course this is much better from your application, like:

$statement = $myDB->prepare('INSERT INTO good_times VALUES (?)');
$statement->execute([$someTime->getTimestamp()]);

Select:

SELECT a_time FROM good_times;

There are techniques for filtering relative times (select posts within the past 30 days, find users that bought within 10 minutes of registering) beyond the scope here.

A timestamp field is a special case of the datetime field. You can create timestamp columns to have special properties; it can be set to update itself on either create and/or update.

In "bigger" database terms, timestamp has a couple of special-case triggers on it.

What the right one is depends entirely on what you want to do.

I would always use a Unix timestamp when working with MySQL and PHP. The main reason for this being the default date method in PHP uses a timestamp as the parameter, so there would be no parsing needed.

To get the current Unix timestamp in PHP, just do time();
and in MySQL do SELECT UNIX_TIMESTAMP();.

DATETIME vs TIMESTAMP:

TIMESTAMP used to track changes of 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 a current time zone to UTC for storage, and during retrieval convert the back to the current time zone.

DATETIME can not do this.

TIMESTAMP is 4 bytes and DATETIME is 8 bytes.

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′

I stopped using datetime in my applications after facing many problems and bugs related to time zones. IMHO using timestamp is better than datetime in most of the cases.

When you ask what is the time ? and the answer comes as something like '2019-02-05 21:18:30', that is not completed, not defined answer because it lacks another part, in which timezone ? Washington ? Moscow ? Beijing ?

Using datetimes without the timezone means that your application is dealing with only 1 timezone, however timestamps give you the benefits of datetime plus the flexibility of showing the same exact point of time in different timezones.

Here are some cases that will make you regret using datetime and wish that you stored your data in timestamps.

1. For your clients comfort you want to show them the times based on their preferred time zones without making them doing the math and convert the time to their meaningful timezone. all you need is to change the timezone and all your application code will be the same.(Actually you should always define the timezone at the start of the application, or request processing in case of PHP applications)

   SET time_zone = '+2:00';
   

2. you changed the country you stay in, and continue your work of maintaining the data while seeing it in a different timezone (without changing the actual data).

3. you accept data from different clients around the world, each of them inserts the time in his timezone.

In short

datetime = application supports 1 timezone (for both inserting and selecting)

timestamp = application supports any timezone (for both inserting and selecting)

This answer is only for putting some highlight on the flexibility and ease of timestamps when it comes to time zones , it is not covering any other differences like the column size or range or fraction.

I prefer using timestamp so to keep everything in one common raw format and format the data in PHP code or in your SQL query. There are instances where it comes in handy in your code to keep everything in plain seconds.

TIMESTAMP is useful when you have visitors from different countries with different time zones. you can easily convert the TIMESTAMP to any country time zone

A DATETIME carries no timezone information with it and will always display the same independent of the timezone that is in effect for the session, which defaults to the server's timezone unless you have explicitly changed it. However, if I initialize a DATETIME column with a function such as NOW() rather than a literal such as '2020-01-16 12:15:00', then the value stored will, of course, be the current date and time localized to the session's timezone.

A TIMESTAMP by contrast does implicitly carry timezone information: When you initialize a TIMESTAMP column with a value, that value is converted to UTC before it is stored. If the value being stored is a literal such as '2020-01-16 12:15:00', it is interpreted as being in the session's current timezone for conversion purposes. Conversely, when a TIMESTAMP column is displayed, it will first be converted from UTC to the session's current timezone.

When to use one or the other? A Case Study

A Website for a community theater group is presenting several performances of a play for which it is selling tickets. The dates and times of these performances will appear in a drop down from which a customer wishing to buy tickets for a performance will select one. It would make sense for database column performance_date_and_time to be a DATETIME type. If the performance is in New York, there is an understanding that there is an implicit timezone involved ("New York local time") and ideally we would want the date and time to display as 'December 12, 2019 at 8:00 PM' regardless of the session's timezone and without having to go to the trouble of having to do any timezone conversions.

On the other hand, once the December 12th, 2019 8 PM performance began, we might no longer want to sell tickets for it and thus no longer display that performance in the drop down. So, we would like to be able to know whether '2019-12-12 20:00:00' has occurred or not. That would argue for having a TIMESTAMP column, setting the timezone for the session to 'America/New_York' with set session time_zone='America/New_York' and then storing '2019-12-12 20:00:00' into the TIMESTAMP column. Henceforth we can test for whether the performance has begun by comparing this column with NOW() independent of the current session timezone.

Or it might make sense to have a DATETIME and a TIMESTAMP column for these two separate purposes. Or not. Clearly, either one could serve both purposes. If you go with just a DATETIME column, then you must set the current timezone to your local timezone before comparing with NOW(). If you go with just a TIMESTAMP column, you must set the session timezone to your local timezone before displaying the column.

timestamp is a current time of an event recorded by a computer through Network Time Protocol (NTP).

datetime is a current timezone that is set in your PHP configuration.

If you want to GUARANTEE your application will NOT function in February, 2038, use TIMESTAMP. Refer to your REFMAN for the RANGE of dates supported.

The DATETIME type is used for values containing date and time parts. MySQL retrieves and displays DATETIMEvalues in format. The supported range is . 'YYYYYY-MM-DD hh:mm:ss' '1000-01-01 00:00:00''9999-12-31 23:59:59'

The TIMESTAMP data type is used for values containing date and time parts. TIMESTAMP has a range from '1970-01-01 00:00:01'UTC to '2038-01-19 03:14:07'UTC.

mysql> SELECT col,
     >     CAST(col AT TIME ZONE INTERVAL '+00:00' AS DATETIME) AS ut
     >     FROM ts ORDER BY id;
+---------------------+---------------------+
| col                 | ut                  |
+---------------------+---------------------+
| 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
| 2019-12-31 23:40:10 | 2020-01-01 04:40:10 |
| 2020-01-01 13:10:10 | 2020-01-01 18:10:10 |
| 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
| 2020-01-01 04:40:10 | 2020-01-01 09:40:10 |
| 2020-01-01 18:10:10 | 2020-01-01 23:10:10 |
+---------------------+---------------------+

URL MySQL 8.0 : https://dev.mysql.com/doc/refman/8.0/en/datetime.html