## 兩個 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 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 裡儲存時間的方式...

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