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

Would you recommend using a datetime or a timestamp field, and why (using MySQL)?

I'm working with PHP on the server side.

3.01.2009 16:14:37
Waqleh 26.07.2018 08:46:41
If you want your application to be broken in February, 2038 use timestamp. Check the date range.
Wilson Hauck 11.03.2020 17:55:19
30 ОТВЕТОВ
РЕШЕНИЕ

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 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.

1817
24.02.2018 04:47:42
An important difference is that DATETIME represents a date (as found in a calendar) and a time (as can be observed on a wall clock), while TIMESTAMP represents a well defined point in time. This could be very important if your application handles time zones. How long ago was '2010-09-01 16:31:00'? It depends on what timezone you're in. For me it was just a few seconds ago, for you it may represent a time in the future. If I say 1283351460 seconds since '1970-01-01 00:00:00 UTC', you know exactly what point in time I talk about. (See Nir's excellent answer below). [Downside: valid range].
MattBianco 1.09.2010 14:36:17
Another one difference: queries with "native" datetime will not be cached, but queries with timestamp - will be.
OZ_ 28.04.2011 17:37:18
"Timestamps in MySQL generally used to track changes to records" Do not think that's a good answer. Timestamp are a lot more powerful and complicated than that as MattBianco and Nir sayd. Although, the second part of the answer is very good. It's true what blivet said, and is a good advise.
santiagobasulto 16.05.2011 14:00:07
Also 1 important note, DATETIME and TIMESTAMP can use CURRENT_TIMESTAMP, NOW() respectfully as it's default value, but DATE for example can't, because it uses '0000-00-00' by default, so to solve that matter U should write Your own trigger to that table, to insert current date (without time) in the field/col with DATE mysql type.
Arthur Kushman 15.10.2012 19:13:09
@DavidHarkness: Documentation says "To specify automatic properties, use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses" dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
Plap 23.02.2013 19:05:55

I would always use a Unix timestamp when working with MySQL and PHP. The main reason for this being the 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();.

23
5.10.2015 17:05:12
-1 I actually think the answer below is better - using datetime allows you to push more logic for date processing into MySQL itself, which can be very useful.
Toby Hede 4.01.2009 05:00:16
Haven't there been benchmarks showing that sorting in MySQL is slower than in php?
sdkfasldf 17.05.2010 13:59:39
well it depends, sometimes it good to use it when we like to not use strtotime. ( php5.3 dep )
Adam Ramadhan 2.09.2010 05:59:36
you can push the logic into mysql by just using FROM_UNIXTIME if needed
inarilo 25.05.2017 08:10:54
I used to use all Unix timestamps in my PHP applications and in MySQL, however I have found it's a lot more convenient to store dates and times in MySQL as native date/time types. This is especially useful for reporting purposes and just browsing data sets. Over time as I got frustrated having to copy/past Unix timestamps I started switching. I'm quite certain there are performance and other pros/cons, but depending on your use case convenience can be more important than fairly micro-optimizations.
DavidScherer 11.05.2018 18:02:32

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.

37
3.07.2016 18:40:29
No, the difference is not just "a special case". Because timezones of the session that sets/queries the values are involved differently.
dolmen 19.01.2016 12:50:47
I'm glad you put in "What the right one is depends entirely on what you want to do." There are too many answers on SE that state, without sufficient justification, "You must never do X" or "You must always do Y".
Agi Hammerthief 23.08.2018 08:35:53

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.

123
14.01.2011 16:10:52
timestamp- fixed point in time, Coordinated Universal Time datetime - relative to a point of view, to a time reference (ej timezone local time), could be.. Coordinated Local Time?
yucer 17.12.2013 10:48:07

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.

517
10.08.2012 14:50:14
you can also hit the upper limit easily if you are in banking or real estate... 30-year mortgages go beyond 2038 now
Kip 10.08.2012 14:47:26
Of course, use 64-bit Unix timestamps. For example, in Java, new Date().getTime() already gives you a 64-bit value.
osa 8.10.2013 01:16:46
+1 for DATETIME: In our data flow, from SQL Server of the Physical Store for purchase and invoice, transferred to MySQL Server for the Virtual Store on the webpage, DATETIME is better for Modification Date-Time as this data type exists also in Transact-SQL. But TIMESTAMP means other thing in Transact-SQL, it is Binary like ROWVERSION, although MySQL TIMESTAMP is DateTime similar. So we avoid the usage of TIMESTAMP for the compatibility of the two DBs.
jacouh 8.10.2013 14:56:41
No idea. It's a much larger problem than just MySQL and there's no simple fix: en.wikipedia.org/wiki/Year_2038_problem I don't believe MySQL can just declare timestamps are now 64-bit and assume everything will be fine. They don't control the hardware.
scronide 10.02.2015 18:11:13
A birthdate can be a DATETIME if you know the time of birth, as I do for mine.
Kris Craig 17.06.2016 06:00:19

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.

7
4.01.2009 07:49:18

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 ;)

97
4.01.2009 09:00:36
The future ends at 2038-01-19 03:14:07 UTC.
MattBianco 1.09.2010 14:39:42
That's stupid. I thought TIMESTAMP type would be "future ready" because it's relatively new. You can't bother converting datetime to UTC and back every time when you work with different time zones. They should have made TIMESTAMP bigger .. at least 1 byte bigger. it'll add 68*255 = 17340 more years ... although it won't be 32 bit aligned.
NickSoft 27.03.2012 09:55:57
Do you know why TIMESTAMP ends in 2038? Because it's an integer, and integers have limit which is 2147483647. I think this is the reason. May be if they change its type to varchar and change the way how to manipulate it, it will be "future ready".
vinsa 20.01.2015 21:16:41
@vinsa, I don't think it'll be future ready by then. Still remember Y2K bug? 2038 is coming.
Pacerier 10.02.2015 06:02:23
By 2038, MySQL (if it still exists) will simply update the TIMESTAMP field to use more than 4 bytes and allow a wider range
the_nuts 27.06.2017 10:21:53

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.

910
5.10.2015 17:07:54
this OReilly presentation is very good for this topic (PDF sorry) cdn.oreillystatic.com/en/assets/1/event/36/…
gcb 27.10.2013 02:11:22
Its also about the nature of the event: - A video-conference (TIMESTAMP). All attendants should see a reference to an absolute instant of time adjusted to its timezone. - A local task time (DATETIME), i should do this task at 2014/03/31 9:00AM no matters if that day i'm working in New York or Paris. I will start to work at 8:00AM of local time of place i'll be that day.
yucer 17.12.2013 10:40:33
So if i CHANGE the server's time zone, then the value of TIMESTAMP will remain the same or will it change too??
Andrew 11.01.2016 12:20:42
@Andrew since it's UTC, it will stay UTC. The backward conversion will change to the "new" server timezone however.
nembleton 5.07.2017 15:04:04
@yucer - I don't recommend thinking of it that way. Most consistent usage in a global economy is to convert all datetimes to UTC for storage. By convention, treat Datetime as a UTC field. This does put the burden on all tasks that input datetimes to make the conversion to UTC, but it is a cleaner design long-term. Of course, present the info to user based on their current settings. If user wishes to enter "09:00 in Paris", then let them set Paris time, then enter 09:00. Then anyone who is in New York can easily find what time they need to be awake at their time, to teleconference in.
ToolmakerSteve 28.10.2019 12:48:10

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.)

192
3.01.2013 11:34:55
I don't think this is a good way of thinking. I'd just store and process all the dates in UTC and make sure that the front-end displays it according to the given time-zone. This approach is simple and predictable.
Kos 3.03.2012 10:51:17
@Kos: isn't storing and processing all dates in UTC exactly what TIMESTAMP is doing internally? (Then converting it to display your local timezone?)
carbocation 9.12.2013 05:49:34
my local timezone? How would your DB know my timezone? ;-) There's normally quite some processing between the database and the user interface. I do the localisation only after the whole processing.
Kos 9.12.2013 10:14:23
@Koz: my database doesnt know your databases timezone :! But it does know the timestamp. Your database knows its own timezone setting and applies that when interpreting/representing the timestamp. 1:01am on Dec 11 2013 in Beijing China is not the same moment in time as 1:01am on Dec 11 2013 in Sydney Australia. Google: 'time zones' and 'prime meridian'.
ekerner 10.12.2013 14:35:48
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) dev.mysql.com/doc/refman/5.5/en/datetime.html
Mahdyfo 22.09.2017 18:39:13

TIMESTAMP is always in UTC (that is, elapsed seconds since 1970-01-01, in UTC), and your MySQL server auto-converts it to the date/time for the connection timezone. In the long-term, TIMESTAMP is the way to go because you know your temporal data will always be in UTC. For example, you won't screw your dates up if you migrate to a different server or if you change the timezone settings on your server.

Note: default connection timezone is the server timezone, but this can (should) be changed per session (see SET time_zone = ...).

37
21.05.2019 16:34:11

Depends on application, really.

Consider setting a timestamp by a user to a server in New York, for an appointment in Sanghai. Now when the user connects in Sanghai, he accesses the same appointment timestamp from a mirrored server in Tokyo. He will see the appointment in Tokyo time, offset from the original New York time.

So for values that represent user time like an appointment or a schedule, datetime is better. It allows the user to control the exact date and time desired, regardless of the server settings. The set time is the set time, not affected by the server's time zone, the user's time zone, or by changes in the way daylight savings time is calculated (yes it does change).

On the other hand, for values that represent system time like payment transactions, table modifications or logging, always use timestamps. The system will not be affected by moving the server to another time zone, or when comparing between servers in different timezones.

Timestamps are also lighter on the database and indexed faster.

47
26.10.2010 21:07:28
Your application should not rely on the timezone of the server. An application should ALWAYS select the timezone in the session of the database connection it uses before issuing any query. If a connection is shared between multiple users (ex: webapp), use UTC and do timezone conversion on the rendering side.
dolmen 21.05.2019 16:29:03

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);
6
5.10.2015 17:11:13
Or, use a human readeable and native MySQL datetime, and use native MySQL functions to add / substract datetimes.
Julien Palard 25.04.2013 10:01:07

The below examples show how the TIMESTAMP date type changed the values after changing the time-zone to 'america/new_york' where DATETIMEis 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.

320
5.10.2015 17:15:13
Well, actually the DATETIME effective time changed with the time-zone change, TIMESTAMP didn't change but the human representation did.
flindeberg 4.07.2014 07:56:02
Seems like this command doesn't work in MySQL 5.6 set time_zone="america/new_york";
Manjunath Reddy 13.01.2016 04:23:29
Here is the answer for the set time_zone issue. stackoverflow.com/questions/3451847/mysql-timezone-change
Manjunath Reddy 13.01.2016 06:34:31
Agree with @flindeberg. Timestamp represents correct time, not datetime, after timezone change
Nitin Bansal 10.11.2016 23:45:08

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.

10
5.10.2015 17:16:08

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.

14
5.10.2015 17:19:45

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.

24
5.10.2015 17:21:15

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 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;
17
5.10.2015 17:27:43

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 .

15
5.10.2015 17:30:26
You can (should) always define the timezone at the session level with SET time_zone = '+0:00'; (UTC here) to be sure what you get/set from TIMESTAMP values and avoid depending on the server time_zone default which might change.
dolmen 9.10.2019 14:47:12

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.

13
5.10.2015 17:51:57
based on how you setup your table you can control this behavior. look at dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html
Charles Faiga 7.03.2014 12:03:50
@CharlesFaiga The default behavior is for the timestamp to update when any other column is updated. You have to explicitly turn this off if you want the timestamp to retain its original value
Lloyd Banks 8.03.2014 22:35:55
What is that ?! you must be set the timstamp column to ON UPDATE CURRENT_TIMESTAMP
Accountant م 2.09.2019 18:59:18
This is a feature that you have to enable explicitely when creating the table.
dolmen 9.10.2019 14:49:12
  1. TIMESTAMP is four bytes vs eight bytes for DATETIME.

  2. Timestamps are also lighter on the database and indexed faster.

  3. 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.

  1. DATETIME is constant while TIMESTAMP is effected by the time_zone setting.
95
5.10.2015 17:32:37
You need to clarify #4: Timestamp as stored is constant and non-relative (completely agnostic) to timezone, while the displayed output on retrieval is affected by the timezone of the requesting session. DATETIME is always relative to the timezone it was recorded in, and must always be considered in that context, a responsibility that now falls to the application.
Christopher McGowan 27.01.2016 22:32:21
Great answer. To add to this answer, if we try to store values beyond that ’2038-01-09 03:14:07', we either get an error or it gets stored as ’0000-00-00 00:00:00'. I was getting this error for my database as it has timeshifted values (for encryption purposes).
KarthikS 28.02.2017 06:28:37
Also, thereis a problem with DATETIME with DEFAULT value on versions of mysql below 5.5. dba.stackexchange.com/questions/132951/…
Velaro 25.02.2019 13:42:52

Another difference between Timestamp and Datetime is in Timestamp you can't default value to NULL.

10
5.04.2014 14:39:11
It is obviously wrong. Here is an example: CREATE TABLE t2 ( ts1 TIMESTAMP NULL, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); The first column can accept NULL value.
Ormoz 28.04.2015 09:36:25

The major difference is

  • a INDEX's on Timestamp - works
  • a INDEX's on Datetime - Does not work

look at this post to see problems with Datetime indexing

9
23.05.2017 12:26:36
Both have the same issue if you're selecting with a function based on the column value, and both can be indexed.
Marcus Adams 19.02.2015 19:05:44
Index works on timestamp and does not work on datetime? You drew wrong conclusions from those posts.
Salman A 3.03.2015 04:59:09

I recommend using neither a DATETIME or a TIMESTAMP field. If you want to represent a specific day as a whole (like a birthday), then use a DATE type, but if you're being more specific than that, you're probably interested in recording an actual moment as opposed to a unit of time (day,week,month,year). Instead of using a DATETIME or TIMESTAMP, use a BIGINT, and simply store the number of milliseconds since the epoch (System.currentTimeMillis() if you're using Java). This has several advantages:

  1. You avoid vendor lock-in. Pretty much every database supports integers in the relatively similar fashion. Suppose you want to move to another database. Do you want to worry about the differences between MySQL's DATETIME values and how Oracle defines them? Even among different versions of MySQL, TIMESTAMPS have a different level of precision. It was only just recently that MySQL supported milliseconds in the timestamps.
  2. No timezone issues. There's been some insightful comments on here on what happens with timezones with the different data types. But is this common knowledge, and will your co-workers all take the time to learn it? On the other hand, it's pretty hard to mess up changing a BigINT into a java.util.Date. Using a BIGINT causes a lot of issues with timezones to fall by the wayside.
  3. No worries about ranges or precision. You don't have to worry about what being cut short by future date ranges (TIMESTAMP only goes to 2038).
  4. Third-party tool integration. By using an integer, it's trivial for 3rd party tools (e.g. EclipseLink) to interface with the database. Not every third-party tool is going to have the same understanding of a "datetime" as MySQL does. Want to try and figure out in Hibernate whether you should use a java.sql.TimeStamp or java.util.Date object if you're using these custom data types? Using your base data types make's use with 3rd-party tools trivial.

This issue is closely related how you should store a money value (i.e. $1.99) in a database. Should you use a Decimal, or the database's Money type, or worst of all a Double? All 3 of these options are terrible, for many of the same reasons listed above. The solution is to store the value of money in cents using BIGINT, and then convert cents to dollars when you display the value to the user. The database's job is to store data, and NOT to intrepret that data. All these fancy data-types you see in databases(especially Oracle) add little, and start you down the road to vendor lock-in.

109
13.12.2017 00:44:40
I like this solution. TIMESTAMP expiring in 2038 is a major problem. That's not really so far away!
Charlie Dalsass 12.05.2015 16:10:10
@CharlieDalsass Do you think that current software will be there that time :-P
Habeeb Perwad 21.05.2015 17:09:40
It makes it hard to query data by date if its stored as a number of milliseconds
Ali Saeed 21.12.2015 15:55:33
This is really the best solution if you care about portability and handling users in multiple time zones, or database in different time zone than users. TIMESTAMP could be the way to go if it hasn't design flaws. Too bad that broken solutions got more votes because they were posted early (years!).
German 3.01.2016 01:20:01
Excellent solution! And you're exactly right abt being "locked in". When you get in the habit of letting others "do the work for you", then you start losing the bits and pieces that make YOU the programmer.
fmc 22.02.2017 09:29:44

Reference taken from this Article:

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′

13
7.02.2016 12:49:37

2016 +: what I advise is to set your Mysql timezone to UTC and use DATETIME:

Any recent front-end framework (Angular 1/2, react, Vue,...) can easily and automatically convert your UTC datetime to local time.

Additionally:

(Unless you are likely to change the timezone of your servers)


Example with AngularJs

// back-end: format for angular within the sql query
SELECT DATE_FORMAT(my_datetime, "%Y-%m-%dT%TZ")...

// font-end Output the localised time
{{item.my_datetime | date :'medium' }}

All localised time format available here: https://docs.angularjs.org/api/ng/filter/date

41
26.08.2017 14:54:02
Your data should not be attached to the time zone settings of your servers. Maybe, it's working for your if you have single MySql box under your desk
Jin 6.05.2016 15:58:44
@Jin UTC means no timezone like TIMESTAMP. If all your servers are in UTC there is no problems. Maybe it's not working for you if you have a 2000's config.
Sebastien Horin 6.05.2016 18:05:38
TIMESTAMP may be upgraded to a 64 bit value in the future. Then there is no more problem.
twicejr 21.09.2017 10:58:40
Loading entire third-party libraries to catch something like this can't be that great for performance either, can it? Given that it's client-side and doesn't affect your database... Still, anything you do front-end WILL require serverside checks. So, thinking of the complete picture, does it really help with the speed issue? -- Those benchmarks are a bit strange by the way, I feel. Using a string in a query to compare timestamp fields also feels quite odd. That must also harm performance, right?
NoobishPro 20.12.2017 03:41:53
Do not rely in your application on the time_zone of the server. Instead define the time_zone to use on each database connection: SET time_zone = '+0:00'; for UTC.
dolmen 9.10.2019 14:42:02

I merely use unsigned BIGINT while storing UTC ...

which then still can be adjusted to local time in PHP.

the DATETIME to be selected with FROM_UNIXTIME( integer_timestamp_column ).

one obviously should set an index on that column, else there would be no advance.

3
31.10.2016 00:45:12

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

7
23.06.2016 14:30:00

A lot of answers here suggest to store as timestamp in the case you have to represent well defined points in time. But you can also have points in time with datetime if you store them all in UTC by convention.

3
28.06.2016 07:50:40
Can you give some reasons why I should prefer this instead of using timestamp?
Jasir 12.08.2016 08:02:53
@Jasir not really, it seems a big debate. I was only pointing out that datetime can represent specific points in time if there is a convention to store all datetimes in a specific timezone (i.e. UTC). Personally, I would store everything in datetime, timestamp being limited to 2038 (even though I guess they will eventually do something about it), and if I look at some raw data in DB, datetime will be readable without having to apply conversion. Again, this is very subjective.
Matthew 12.08.2016 11:41:27
@Matthew But if the session time_zone is not UTC too, you will have surprises (bugs) if you use MySQL date/time functions such as NOW(), DATE_ADD without converting timezone first... So why make database interaction more complicated than necessary?
dolmen 9.10.2019 15:06:10
@dolmen the idea is that server-side everything is UTC. MySQL session time zone included. This should be set as the server's time zone which should be UTC
Matthew 10.10.2019 01:54:28

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

5
27.10.2016 07:59:30

Not mentioned so far, is that DEFAULT CURRENT_TIMESTAMP only works with Timestamp, but not DateTime type fields.

This becomes relevant for MS Access tables which can only use DateTime but not Timestamp.

3
3.11.2016 19:27:00
The DEFAULT CURRENT_TIMESTAMP support for a DATETIME is added in MySQL 5.6.
Sarath Sadasivan Pillai 27.03.2017 07:59:18
"Any of the synonyms for CURRENT_TIMESTAMP have the same meaning as CURRENT_TIMESTAMP. These are CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP()." -> mysql docs
CPHPython 26.04.2018 15:20:21