Питання Чи потрібно використовувати тип даних datetime або timestamp у MySQL?


Ви б рекомендували використовувати дата, час або a часовий покажчик поле, і чому (використовуючи MySQL)?

Я працюю з PHP на стороні сервера.


2304
2018-01-03 16:14


походження


це має певну релевантну інформацію codeproject.com/Tips/1215635/MySQL-DATETIME-vs-TIMESTAMP - Waqleh


Відповіді:


Тимчасові мітки в MySQL, як правило, використовуються для відстеження змін у записах і часто оновлюються щоразу, коли змінюється запис. Якщо ви хочете зберегти певне значення, слід використовувати поле datetime.

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


1559
2018-01-03 16:26



Важлива відмінність полягає в тому, що DATETIME представляє дату (як знайдено в календарі) і час (як це можна спостерігати на настінні годинники), в той час як TIMESTAMP являє собою добре визначений момент часу. Це може бути дуже важливо, якщо ваша програма обробляє часові пояси. Як давно було "2010-09-01 16:31:00"? Це залежить від того, до якого часового поясу ви перебуваєте. Для мене це було всього кілька секунд тому, оскільки для вас це може означати час у майбутньому. Якщо я кажу 1283351460 секунд з "1970-01-01 00:00:00 UTC", ви точно знаєте, який момент часу я говорю. (Див. Відмінну відповідь Ніра нижче). [Недолік: діючий діапазон]. - MattBianco
Ще одна різниця: запити з "рідною" датою не будуть кешовані, але запити з міткою часу буде. - OZ_
"Тиставки в MySQL зазвичай використовуються для відстеження змін до записів" Не думайте, що це хороша відповідь. Тимчасова мітка є набагато потужнішою та складнішою, ніж те, що називають MattBianco та Nir. Хоча друга частина відповіді дуже хороша. Це правда, що сказав Blivet, і це хороший порад. - santiagobasulto
Також 1 важлива примітка, DATETIME та TIMESTAMP можуть використовувати CURRENT_TIMESTAMP, NOW () з повагою, оскільки це за замовчуванням, але DATE, наприклад, не може, тому що він використовує '0000-00-00' за умовчанням, тому для вирішення цього питання U повинен написати Ваш власний тригер до цієї таблиці, щоб вставити поточну дату (без часу) у поле / col з типом mysql DATE. - Arthur Kushman
@DavidHarkness: Документація говорить: "Щоб вказати автоматичні властивості, використовуйте предмети CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP та UPDATE CURRENT_TIMESTAMP" dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html - Plap


У MySQL 5 і вище TIMESTAMP значення перетворюються з поточного часового поясу на UTC для зберігання і перетворюються з UTC до поточного часового поясу для отримання. (Це трапляється тільки для типу даних TIMESTAMP та ні для інших типів, таких як DATETIME.)

За замовчуванням поточним часовим поясом для кожного з'єднання є час роботи сервера. Часовий пояс можна встановити на основі кожного з'єднання, як описано в Підтримка часового поясу сервера MySQL.


816
2018-03-02 11:49



Джерело: dev.mysql.com/doc/refman/5.1/en/datetime.html - Andy0708
це OReilly презентація дуже добре для цієї теми (PDF вибачте) cdn.oreillystatic.com/uk/assets/1/event/36/... - gcb
Його також про природу заходу: - відеоконференція (TIMESTAMP). Всі супроводжуючі повинні бачити посилання на абсолютну мить часу, пристосовану до його часового поясу. - Місцевий час завдання (DATETIME), я повинен виконати це завдання 31.03.2013 о 9:00 ранку, якщо в той день я працюю в Нью-Йорку чи Парижі. Я почну працювати о 8 годині ранку за місцевим часом місця, який я буду в той день. - yucer
Тож якщо я ЗМІНИВ часовий пояс сервера, то значення TIMESTAMP залишатимуться однаковими або змінюватимуться теж? - Andrew
@Andrew, оскільки це UTC, воно залишиться UTC. Зворотне перетворення зміниться на "новий" часовий пояс сервера, однак. - nembleton


Я завжди використовую поля 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 до '2038-01-09 03:14:07' UTC. Вона має різні властивості залежно від версії MySQL та режиму SQL, на якому працює сервер.

Ви цілком імовірно потрапите на нижчу межу TIMESTAMP у загальному користуванні - наприклад, зберігання дати народження.


430
2018-01-04 04:26



ви також можете потрапити в верхній легко обмежуватись, якщо ви перебуваєте в банківській справі або нерухомості ... 30-річна іпотека виходить за межі 2038 року - Kip
Звичайно, використовуйте 64-розрядні позначки часу Unix. Наприклад, в Java, new Date().getTime() вже дає вам 64-бітове значення. - osa
+1 для DATETIME: у нашому потоці даних, з SQL Server фізичного магазину для покупки та рахунку-фактури, переведеного на сервер MySQL для віртуального магазину на веб-сторінці, DATETIME краще для Date-Time модифікації, оскільки цей тип даних також існує в Transact -SQL Але TIMESTAMP означає інше у Transact-SQL, це Binary як ROWVERSION, хоча MySQL TIMESTAMP - подібний до DateTime. Тому ми уникаємо використання TIMESTAMP для сумісності двох БД. - jacouh
Не маю уявлення. Це набагато більша проблема, ніж просто MySQL, і немає простого виправлення: en.wikipedia.org/wiki/Year_2038_problem Я не вірю, що MySQL може просто оголосити, що тимчасові мітки зараз 64-розрядні і припускають, що все буде добре. Вони не контролюють апаратне забезпечення. - scronide
Дата народження може бути DATETIME, якщо ви знаєте час народження, як і для мого. - Kris Craig


Наведені нижче приклади показують, як це 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 проти Timestamp.


284
2017-08-21 08:45



Ну, власне DATETIME ефективний час змінився з зміною часової зони, TIMESTAMP не змінився, але людське уявлення. - flindeberg
Схоже, ця команда не працює в MySQL 5.6 set time_zone="america/new_york"; - Manjunath Reddy
Ось відповідь на задану проблему time_zone. stackoverflow.com/questions/3451847/mysql-timezone-change - Manjunath Reddy
Погодьтеся з @flindeberg. Часовий пояс відображає правильний час, а не дату, після зміни часового поясу - Nitin Bansal


Основна відмінність полягає в тому, що DATETIME є постійним, тоді як на TIMESTAMP це впливає time_zone налаштування

Так що це має значення лише тоді, коли у вас є - або може, у майбутньому - синхронізовані кластери в часових поясах.

Прості слова: Якщо у мене є база даних в Австралії та зробіть дамп цієї бази даних, щоб синхронізувати / заповнити базу даних в Америці, тоді TIMESTAMP буде оновлюватися, щоб відобразити реальний час події в новому часовому поясі, тоді як DATETIME все одно буде відображати час події у часовому поясі.

Відмінним прикладом використання DATETIME, де TIMESTAMP слід було використовувати, є на Facebook, де їхні сервери ніколи не зовсім впевнені в тому, що час траплявся через часові пояси. Одного разу я мав розмову, в якій час говорив, що я відповідаю на повідомлення, перш ніж повідомлення було надіслано. (Це, звичайно, могло також бути викликане перекладом поганого часового поясу в програмі обміну повідомленнями, якщо часи було опубліковано, а не синхронізовано.)


169
2018-01-14 14:26



Я не думаю, що це хороший спосіб мислення. Я просто зберігаю і оброблю всі дати в UTC і переконайтеся, що передній кінець відображає його відповідно до заданої часової зони. Цей підхід є простим та передбачуваним. - Kos
@Kos: не зберігає та не обробляє всі дати в UTC саме те, що TIMESTAMP робить всередині? (Тоді перетворивши його на місцевий часовий пояс?) - carbocation
мій місцевий часовий пояс? Яким би БД не знав мого часового поясу? ;-) Зазвичай досить багато обробки між базою даних та користувальницьким інтерфейсом. Я здійснюю локалізацію лише після всієї обробки. - Kos
@ Коз: моя база даних не знає часових поясів вашої бази даних:! Але це знає мітку часу. Ваша база даних знає власний часовий пояс і застосовує це при інтерпретації / представленні позначки часу. 1:01 ранку 11 грудня 2013 року в Пекіні Китай не той самий момент, як 1:01 ранку 11 грудня 2013 року в Сіднеї, Австралія. Google: "часові пояси" та "перший меридіан". - ekerner
Різниця полягає не лише в часових поясах інших серверів кластера. Відповідний параметр - визначений часовий пояс (за умовчанням той же самий сервер, але за замовчуванням він може залежати від драйвера сторони клієнта) у з'єднанні з будь-яким клієнтом MySQL. - dolmen


Я приймаю це рішення на семантичну базу.

Я використовую мітку часу, коли мені потрібно записати (більш-менш) фіксовану точку в часі. Наприклад, коли запис була вставлена ​​в базу даних або коли відбулася деяка дія користувача.

Я використовую поле datetime, коли дату / час можна встановити та змінити довільно. Наприклад, коли користувач може пізніше зберегти зміну зустрічей.


109
2018-01-03 17:11



timestamp-фіксована точка у часі, Координована універсальна дата часу - відносно точки зору, до часу посилання (ej часовий пояс місцевого часу), може бути .. Координація місцевого часу? - yucer


TIMESTAMP становить 4 байти від 8 байт за DATETIME.

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

Але, як і Scronide, він має нижчу межу 1970 року. Це чудово для всього, що може статися в майбутньому, хоча;)


86
2018-01-04 09:00



Майбутнє закінчується 2038-01-19 03:14:07 UTC. - MattBianco
Це дурень. Я думав, що тип TIMESTAMP буде "майбутнім готовий", оскільки він відносно новий. Ви не можете перешкодити перетворенню datetime на UTC і назад кожного разу, коли ви працюєте з різними часовими поясами. Вони повинні були зробити TIMESTAMP більшим .. принаймні 1 байт більше. це додасть 68 * 255 = 17340 років ... хоча це не буде 32-бітним вирівнюванням. - NickSoft
Чи знаєте ви, чому TIMESTAMP закінчується в 2038 році? Тому що це ціле число, а цілі числа мають ліміт, який є 2147483647. Я думаю, що це причина. Може бути, якщо вони змінять свій тип на варшар і змінять спосіб маніпулювати ним, це буде "майбутнє". - vinsa
@Вінса, я не думаю, що це буде майбутнє, готовий до того часу. Досі пам'ятаєте помилку Y2K? 2038 року. - Pacerier
До 2038 року MySQL (якщо він все ще існує) просто оновить поле TIMESTAMP, щоб використовувати більше 4 байт і дозволити ширший діапазон - the_nuts


  1. TIMESTAMP - це чотири байти проти вісім байт для DATETIME.

  2. Тимчасові мітки також легші в базі даних та швидше індексовані.

  3. Тип 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 до '2038-01-09 03:14:07' UTC. Вона має різні властивості залежно від версії MySQL та режиму SQL, на якому працює сервер.

  1. DATETIME є постійним, тоді як TIMESTAMP здійснюється за допомогою параметра time_zone.

80
2017-12-21 11:12



Потрібно вказати номер 4: збережений часовий пояс є постійним і не відносним (повністю агностичним) до часового поясу, тоді як відображуваний вихід на пошук залежить від часового поясу запитуючого сеансу. DATETIME завжди залежить від часового поясу, який він записав, і завжди слід розглядати в цьому контексті відповідальність, яка зараз потрапляє до програми. - Christopher McGowan
Відмінна відповідь. Щоб додати до цієї відповіді, якщо ми намагатимемося зберегти значення, що перевищують значення «2038-01-09 03:14:07», ми або отримуємо помилку, або зберігається як '0000-00-00 00:00:00'. Я отримав цю помилку в моїй базі даних, оскільки має часовий час (для цілей шифрування). - Earnest_learner


Я рекомендую використовувати ні поле DATETIME або TIMESTAMP. Якщо ви хочете представляти певний день в цілому (наприклад, день народження), тоді використовуйте тип DATE, але якщо ви більш точні, ніж це, ви, напевно, зацікавлені в реєстрації фактичної хвилини, а не одиниці час (день, тиждень, місяць, рік). Замість того, щоб використовувати DATETIME або TIMESTAMP, використовуйте BIGINT і просто зберігайте кількість мілісекунд з епохи (System.currentTimeMillis (), якщо ви використовуєте Java). Це має кілька переваг:

  1. Ви уникаєте входу в систему постачальника. Практично кожна база даних підтримує цілі числа відносно аналогічним чином. Припустімо, ви хочете перейти до іншої бази даних. Ви хочете турбуватися про відмінності між значеннями MySQL DATETIME та тим, як Oracle їх визначає? Навіть серед різних версій MySQL, TIMESTAMPS мають різний рівень точності. Лише зовсім недавно MySQL підтримував мілісекунди в мітках часу.
  2. Немає часових поясів. Тут було кілька цікавих коментарів щодо того, що відбувається з часовими точками з різними типами даних. Але чи це загальне знання, і чи будуть ваші співробітники все витрачати час, щоб це вчитися? З іншого боку, досить важко перешкодити зміні BigINT на java.util.Date. Використання BIGINT викликає багато проблем з тимчасовими зонами, що потрапляють назовні.
  3. Не хвилюйтеся про діапазони або точність. Вам не доведеться турбуватися про те, що скоротити в майбутніх діапазонах дат (TIMESTAMP лише до 2038).
  4. Інтеграція сторонніх інструментів. Використовуючи ціле, це тривіальне для сторонніх інструментів (наприклад, EclipseLink) для взаємодії з базою даних. Не кожен сторонній інструмент має однакове розуміння "datetime", як це робить MySQL. Хочете спробувати з'ясувати, чи слід використовувати об'єкт java.sql.TimeStamp або java.util.Date у Hibernate, якщо ви використовуєте ці спеціальні типи даних? Використання базових типів даних робить використання тривимірними інструментами сторонніх розробників.

Це питання тісно пов'язане з тим, як слід зберігати в базі грошову вартість (тобто 1,99 долара США). Якщо ви використовуєте десяткове число, або тип грошової банки, або, найгірший, подвійний? Всі 3 з цих варіантів є жахливими, з багатьох причин, перерахованих вище. Рішення полягає в тому, щоб зберегти вартість грошей в центрах за допомогою BIGINT, а потім конвертувати центри в долари, коли відобразити значення для користувача. Робота бази даних полягає в тому, щоб зберігати дані, а НІ - щоб не переплутати ці дані. Всі ці вигадані типи даних, які ви бачите в базах даних (особливо Oracle), додає мало, і починаєш рухатись від блокування постачальника.


75
2018-06-11 23:02



Мені подобається це рішення. Тимчасовий термін TIMESTAMP, який закінчується 2038 р., Є головною проблемою. Це не так далеко! - Charlie Dalsass
@CharlieDalsass Ви вважаєте, що поточне програмне забезпечення буде там: -P - Habeeb Perwad
я в проблеми з TIMESTAMP. Ця альтернатива BIGINT виглядає кращою. - KcFnMi
Це ускладнює запит даних за датою, якщо воно зберігається як число мілісекунд - Ali Saeed
Це дійсно найкраще рішення, якщо ви зацікавлені в мобільності та обробці користувачів у декількох часових поясах або в базі даних у різний часовий пояс, ніж у користувачів. TIMESTAMP може бути шлях, якщо він не має дизайнерських недоліків. Надто погано, що розбиті рішення отримали більше голосів, тому що вони були розміщені рано (роки!). - German


Залежить від програми, дійсно.

Подумайте про встановлення мітки часу для користувача на сервері в Нью-Йорку для зустрічі в Санхеі. Тепер, коли користувач підключається до Sanghai, він отримує таку саму позначку часу призначення від дзеркального сервера в Токіо. Він побачить зустріч в токійський час, компенсується від оригінального Нью-Йоркського часу.

Тому для дат, які вказують час користувача, наприклад, призначення або графік, датате час краще. Це дозволяє користувачеві керувати точною бажаною датою та часом, незалежно від налаштувань сервера. Встановлений час - це встановлений час, який не впливає на часовий пояс сервера, часовий пояс користувача або зміни у способі обчислення літнього часу (так, це змінюється).

З іншого боку, для значень, які відображають системний час, такі як платіжні транзакції, модифікації таблиці або ведення журналу, завжди використовуються мітки часу. Система не впливатиме на переміщення сервера до іншого часового поясу або при порівнянні серверів у різних часових поясах.

Тимчасові мітки також легші в базі даних та швидше індексовані.


37
2017-10-26 21:07