mysql日期用between还是%3e_MySQL:最好使用Unix時間戳或DATETIME [duplicate]

9

As others have said, timestamps can represent a smaller range of datetimes (from 1970 to 2038). However, timestamps measure the number of seconds since the Unix Epoch (1970-01-01 00:00:00 UTC), thereby making them independent of time zone, whereas DATETIME stores a date and time without a time zone. In other words, timestamps unambiguously reference a particular point in time, whereas the exact point in time a DATETIME refers to requires a time zone (which is not stored in a DATETIME field). To see why this can matter, consider what happens if we change our time zone.

正如其他人所說，時間戳可以代表更小的日期時間范圍(從1970年到2038年)。但是，時間戳度量Unix紀元后的秒數(1970-01-01 00:00 UTC)，從而使它們獨立於時區，而DATETIME存儲一個沒有時區的日期和時間。換句話說，時間戳明確地引用了一個特定的時間點，而DATETIME引用的確切時間點則需要一個時區(它不存儲在DATETIME字段中)。要知道為什么這很重要，考慮一下如果我們改變時區會發生什么。

Let's say we want to store the datetime 2010-03-27 12:00 UTC. If we store this and retrieve it using a timestamp or DATETIME, then there usually appears to be no difference. However, if the server now changes so that the local time zone is UTC+01, then we get two different results if we pull out the datetime.

假設我們想要存儲datetime 2010-03-27 12:00 UTC。如果我們存儲它並使用時間戳或DATETIME來檢索它，那么通常沒有區別。但是，如果服務器現在發生了變化，那么本地時區是UTC+01，那么如果我們取出datetime，就會得到兩個不同的結果。

If we'd set the field to a DATETIME, it would report the datetime as 2010-03-27 12:00, despite the change in time zone. If we'd set the field to a timestamp, the date would be reported as 2010-03-27 11:00. This isn't a problem with either datatype -- it's just a result of the fact that they store slightly different information.

如果我們將字段設置為DATETIME，它會將DATETIME報告為2010-03-27 12:00，盡管時區發生了變化。如果我們將字段設置為時間戳，那么日期將被報告為2010-03-27 11:00。這兩個數據類型都不是問題——這只是它們存儲的信息略有不同的結果。