我应该在 MySQL 中使用 datetime 还是 timestamp 数据类型？

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

I'm working with PHP on the server side.

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

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.

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

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

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.

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

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.

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

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

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.

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

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

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 .

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.

  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.

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

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

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.

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′

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

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.

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

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

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

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: