now - mysql timestamp to date




我應該在MySQL中使用日期時間或時間戳數據類型嗎? (20)

2016 + :我建議將Mysql時區設置為UTC並使用DATETIME:

任何最新的前端框架(Angular 1/2,react,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/datehttps://docs.angularjs.org/api/ng/filter/date

您會建議使用datetime datetimedatetime字段,以及為什麼(使用MySQL)?

我在服務器端使用PHP。


  1. 對於DATETIME,TIMESTAMP是四個字節對八個字節。

  2. 時間戳在數據庫上也更輕,索引速度更快。

  3. 當您需要包含日期和時間信息的值時,將使用DATETIME類型。 MySQL以'YYYY-MM-DD HH:MM:SS'格式檢索並顯示DATETIME值。 支持的範圍是'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. 當TIMESTAMP受time_zone設置影響時,DATETIME是常量。

MySQL中的時間戳通常用於跟踪記錄的更改,並且通常在每次更改記錄時更新。 如果要存儲特定值,則應使用datetime字段。

如果您想要在使用UNIX時間戳或本機MySQL日期時間字段之間做出決定,請使用本機格式。 您可以在MySQL中以這種方式進行計算("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") ,當您查詢記錄時,可以很容易地將值的格式更改為UNIX時間戳("SELECT UNIX_TIMESTAMP(my_datetime)")如果你想用PHP操作它。


TIMESTAMP始終為UTC(即1970-01-01以來經過的秒數,以UTC為單位),並且您的MySQL服務器會自動將其轉換為服務器時區的日期/時間。 從長遠來看,TIMESTAMP是最佳選擇,因為您知道您的時態數據將始終為UTC。 例如,如果您遷移到其他服務器或更改了服務器上的時區設置,則不會將日期搞砸。


參考本文:

主要區別:

TIMESTAMP用於跟踪記錄的更改,並在每次更改記錄時更新。 DATETIME用於存儲特定值和靜態值,不受記錄中任何更改的影響。

TIMESTAMP也受到不同TIME ZONE相關設置的影響。DATETIME是不變的。

TIMESTAMP在內部將當前時區轉換為UTC以進行存儲,並在檢索期間將其轉換回當前時區。DATETIME無法做到這一點。

TIMESTAMP支持的範圍:'1970-01-01 00:00:01'UTC到'2038-01-19 03:14:07'DESD DATETIME支持的範圍:'1000-01-01 00:00:00'到'9999 -12-31 23:59:59'


timestamp字段是datetime timestamp字段的特例。 您可以創建timestamp列以具有特殊屬性; 它可以設置為在創建和/或更新時更新自身。

在“更大”的數據庫術語中, timestamp上有幾個特殊情況觸發器。

正確的取決於你想做什麼。


主要區別在於DATETIME是常量,而TIMESTAMP受time_zone設置的影響。

因此,只有當您擁有 - 或者將來可能 - 跨時區同步集群時,這才有意義。

換句話說: 如果我在澳大利亞有一個數據庫,並且轉儲該數據庫以同步/填充美國的數據庫,則TIMESTAMP將更新以反映新時區中事件的實際時間,而DATETIME將仍然反映了au時區的事件發生時間

應該使用TIMESTAMP的DATETIME的一個很好的例子是在Facebook,他們的服務器永遠不能確定跨時區發生的時間。 一旦我正在進行對話,其中時間說我在實際發送消息之前回復了消息。 (當然,這也可能是由於消息傳遞軟件中的時區轉換不正確而導致發佈時間而不是同步。)


以下示例顯示TIMESTAMP日期類型如何在將time-zone to 'america/new_york'更改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:日期時間與時間戳數據類型


在MySQL 5及更高版本中, TIMESTAMP值從當前時區轉換為UTC以進行存儲,並從UTC轉換回當前時區以進行檢索。 (這僅適用於TIMESTAMP數據類型, 而不適用於其他類型,例如DATETIME。)

默認情況下,每個連接的當前時區是服務器的時間。 可以在每個連接的基礎上設置時區,如MySQL服務器時區支持中所述


在使用MySQL和PHP時,我總是會使用Unix時間戳。 這是PHP中的默認date方法的主要原因是使用時間戳作為參數,因此不需要解析。

要在PHP中獲取當前的Unix時間戳,只需執行time();
在MySQL中做SELECT UNIX_TIMESTAMP();



我在語義基礎上做出這個決定。

當我需要記錄(或多或少)固定時間點時,我使用時間戳。 例如,當記錄插入數據庫或發生某些用戶操作時。

當日期/時間可以任意設置和更改時,我使用日期時間字段。 例如,當用戶可以保存以後更改約會時。


我發現TIMESTAMP能夠在不使用不必要的觸發器的情況下根據當前時間自動更新自身的能力無與倫比。 這只是我,雖然TIMESTAMP是UTC,就像它說的那樣。

它可以跟踪不同的時區,因此如果您需要顯示相對時間,則UTC時間就是您想要的。


我總是使用Unix時間戳,只是為了在處理大量日期時間信息時保持理智,特別是在執行時區調整,添加/減去日期等時。 在比較時間戳時,這排除了時區的複雜因素,並允許您在服務器端處理(無論是應用程序代碼還是數據庫查詢)中節省資源,因為您使用輕量級算術而不是更重的日期時間加/減功能。

另一件值得考慮的事:

如果您正在構建應用程序,那麼您永遠不會知道如何在線下使用您的數據。 如果你不得不比較你的數據集中的一堆記錄,比如來自第三方API的一堆項目,並說,按照時間順序排列它們,你會很高興有您的行的Unix時間戳。 即使您決定使用MySQL時間戳,也要將Unix時間戳存儲為保險。


時間戳數據類型存儲日期和時間,但是以UTC格式存儲,而不是像datetime那樣以當前時區格式存儲。 當您獲取數據時,時間戳再次將其轉換為當前時區時間。

因此,假設您在美國並從具有美國時區的服務器獲取數據。 然後,您將根據美國時區獲取日期和時間。 時間戳數據類型列在其行更新時始終自動更新。 因此,跟踪上次更新特定行的時間非常有用。

有關詳細信息,請閱讀博客文章Timestamp Vs Datetime


根據我的經驗,如果您想要一個只插入一次的日期字段,並且您不希望對該特定字段進行任何更新或任何其他操作,請使用日期時間

例如,考慮具有REGISTRATION DATE字段的user表。 在該user表中,如果您想知道特定用戶的上次登錄時間,請使用時間戳類型字段以便更新該字段。

如果要從phpMyAdmin創建表,則默認設置將在發生行更新時更新時間戳字段。 如果您的時間戳字段未使用行更新進行更新,則可以使用以下查詢使時間戳字段自動更新。

ALTER TABLE your_table
      MODIFY COLUMN ts_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

A TIMESTAMP需要4個字節,而a DATETIME需要8個字節。


就我而言,我每次都將UTC設置為所有內容的時區:系統,數據庫服務器等。如果我的客戶需要另一個時區,那麼我在應用程序上配置它。

我幾乎總是喜歡時間戳而不是日期時間字段,因為時間戳隱含地包含時區。因此,由於應用程序將從不同時區的用戶訪問,並且您希望他們在本地時區中查看日期和時間,因此該字段類型使得執行此操作非常容易,而不是將數據保存在日期時間字段中。

另外,在將數據庫遷移到具有其他時區的系統的情況下,我會更自信地使用時間戳。不是說在計算兩個時刻之間的差異時可能出現的問題,其中時間間隔發生變化,需要1小時或更短的精度。

總而言之,我重視時間戳的這些優點:

  • 準備在國際(多時區)應用程序上使用
  • 在時區之間輕鬆遷移
  • 很容易計算差異(只是減去兩個時間戳)
  • 不用擔心夏季進出日期

出於所有這些原因,我選擇了有效的UTC和時間戳字段。我避免頭痛;)


我喜歡Unix時間戳,因為你可以轉換為數字而只是擔心數字。另外,您可以添加/減去並獲得持續時間等。然後將結果轉換為日期格式。此代碼查找文檔的時間戳與當前時間之間經過的時間(以分鐘為單位)。

$date  = $item['pubdate']; (etc ...)
$unix_now = time();
$result = strtotime($date, $unix_now);
$unix_diff_min = (($unix_now  - $result) / 60);
$min = round($unix_diff_min);

我更喜歡使用時間戳,以便將所有內容保存為一種通用的原始格式,並在PHP代碼或SQL查詢中格式化數據。在某些情況下,它會在您的代碼中派上用場,以便在一瞬間保持所有內容。







sqldatatypes