# MySQL的Timestamp準確度、快取應用的雷點

MySQL是常用的關聯式資料庫管理系統，其中Timestamp類型是一種用來儲存日期和時間的資料類型。在定義Timestamp欄位時，如果不[定義準確小數點](https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html) 的話，預設只到秒，且會四捨五入。  
  
比如以下的資料表定義，就明確定義了到小數點下6位也就是微秒精度。  

```sql
CREATE TABLE `services` (  
`id` bigint(20) NOT NULL AUTO_INCREMENT,  
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  
`end_time` timestamp(6),  
`name` varchar(255) NOT NULL,  
`created_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  
`updated_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),  
PRIMARY KEY (`id`)  
)  
```

以下的定義就沒有明確定義，所以是到秒。本文的範例是以這個為主。
```sql
CREATE TABLE `services` (  
`id` bigint(20) NOT NULL AUTO_INCREMENT,  
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  
`end_time` timestamp,  
`name` varchar(255) NOT NULL,  
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
PRIMARY KEY (`id`)  
)  
```

  
若要調整成捨棄小數位，可以使用MySQL的參數設定：[`time_truncate_fractional`](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_time_truncate_fractional)。該設定可以用來設置Timestamp的小數點精度。如果將此參數設置為ON，則MySQL會捨棄Timestamp值的小數位，而不是四捨五入。  

```
SET sql_mode='TIME_TRUNCATE_FRACTIONAL';  
```
  
在使用MySQL Timestamp時，可能會遇到大小比較的問題。例如，當從資料庫中讀取一筆Timestamp資料並與Java裡的時間進行比較時，可能會出現比較錯誤的情況。這是因為MySQL Timestamp的四捨五入可能導致大小關係相反。  
  
用剛剛的Services資料表做比喻，這也類似我實際上遇到的問題。 這邊定義我們的邏輯，用現在時間跟DB讀出來的 `startTime` `endTime` 比較，`isEnabled` 會回傳現在該服務的狀態。

```java
public class Service {

    private Long id;
    private String name;
    private LocalDateTime startTime;
    private LocalDateTime endTime;
    private LocalDateTime createdAt;
    private LocalDateTime updatedAt;

	public boolean isEnabled() {
	    LocalDateTime now = LocalDateTime.now();
	    if (startTime.isAfter(now)) {
	        // Current time is prior to startTime
	        return false;
	    } else if (endTime == null || endTime.isAfter(now)) {
	        // Current time is between startTime and endTime, or endTime is null
	        return true;
	    } else {
	        // Current time is after endTime
	        return false;
	    }
	}
}

```
  
範例資料如下:
```sql
INSERT INTO `services` (`id`, `start_time`, `name`, `created_at`, `updated_at`)
VALUES (1, '2022-02-25 12:00:00', 'credit_card', NOW(), NOW());

select * from services;
+----+---------------------+--------------+---------------------+---------------------+---------------------+
| id | start_time          | name | end_time            | created_at          | updated_at          |
+----+---------------------+--------------+---------------------+---------------------+---------------------+
|  1 | 2022-02-25 12:00:00 | credit_card  | NULL                | 2022-02-25 10:00:00 | 2022-02-25 10:00:00 |
+----+---------------------+--------------+---------------------+---------------------+---------------------+

```

假設現在時間是是 `2023-02-25 12:00:04.500`，我們想結束某個服務，我們把end_time設成現在時間，sql上會被存成 `2023-02-25 12:00:05`。注意到這個 timestamp 已經被進位到了整秒，而且精度是秒級的。  
  
假設馬上有另一個請求進到資料庫查找，時間是 `2023-02-25 12:00:04.600` ，這時候這個 `isEnabled` 的結果就會還是 `true` 。
這樣會出現service還沒結束的錯誤結果，雖然這只會持續幾百毫秒，但因為原本設計是快取 API Response，也就是只快取計算後的 `isEnabled`，導致接下來的更長時間 API 都回傳錯誤結果。如果 timestamp 精度較高，這種錯誤的機率會變小，但我們後來發現即使解決了比較問題，還有另一個問題需要注意，那就是不同主機上的時間可能會有幾百毫秒的差異，甚至更高。 
  
因此，如果把比較Timestamp的結果存在快取中，則可能會導致錯誤的結果。解決方法是把判斷邏輯寫在應用層 ，在快取中我們直接儲存原本的 `startTime` 和 `endTime` 。這才能根本解決問題。

總結如下三點:

1. MySQL 的 `timestamp`  如果沒有特別指定是準確到秒
2. MySQL 可以用這個格式定義精度: `timestamp(6)`  接受的精度數字是 0 到 6
3. 不要快取會因為時間差異而結果不一樣的資料

P.S. 這篇網誌的程式碼及一部分文字是 ChatGPT 生成