Вопрос: Должен ли я использовать тип данных datetime или timestamp в MySQL?


Вы бы рекомендовали использовать Дата и время или отметка времени поле и почему (используя MySQL)?

Я работаю с PHP на стороне сервера.


2265


источник


Ответы:


Временные метки в MySQL обычно используются для отслеживания изменений в записях и часто обновляются каждый раз, когда запись изменяется. Если вы хотите сохранить определенное значение, вы должны использовать поле datetime.

Если вы имели в виду, что хотите решить, используя временную метку UNIX или собственное поле datetime MySQL, перейдите в собственный формат. Вы можете делать вычисления в MySQL таким образом ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)")и просто изменить формат значения на отметку времени UNIX ("SELECT UNIX_TIMESTAMP(my_datetime)")когда вы запрашиваете запись, если хотите работать с ней с помощью PHP.


1537



В MySQL 5 и выше, TIMESTAMP значения преобразуются из текущего часового пояса в UTC для хранения и преобразуются обратно из UTC в текущий часовой пояс для извлечения. (Это происходит только для типа данных TIMESTAMP и не для других типов, таких как DATETIME.)

По умолчанию текущий часовой пояс для каждого соединения - это время сервера. Часовой пояс может быть установлен для каждого соединения, как описано в Поддержка часовых поясов сервера MySQL ,


806



Я всегда использую поля DATETIME для чего-либо другого, кроме метаданных строк (дата создана или изменена).

В виде упомянутый в документации MySQL:

Тип DATETIME используется, когда вам нужны значения, которые содержат информацию о дате и времени. MySQL извлекает и отображает значения DATETIME в формате «YYYY-MM-DD HH: MM: SS». Поддерживаемый диапазон: «1000-01-01 00:00:00» до «9999-12-31 23:59:59».

...

Тип данных TIMESTAMP имеет диапазон '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Он имеет разные свойства, в зависимости от версии MySQL и режима SQL, на котором работает сервер.

Вы, скорее всего, попадете в нижний предел для TIMESTAMPs в общем использовании - например, хранение даты рождения.


427



В приведенных ниже примерах показано, как TIMESTAMPтип даты изменил значения после изменения time-zone to 'america/new_york'где DATETIMEне меняется.

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

Я превратил свой ответ в статью, чтобы все люди могли найти это полезное, MySQL: Datetime Vs Timestamp Типы данных ,


281



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


168



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.


108



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


85



  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.

79



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.


68



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.


35