Трябва ли да използвам типа данни във времето или датата в MySQL?

Бихте ли препоръчали използването на datetime или timestamp и защо (използвайки MySQL)?

Работя с php на сървърната страна.

2422
03 янв. зададен от karlipoppins 03 януари 2009-01-03 19:14 '09 в 19:14 2009-01-03 19:14
@ 35 отговора
  • 1
  • 2

MySQL timestamps обикновено се използват за проследяване на промените в записите и често се актуализират всеки път, когато записът се променя. Ако искате да запазите определена стойност, трябва да използвате полето за дата-час.

Ако сте имали предвид, че искате да избирате между използването на UNIX timestamp или полето за дата и час на MySQL, отидете във вашия собствен формат. Можете да извършвате изчисления в MySQL по този начин ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") и просто да промените формата на стойността на UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") когато искате да работите с него с използване на PHP.

1642
03 янв. отговорът е даден blivet Jan 03 2009-01-03 19:26 '09 в 19:26 2009-01-03 19:26

В MySQL 5 и по-горе, TIMESTAMP стойностите се преобразуват от текущата часова зона в UTC за съхранение и се преобразуват обратно от UTC в текущата часова зона за извличане. (Това се случва само за типа данни TIMESTAMP, а не за други типове като DATETIME.)

border=0

По подразбиране текущата часова зона за всяка връзка е сървърно време. Часовата зона може да бъде зададена за всяка връзка, както е описано в поддръжката на часовата зона на MySQL сървъра .

844
02 марта '09 в 14:49 2009-03-02 14:49 отговорът е даден Nir 02 март '09 в 14:49 2009-03-02 14:49

Винаги използвам полетата 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 режима, на който сървърът работи.

Най-вероятно ще попаднете в долната граница за TIMESTAMPs като цяло - например. дата на раждане.

457
04 янв. отговорът е даден scronide 04 jan. 2009-01-04 07:26 '09 в 7:26 AM 2009-01-04 07:26

По-долу са дадени примери за това как типът на датата 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 Versus Типове данни за време .

295
21 авг. Отговор даден mr_eclair 21 Aug. 2011-08-21 11:45 '11 в 11:45 2011-08-21 11:45

Основната разлика е, че DATETIME е константа, а TIMESTAMP зависи от параметъра time_zone .

Така че има значение само когато имате - или може да имате в бъдеще; синхронизирани клъстери в часовите зони.

С прости думи: Ако имам база данни в Австралия и изхвърлям тази база данни, за да синхронизирам / запълня базата данни в Америка, тогава TIMESTAMP ще се актуализира, за да показва реалното време на събитието в нова часова зона, докато DATETIME ще бъде все още отразяват времето на събитието в часовата зона .

Чудесен пример за DATETIME, използван там, където е трябвало да се използва TIMESTAMP, е Facebook, където техните сървъри никога не могат да бъдат сигурни какво се е случило по време на часовите зони. Веднъж имах разговор, в който време казах, че отговарям на съобщенията, преди съобщението да бъде изпратено. (Това, разбира се, може да е причинено и от неправилен превод на часовата зона в софтуера за съобщения, ако времето е изпратено, а не синхронизирано.)

179
14 янв. отговорът е даден от ekerner 14 януари. 2010-01-14 17:26 '10 в 17:26 2010-01-14 17:26

Това решение вземам на семантична основа.

Използвам клеймо, когато трябва да записвам (повече или по-малко) фиксирана точка във времето. Например, когато записът е бил вмъкнат в базата данни или когато възникне някакво действие от страна на потребителя.

Използвам полето за дата-час, когато датата / времето могат да бъдат зададени и променени произволно. Например, когато потребителят може да запази последващи промени в среща.

114
03 янв. отговорът е даден непознат 03 януари 2009-01-03 20:11 '09 в 20:11 2009-01-03 20:11

TIMESTAMP - 4 байта с 8 байта за DATETIME.

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

Но точно както Скронид каза, че има долната граница от 70-те години. Въпреки това е чудесно за всичко, което може да се случи в бъдеще;)

90
04 янв. отговорът е даден Алекс 04 януари. 2009-01-04 12:00 '09 в 12:00 2009-01-04 12:00

Препоръчвам да не използвате нито полето 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, ако използвате тези персонализирани типове данни? Използването на основните типове данни прави използването на инструменти на трети страни тривиални.

Този проблем е тясно свързан с начина, по който трябва да съхранявате паричната стойност (например $ 1.99) в базата данни. Трябва ли да използвам база данни с десетичен знак или тип „Пари“ или, най-лошото, двойна? Всичките три варианта са ужасни поради много от горните причини. Решението е да се съхранява стойността на парите в цента с помощта на BIGINT, а след това да се конвертират цента в долари, когато се показва стойността на потребителя. Задачата на базата данни е да съхранява данни и НЕ интерпретира тези данни. Всички тези странни типове данни, които виждате в базите данни (особено в Oracle), добавят малко и започвате да следвате пътя на сцепването с доставчици.

87
12 июня '14 в 2:02 2014-06-12 02:02 отговорът е даден от user64141 12 юни, 14 в 2:02 2014-06-12 02:02
  • TIMESTAMP е четири байта спрямо осем байта за DATETIME.

  • Времевите отметки също са по-леки в базата данни и се индексират по-бързо.

  • Типът 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.
86
21 дек. Отговорът е даден Vivek S 21 dec. 2013-12-21 14:12 '13 в 14:12 2013-12-21 14:12

Зависи от приложението.

Помислете за задаване на времева клейма за потребител на сървър в Ню Йорк, която да бъде зададена на Sangha. Сега, когато потребителят се свърже със Sangha, той получава достъп до една и съща метеорологична марка на дестинация от огледален сървър в Токио. Той ще види назначението в Токио, компенсирано от първоначалното време в Ню Йорк.

Така за стойности, които представляват потребителско време, например среща или график, datetime е по-добре. Тя позволява на потребителя да контролира точната дата и час, независимо от настройките на сървъра. Определеното време е зададеното време, независимо от часовата зона на сървъра, часовата зона на потребителя или промяна в метода за изчисляване на лятното часово време (да, това е вярно).

От друга страна, за стойности, представляващи системно време, като платежни транзакции, промени в таблиците или журналиране, винаги използвайте времеви печати. Системата няма да бъде засегната от преместването на сървъра в различна часова зона или при сравняване на сървъри в различни часови зони.

Времевите отметки също са по-леки в базата данни и се индексират по-бързо.

40
27 окт. отговор, даден ianaré 27 октомври 2010-10-27 00:07 '10 в 0:07 2010-10-27 00:07

2016 +: Съветвам ви да зададете часовата зона на Mysql до UTC и да използвате DATETIME:

Всяка съвременна интерфейсна среда (Angular 1/2, Responsive, Vue, ...) може лесно и автоматично да преобразува UTC и датата в местно време.

В допълнение:

(Ако вероятно не променяте часовата зона на сървърите си)


Пример за 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' }} 

Целият локализиран формат на времето можете да намерите тук: https://docs.angularjs.org/api/ng/filter/date

36
18 февр. Отговорът е даден от Себастиен Хорин на 18 февруари. 2016-02-18 01:36 '16 в 1:36 2016-02-18 01:36
поле

timestamp е специален случай на полето datetime . Можете да създадете колони с timestamp печати, които да имат специални свойства; Може да бъде конфигуриран да се актуализира при създаването и / или актуализирането.

От гледна точка на големи бази данни, timestamp има няколко специални тригера на случая.

Това, което е правилно, зависи изцяло от това, което искате да направите.

32
03 янв. отговор, даден от Jeff Warnica на 03 януари 2009-01-03 19:21 '09 в 19:21 2009-01-03 19:21

TIMESTAMP е винаги в UTC (т.е. изминалите секунди от 1970-01-01, в UTC) и MySQL сървъра автоматично го преобразува в дата / час за часовата зона на сървъра. В дългосрочен план TIMESTAMP е начинът, по който трябва да отидете, защото знаете, че данните за времето Ви винаги ще бъдат в UTC. Например, няма да вмъквате вашите дати, ако превключите на друг сървър или промените настройките на часовите зони на вашия сървър.

27
26 авг. Отговорът е даден от Sobes на 26 август. 2010-08-26 02:21 '10 в 2:21 am 2010-08-26 02:21

Сравнение между DATETIME, TIMESTAMP и DATE

2019

11 авг. отговорът е даден jdc91 11 август. 2017-08-11 12:53 '17 в 12:53 2017-08-11 12:53

Струва си да се отбележи, че в MySQL можете да използвате нещо в долните редове, когато създавате таблични колони:

 on update CURRENT_TIMESTAMP 

Това ще актуализира времето във всеки случай, в който променяте низа, и понякога е много полезно за запазване на най-новата информация за редактиране. Това работи само с времеви клейма, но не и с datetime.

21
07 янв. Отговор, даден от leejmurphy 07 януари 2012-01-07 02:04 '12 в 2:04 2012-01-07 02:04

Винаги съм използвал Unix timestamp, когато работя с MySQL и PHP. Основната причина за това е методът на датата по подразбиране в PHP, използвайки времеви отпечатък като параметър, така че не се изисква анализ.

За да получите текущата времева маркировка на Unix в PHP, само time();
и в MySQL SELECT UNIX_TIMESTAMP(); ,

20
03 янв. Отговор, даден от Марк Дейвидсън на 03 януари 2009-01-03 19:18 '09 в 19:18 2009-01-03 19:18

Въз основа на моя опит, ако се нуждаете от поле за дата, в което вмъкването се извършва само веднъж и не искате да актуализирате или изпълнявате други действия в тази конкретна област, отидете на датата .

Например, помислете за user таблица с полето REGISTRATION DATE . В тази user таблица, ако искате да знаете последното време за влизане на конкретен потребител, укажете полето за тип време , за да се актуализира.

Ако създадете таблица от phpMyAdmin, настройката по подразбиране ще актуализира полето timestamp, когато редът се актуализира. Ако вашата времева маркировка не се актуализира с обновяване на ред, можете да използвате следната заявка, за да актуализирате автоматично полето timestamp .

 ALTER TABLE your_table MODIFY COLUMN ts_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 
14
06 февр. Отговорът е даден от Kannan Prasad 06 Feb. 2012-02-06 06:34 '12 в 6:34 am 2012-02-06 06:34

Типът данни за времеви клейма съхранява датата и часа, но във формат UTC, а не в текущия формат на часовата зона, като datetime. А когато изберете данни, времевият отпечатък отново го преобразува в текущото времево време.

Да предположим, че се намирате в САЩ и получавате данни от сървър, който има времева зона в САЩ. След това ще получите датата и часа в съответствие с часовата зона на САЩ. Колоната на типа данни за времеви клеймо винаги се актуализира автоматично при обновяване на реда. Така че може да е полезно да се проследява кога е бил актуализиран определен ред.

За повече подробности можете да прочетете блога Timestamp Vs Datetime .

13
20 дек. отговорът се дава от Arvind 20 dec. 2012-12-20 11:48 '12 в 11:48 2012-12-20 11:48

Внимавайте да не промените timestamp, когато изпълните оператор UPDATE в таблица. Ако имате таблица с колони "Име" (varchar), "Age" (int) и "Date_Added" (timestamp), и стартирате следния DML израз

 UPDATE table SET age = 30 

след това всяка стойност в колоната "Дата на добавяне" ще бъде променена на текущата дата.

12
04 окт. отговорът се дава от Lloyd Banks 04 окт. 2013-10-04 09:00 '13 в 9:00 2013-10-04 09:00

Връзка, взета от тази статия:

Основните разлики:

TIMESTAMP се използва за проследяване на промените в записи и актуализации всеки път, когато даден запис бъде променен. DATETIME се използва за съхраняване на определена и статична стойност, която не се влияе от промените в записите.

TIMESTAMP също зависи от настройката, свързана с TIME ZONE. DATETIME е постоянен.

TIMESTAMP преобразува текущата часова зона в UTC за съхранение и по време на търсенето се преобразува обратно в текущата часова зона. DATETIME не може да направи това.

Поддържан обхват TIMESTAMP: '1970-01-01 00:00:01' UTC до '2038-01-19 03:14:07' UTC Поддържан диапазон DATETIME: '1000-01-01 00:00:00' до '9999 -12-31 23:59:59

12
07 февр. Отговорът е даден от Anvesh 07 Feb. 2016-02-07 15:49 '16 в 15:49 2016-02-07 15:49

Винаги използвам времето на Unix, само за да поддържам здрав разум, когато се занимавам с много информация за времето и времето, особено когато правя корекции за часови зони, добавяне / изваждане на дати и т.н. Когато сравнявате времеви печати, това елиминира усложняващите фактори на часовата зона и ви позволява да пестите ресурси в обработката от страна на сървъра (независимо дали става дума за код на заявка или заявки за база данни), като използвате аритметика с ниско тегло, вместо да добавяте тежка дата / час / извадете функцията.

Още едно нещо, което трябва да имате предвид:

Ако създадете приложение, никога не знаете как могат да се използват данните ви по линията. Ако трябва, да кажем, да сравним куп записи във вашия набор от данни, да речем, куп елементи от API на трета страна, и да кажем, да ги поставим в хронологичен ред, ще се радваме да имаме времеви отпечатъци на Unix за вашите низове. Дори и да решите да използвате MySQL времеви отпечатъци, запазете времевата отметка на Unix като застраховка.

12
05 янв. Отговор е даден от Оливър Холмберг 05 януари 2012-01-05 05:50 '12 в 5:50 2012-01-05 05:50

В моя случай поставям UTC като часовата зона за всичко: системата, сървъра на базата данни и т.н. всеки път, когато мога. Ако клиентът ми се нуждае от различна часова зона, конфигурирам го в приложението.

Почти винаги предпочитам времеви отпечатъци, а не полета с дата-час, защото временните метки имплицитно включват времева зона. По този начин, от момента, в който приложението е достъпно за потребители от различни часови зони, и искате те да виждат датите и часовете в местната им часова зона, този тип поле го прави доста прост, отколкото ако данните се съхраняват в полетата във времето ,

Като плюс, ако базата данни беше мигрирана към система с различна часова зона, бих се чувствал по-уверен, като използвам времеви печати. Не можем да говорим за възможни проблеми при изчисляването на разликите между два момента с промяна в времето за изчакване между тях и необходимостта от точност от 1 час или по-малко.

Така че, обобщавайки, оценявам тези предимства на времеви клейма:

  • готови за използване в международни (многочасови) приложения
  • лесна миграция между часовите зони
  • Много е лесно да изчислите разликите (просто извадете двата времеви отпечатъка)
  • Не се притеснявайте за дати до / от летния период.

Поради всички тези причини избирам полетата UTC и timestamp, където е възможно. И избягвам главоболие;)

11
24 февр. отговорът е даден rogerpro 24 Feb. 2017-02-24 21:19 '17 в 21:19 2017-02-24 21:19

Открих ненадминато полезност в способността на TIMESTAMP да се актуализира автоматично въз основа на текущото време, без да използва ненужни тригери. Това е само мен, въпреки че TIMESTAMP е UTC, както беше казано.

Тя може да проследява различни часови зони, така че ако трябва да покажете относително време, например, UTC времето е това, което искате.

10
01 нояб. Отговорът е даден от Марк Димильо 01 ноември. 2011-11-01 22:42 '11 в 22:42 2011-11-01 22:42

Друга разлика между Timestamp и Datetime в Timestamp, не можете да използвате стойността по подразбиране NULL.

10
19 янв. отговорът е даден от ecleel 19 януари 2014-01-19 14:06 '14 в 14:06 2014-01-19 14:06