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

Asked 2023-09-20 20:20:18 View 958,499

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

I'm working with PHP on the server side.

Answers

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.

Answered   2023-09-20 20:20:18

  • 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]. - anyone
  • Another one difference: queries with "native" datetime will not be cached, but queries with timestamp - will be. - anyone
  • "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. - anyone
  • TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. It is the year 2022 now, 2038 is a near future. What will happen then? - anyone
  • This answer is completely wrong in every way. - anyone

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.

Answered   2023-09-20 20:20:18

  • this OReilly presentation is very good for this topic (PDF sorry) cdn.oreillystatic.com/en/assets/1/event/36/… - anyone
  • 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. - anyone
  • Yes, and this is just horrible. The DBMS should never, ever convert timestamps in any direction nor taking the current DB's system time into account. It should just save the internal timestamp as it is (ms since epoch). When rendering the value (in the very last moment) the value should be presented (!) in the user's timezone from the application. Everything else is just pure pain. If any, the DBMS should support explicit types for local and absolute time where 'local' is something like your birthday or 'noon' and 'absolute' is something like the start time of a rocket. - anyone

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.

Answered   2023-09-20 20:20:18

  • you can also hit the upper limit easily if you are in banking or real estate... 30-year mortgages go beyond 2038 now - anyone
  • Of course, use 64-bit Unix timestamps. For example, in Java, new Date().getTime() already gives you a 64-bit value. - anyone
  • 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. - anyone
  • So timestamp is stored internally (presumably) as a Unix timestamp - an integer. It's quick to insert and retrieve, but propably slower when applying date fucntions to it, as it will need converting to an internal datetime format to do this. So use timestamp for simple logging, where it will be fast, but less flexible and with a smaller range. - anyone

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.

Answered   2023-09-20 20:20:18

  • Well, actually the DATETIME effective time changed with the time-zone change, TIMESTAMP didn't change but the human representation did. - anyone

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

Answered   2023-09-20 20:20:18

  • 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. - anyone
  • @Kos: isn't storing and processing all dates in UTC exactly what TIMESTAMP is doing internally? (Then converting it to display your local timezone?) - anyone
  • 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. - anyone
  • @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'. - anyone
  • yes, TIMESTAMP effectively stores (a limited range) times in UTC. but then effectively hides it from you. highly recommend only using datetime and only storing UTC, and avoiding all functions that use the connection timezone (now, current_date, unix_timestamp, etc). - anyone

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.

Answered   2023-09-20 20:20:18

  • 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? - anyone
  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.

  4. DATETIME is constant while TIMESTAMP is affected by the time_zone setting.

Answered   2023-09-20 20:20:18

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

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.

Answered   2023-09-20 20:20:18

  • I like this solution. TIMESTAMP expiring in 2038 is a major problem. That's not really so far away! - anyone
  • It makes it hard to query data by date if its stored as a number of milliseconds - anyone
  • This should be the accepted answer. I despise any one of the "native" date formats. Endless time zone catches etc. - anyone
  • @Merc no, it shouldn't be. Semantics in data structures are a thing for a reason. Database engines have semantic types also for a reason. If you don't care about semantics at all, why do you use a RDBMS (relational -> based on semantic relations between data) in the first place? - anyone
  • @lukasz032 yes it should be. Semantics are a good thing, but when you add to the mix timezones, limitations (2038 anyone?), Messy implementation and ambiguity, then no -- real world projects will use a bigint thank you very much - anyone

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

Answered   2023-09-20 20:20:18

  • The future ends at 2038-01-19 03:14:07 UTC. - anyone
  • For MySQL 8.0.28 and later running on 64-bit platforms, the valid range of argument values for UNIX_TIMESTAMP() is '1970-01-01 00:00:01.000000' UTC to '3001-01-19 03:14:07.999999' UTC (corresponding to 32536771199.999999 seconds). - anyone

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

Insert a default value (thx Brad):

ALTER TABLE good_times MODIFY a_time BIGINT DEFAULT (UNIX_TIMESTAMP());

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.

Answered   2023-09-20 20:20:18

  • @Marko that argument applies equally well to storing datetime values into a database as a VARCHAR: your application code should convert the value to the desired format before it inserts into the database. My opinion is that databases are made to store data including all context necessary for that data to have meaning. - anyone
  • Here is a MySQL built-in function applying to an ISO 8601 VARCHAR value: SELECT DATE_ADD("2020-01-01", INTERVAL 2 DAY). Here is a MySQL built-in date comparison operator applying ISO 8601 VARCHARs: SELECT "2020-01-01" < "2020-03-01" - anyone
  • I still don't understand the point of this. If you store any date in UTC, and run all your servers in UTC, you have a clear understanding of your data. Thus, you can serve dates to clients as 2022-01-23T09:59:56+00:00, and they can apply formatting according to the users' timezone. What am I missing here? - anyone
  • Because some database administrator don't have full control over which server their application runs on. Maybe your database is sent back to QA/DEV and they have a different timezone, things break. I had a server in UTC-8 timezone, everything was fine. But when it restarted it was suddenly UTC. I have root access there and this change was unexpected. Using BIGINT never breaks ever. - anyone
  • Heres stidbit for you, auto genreated timestamp for bigint. I was struggling with this and found mysql 8 lets you use unix_timestamp() as a default if you add extra () around it. Would really complete the answer if you add this I think. - anyone

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.

Answered   2023-09-20 20:20:18

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

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

Answered   2023-09-20 20:20:18

  • 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 - anyone

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

Answered   2023-09-20 20:20:18

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.

Answered   2023-09-20 20:20:18

  • No, the difference is not just "a special case". Because timezones of the session that sets/queries the values are involved differently. - anyone

Comparison between DATETIME, TIMESTAMP and DATE

enter image description here

What is that [.fraction]?

  • A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. This is of course optional.

Sources:

Answered   2023-09-20 20:20:18

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.

Answered   2023-09-20 20:20:18

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

Answered   2023-09-20 20:20:18

  • -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. - anyone
  • Haven't there been benchmarks showing that sorting in MySQL is slower than in php? - anyone
  • well it depends, sometimes it good to use it when we like to not use strtotime. ( php5.3 dep ) - anyone
  • you can push the logic into mysql by just using FROM_UNIXTIME if needed - anyone
  • 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. - anyone

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;

Answered   2023-09-20 20:20:18

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 .

Answered   2023-09-20 20:20:18

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

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.

Answered   2023-09-20 20:20:18

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.

Answered   2023-09-20 20:20:18

  • 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 - anyone
  • @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 - anyone
  • What is that ?! you must be set the timstamp column to ON UPDATE CURRENT_TIMESTAMP - anyone
  • This is a feature that you have to enable explicitely when creating the table. - anyone
  • I feel lucky to have read this answer - anyone

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′

Answered   2023-09-20 20:20:18

In my case, I set UTC as a time zone for everything: the system, the database server, etc. every time that I can. If my customer requires another time zone, then I configure it on the app.

I almost always prefer timestamps rather than datetime fields, because timestamps include the timezone implicitly. So, since the moment that the app will be accessed from users from different time zones and you want them to see dates and times in their local timezone, this field type makes it pretty easy to do it than if the data were saved in datetime fields.

As a plus, in the case of a migration of the database to a system with another timezone, I would feel more confident using timestamps. Not to say possible issues when calculating differences between two moments with a sumer time change in between and needing a precision of 1 hour or less.

So, to summarize, I value this advantages of timestamp:

  • ready to use on international (multi time zone) apps
  • easy migrations between time zones
  • pretty easy to calculate diferences (just subtract both timestamps)
  • no worry about dates in/out a summer time period

For all this reasons, I choose UTC & timestamp fields where posible. And I avoid headaches ;)

Answered   2023-09-20 20:20:18

  • timestamp data will be fun for the person supporting your application when January 20, 2038 arrives. tick tock tick tock - anyone
  • Timestamp type could then be increased by a bit and double the possible values. - anyone
  • Test your theory to 'increase by a bit' and see if you can successfully store February 1, 2038 and retrieve it with MySQL. Let's see your table definition when finished, please. You are probably going to have a lot of unhappy past acquaintances in February, 2038. - anyone
  • @WilsonHauck You'll have to upgrade the MySQL version long before 2038 anyway. And that extended TIMESTAMP will be handled by the migration. Also, few applications besides handling mortgages really need to care about 2038 right now. - anyone
  • @dolmen lots of professional-grade tools and materials have a 20+ year warranty. So something like warranties.expires_at could not be a MySQL timestamp today. - anyone
+---------------------------------------------------------------------------------------+--------------------------------------------------------------------------+
|                                       TIMESTAMP                                       |                                 DATETIME                                 |
+---------------------------------------------------------------------------------------+--------------------------------------------------------------------------+
| TIMESTAMP requires 4 bytes.                                                           | DATETIME requires 8 bytes.                                               |
| Timestamp is the number of seconds that have elapsed since January 1, 1970 00:00 UTC. | DATETIME is a text displays 'YYYY-MM-DD HH:MM:SS' format.                |
| 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′ |
| TIMESTAMP during retrieval converted back to the current time zone.                   | DATETIME can not do this.                                                |
| TIMESTAMP is used mostly for metadata i.e. row created/modified and audit purpose.    | DATETIME is used mostly for user-data.                                   |
+---------------------------------------------------------------------------------------+--------------------------------------------------------------------------+

Answered   2023-09-20 20:20:18

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.

Answered   2023-09-20 20:20:18

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′

Answered   2023-09-20 20:20:18

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

Answered   2023-09-20 20:20:18

  • Both have the same issue if you're selecting with a function based on the column value, and both can be indexed. - anyone
  • Index works on timestamp and does not work on datetime? You drew wrong conclusions from those posts. - anyone

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.

Answered   2023-09-20 20:20:18

  • what if there are fields that use date, and other field uses timestamp in one table. I think it will cause a new problem because the data that is filtered by where is not in accordance with Timezone changes. - anyone
  • @ErlangP In that case your application should fix the timezone problem on the date column before sending the query. - anyone

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

Answered   2023-09-20 20:20:18

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

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.

Answered   2023-09-20 20:20:18