## 兩個 unsigned int 取平均值的方法

Hacker News Daily 上看到 Raymond Chen 在講怎麼對兩個 unsigned int 取平均值的方法：「On finding the average of two unsigned integers without overflow」，這篇裡面提到了不少有趣的歷史，另外 Hacker News 上的討論「Finding the average of two unsigned integers without overflow (microsoft.com)」也可以翻翻。

```unsigned average(unsigned a, unsigned b)
{
return (a + b) / 2;
}
```

```unsigned average(unsigned low, unsigned high)
{
return low + (high - low) / 2;
}
```

```unsigned average(unsigned a, unsigned b)
{
return (a / 2) + (b / 2) + (a & b & 1);
}
```

```unsigned average(unsigned a, unsigned b)
{
return (a & b) + (a ^ b) / 2;
}
```

```unsigned average(unsigned a, unsigned b)
{
// Suppose "unsigned" is a 32-bit type and
// "unsigned long long" is a 64-bit type.
return ((unsigned long long)a + b) / 2;
}
```

## MySQL 的 TIME 範圍

TIME values may range from '-838:59:59' to '838:59:59'.

One of the bits was used for the sign as well, but the remaining 23 bits were an integer value produced like this: Hours × 10000 + Minutes × 100 + Seconds; in other words, the two least significant decimal digits of the number contained the seconds, the next two contained the minutes, and the remaining ones contained the hours. 223 is 83888608, i.e. 838:86:08, therefore, the maximum valid time in this format is 838:59:59.

MySQL at Facebook這邊說明提到了，Facebook 內部是使用 `INT UNSIGNED` 儲存時間：

Which gets us to the point that it is no different than storing INT (hello 2038?) or UNSIGNED INT (a bit later) or BIGINT (till the end of time) and possibly passing binary values in efficient protocols eventually.

If you got that far of this post, your likes in Facebook graph are stored with 'INT UNSIGNED' time field.

It is my favorite MySQL bug, simply because it forces any reasonable mind not to use TIMESTAMP, and MySQL is never going to fix it (nor will ever understand time). I lost my temper a bit on that bug: https://bugs.mysql.com/bug.php?id=38455

There're few ways around that. One of them is side-load and maintain timezone data inside MySQL itself - it has support for internal timezone database and tracks obscure time shifts like ones for "Pacific War Time" and "Pacific Peace Time". That is operationally feasible (you have to remind yourself to update the database whenever time rules change, and they do change a lot, if you consider every timezone in the world), but has limited value.

All date and time columns shall be `INT UNSIGNED NOT NULL`, and shall store a Unix timestamp in UTC.

## MySQL 裡搜尋 CHAR/VARCHAR (String) 欄位時要注意的事情

MySQL 表格欄位是 CHAR 或 VARCHAR 時，寫搜尋條件要記得使用 string 格式，而非數字。意思是，要避免這種 SQL query：

`SELECT * FROM foo WHERE `column_string` = 123456;`

```mysql> SELECT 123456 = '0123456';
+--------------------+
| 123456 = '0123456' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT 123456 = ' 123456';
+--------------------+
| 123456 = ' 123456' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)```

• 可以用 INT/BIGINT 時，不要用 CHAR/VARCHAR 儲存。
• 使用 CHAR/VARCHAR 的欄位當搜尋條件時，要用字串形式當作搜尋條件。(除非你很清楚你在做什麼)

## MySQL 裡儲存時間的方式...

MySQL 內可以儲存「日期與時間」的資料型態是 DATETIME 與 TIMESTAMP 兩種，不過 DATETIME 沒有時區觀念，而 TIMESTAMP 只能是 UTC (GMT+0)。