用 PostgreSQL 的 int4range 與 GiST

發現自己根本還不熟悉 PostgreSQL 的特性,寫一下記錄起來。

產品上常常會有 coupon 與 voucher 之類的設計,這時候通常都會設定 coupon 或 voucher 的有效期間,在 MySQL 的環境下可能會這樣設計:

CREATE TABLE coupon (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(255) NOT NULL,
  started_at INT UNSIGNED NOT NULL,
  ended_at INT UNSIGNED NOT NULL
);

另外是設計 index 的部份,在產品推出夠久後,通常是過期的 coupon 或 voucher 會比目前還有效的多,而還沒生效的 coupon 與 voucher 通常都不多,所以會設計成對 ended_at 放一組 B-tree index:

CREATE INDEX ON t1 (ended_at);

這個設計不算差,不過用了一些假設。

如果不想要用這些假設,可以改用 Spatial 的資料型態去模擬並且加上 index (使用到 LineString Class),這樣就直接對 a < x < b 這類查詢更有效率,不過缺點就是可讀性會比較差。

在 PostgreSQL 這邊就有更清晰的資料結構來處理這些事情,主要是有一般性的 int4rangeint8range 以及時間類的 tsrangetstzrangedaterange (參考「Range Types」這邊有更多資料型態),所以會變成:

CREATE TABLE coupon (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  code VARCHAR NOT NULL,
  active_at INT4RANGE NOT NULL
);

然後用 GIST 建立 index:

CREATE INDEX ON t1 USING GIST(active_at);

後續的 query 語法就用 <@ 的語法:

SELECT COUNT(*) FROM coupon WHERE 10000 <@ active_at;

塞了 10M 筆資料後的 table 可以看到本來需要的時間是:

Time: 779.542 ms

變成:

Time: 5.510 ms

不過缺點就是 SQLite 沒支援這些資料型態,對於 test case 就一定得跑個 PostgreSQL 起來測...

RFC 3339 與 ISO 8601 用範例的比較

Hacker News 首頁上看到「RFC 3339 vs. ISO 8601 (ijmacd.github.io)」這個連結,原始網站是「RFC 3339 vs ISO 8601」。

兩個都是試著定義日期與時間的表達方式,其中 ISO 8601 是在 1988 年定義出來的,被廣泛應用在很多 spec 裡,但最大的問題就是 ISO 8601 沒辦法公開免費取得,這點在前幾個月也在 Hacker News 上被討論過:「ISO-8601 date format reference not publicly available (twitter.com/isostandards)」。

RFC 3339 是在 2002 年訂出來,其中一個目標就是試著解決 ISO 8601 不公開的問題 (沒講明就是了):

This document includes an Internet profile of the ISO 8601 [ISO8601] standard for representation of dates and times using the Gregorian calendar.

這份 RFC 3339 與 ISO 8601 的比較可以讓設計 spec 的人可以看一下,在大多數的情況下,RFC 3339 應該是夠用的...

第一堂:Course overview + the shell

這個系列是從『MIT 的「The Missing Semester of Your CS Education」』這邊延伸出來的,這篇文章講第一堂課「Course overview + the shell」。

前面大概講一下這 11 堂各一個小時的課大概是什麼,後面就開始講 shell 下的操作了。

先講了一些基本指令 (date & echo),然後提到了環境變數 $PATH,接著就講目錄結構與 ls,然後就順便提到 man 可以拿來查說明,接著是講 redirect 與 pipe 以及 root 權限的特殊性 (以及 sudo)。

在課程最後面的這個範例,你第一眼看不會想到是第一堂課就可以教完的東西,但的確是結合了上面提到的所有東西,可以細細品味一下:

$ echo 1 | sudo tee /sys/class/leds/input6::scrolllock/brightness

Trac 1.2 裡 datepicker 每個禮拜的第一天改成星期天

Trac 1.0 搭配舊的 DateFieldPlugin 時,預設每週的第一天會是星期一,但這個設定可以在 trac.ini 內用 first_day 參數調整,像是這樣:

[datefield]
format = ymd
separator = -
first_day = 0

但在 Trac 1.2 在 trac.ini 裡就沒有提供設定讓人調整了... 而由於 Trac 是用 jQuery UIDatepicker,在這個套件裡有提供方法讓人調整,所以解決的方向變成在 site.html 內用 JavaScript 處理,把這段程式碼塞到 JavaScript 的區段內就可以了:

// Datepicker
jQuery.datepicker.setDefaults({firstDay: 0});

一整個繼續惡搞中... 然後把 wiki 上的 Trac 條目也更新上去。

Trac 1.2 的 Due Date...

在先前的文章提到了把自己在用的事件管理系統 Trac 從 1.0 升級到 1.2,然後 Due Date 的設計改變了:「Trac 1.1 增加的 time 欄位,以及 Due Date 資料的轉移」、「總算把手上的 Trac 1.0 升級到 1.2 了...」。

Trac 1.2 的資料型態是在底層存 unix timestamp 的變形 (乘以 1000000,然後前端補上 0 存成文字),這幾天用下來才發現一些以前沒遇到的問題。

一開始轉到 Trac 1.2 是設成 date,但意外的發現 (因為伺服器時間不是 UTC),不同時區的使用者在更新 ticket 時,系統會判定 Due Date 有變動而產生變更記錄,想了一下就改用 datetime 來處理這個問題。

用了 datetime 一陣子後,才發現先前的公司遇到的情境中,時區差異都很小,所以不會有 Due Date 理解上的問題 (像是從 +7 到 +9 的時區),如果今天是美國西岸跟台灣互相合作的話,只用 date 就會產生很明顯的理解問題了...

算是這陣子用 Trac 1.2 而對 Due Date 設計有不一樣的理解...

總算把手上的 Trac 1.0 升級到 1.2 了...

就如同上一篇提到的,Trac 在 1.1.1 後新增了 time 格式,所以本來的 DateFieldPlugin 有些資料要轉換。我這邊只有用在 Due Date,所以就是轉 due_date 的資料而已。

先把 due_date 都改成 due_date_bak

UPDATE ticket_custom SET name = 'due_date_bak' WHERE name = 'due_date';

然後重新計算資料,這邊是因為所有的系統都是 UTC,所以直接轉就可以了:

INSERT INTO ticket_custom (ticket, name, value) SELECT ticket, 'due_date', LPAD(UNIX_TIMESTAMP(STR_TO_DATE(value, '%Y-%m-%d')) * 1000000, 18, '0') FROM ticket_custom WHERE name = 'due_date_bak';

而我的 Report 有用到 due_date 欄位的東西,本來是 c.value 直接輸出,現在要改成:

FROM_UNIXTIME(CONVERT(c.value / 1000000, UNSIGNED INTEGER), '%Y-%m-%d') AS due_date

Trac 1.2 相較於 1.0 最不習慣的地方應該是修改界面的位置改變了,現在 Add Comment 變成在 Modify 下面,有點不太習慣,但之後用久了應該就會習慣了。其他的修一修改一改都會動了...

Trac 1.1 增加的 time 欄位,以及 Due Date 資料的轉移

Trac 的版本玩法跟早期 Linux Kernel 的模式有點像,也就是版號偶數是正式版,奇數是開發版... 雖然現在 Linux Kernel 已經不玩這套了,但 Trac 還是維持這樣的開發方式。

先前一直都是用 Trac 1.0,其中 Due Date 的功能則是用「DateFieldPlugin」這個套件,讓 Trac 支援 date 格式,於是就可以在 [ticket-custom] 裡面指定 Due Date 了:

due_date = text
due_date.date = true
due_date.date_empty = false
due_date.label = Due Date
due_date.value = <now>

在套件的頁面也有提到在 Trac 1.1.1 後就有內建的方式可以用了:

Notice: This plugin is deprecated in Trac 1.2 and later. Custom fields of type ​time were added in Trac 1.1.1.

連結是連到 1.1 的,我要測 1.2 的,所以往現在的版本翻資料,可以看到在 TracTicketsCustomFields 這邊的說明:(這邊就懶的照原來 html 排了,用 pre 直接放縮排)

time: Date and time picker. (Since 1.1.1.)
    label: Descriptive label.
    value: Default date.
    order: Sort order placement.
    format: One of:
        relative for relative dates.
        date for absolute dates.
        datetime for absolute date and time values.

這樣一來設定就會變成:

due_date = time
due_date.format = date
due_date.label = Due Date
due_date.value = now

但底層資料怎麼存?先看 ticket_custom 這個表格的結構,可以看到是 EAV 的架構:

+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| ticket | int(11)    | NO   | PRI | NULL    |       |
| name   | mediumtext | NO   | PRI | NULL    |       |
| value  | mediumtext | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

隨便拉一些可以看出來放法很簡單:

+--------+----------+------------+
| ticket | name     | value      |
+--------+----------+------------+
|      1 | due_date | 2016-10-03 |
+--------+----------+------------+

改成 Trac 1.2 內建的 time 後,塞 2018/02/28 變成:

+--------+----------+--------------------+
| ticket | name     | value              |
+--------+----------+--------------------+
|      1 | due_date | 001519776000000000 |
+--------+----------+--------------------+

拿掉後面的六個 0 後可以看到就是 2018/02/28 了,要注意的是,這邊會受到時區影響,我一開始測試的時候沒調整,寫進去的時間是用伺服器預設的時區計算的。另外也大概能理解前面放兩個 0 的目的,是為了讓 string 比較時的大小就會是數字實際的大小。

$ date --date=@1519776000
Wed Feb 28 00:00:00 UTC 2018

這樣就知道要怎麼做人工轉換了...

Firefox 的 Input 要支援 Date/Time 格式了

Hacker News Daily 上看到六月的文章,看起來是 Firefox 下一個版本要支援所以冒出來了:「Date/Time Inputs Enabled on Nightly」。

然後支援多國語系:(官方拿的範例剛好就是中文)

依照 MDN 上的說明「<input type="date">」以及 Can I Use 往站上的說明「Can I use Date and time input types」,可以看到這樣又多了一個瀏覽器啦:

Safari 被稱為新世代的 IE 真不是蓋的 XDDD

Facebook 在 MySQL 裡存時間的型態

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.

順道一提,INT 是 2038 年問題,INT UNSIGNED 是 2106 年問題。

而 Facebook 在 MySQL 上會選擇不使用 DATETIMETIMESTAMP 的原因其實跟技術搭不上太多關係,主因是因為 MySQL 根本沒打算修 XDDD

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

我的猜測是已經爛成一團了,而且大家都有 workaround (呃,其實就是 Facebook 推薦用 INT UNSIGNED 的方法),再考慮到有一票現有程式,在上面狂用 side effect 讓執行結果正確,不如就不要修這種吃力不討好的東西了 XDDD

另外一方面 timezone 資訊其實常常變化,常常需要更新 MySQL 的 timezone database (而這對於維運來說不是什麼開心的事情):

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.

這就是為什麼大家遇到 MySQL 時都會推薦用 INT UNSIGNED 了...

另外可以參考三年前的文章「MySQL 裡儲存時間的方式...」,裡面引用了 Baron Schwartz 的說明:

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

其實這已經是個 best practice 了...

DVD 的發行時間差異導致盜版

MPAA 資助的報告顯示 DVD 的發行時間差異導致盜版:「DVD Release Delays Boost Piracy and Hurt Sales, Study Shows」,報告在「Windows of Opportunity: The Impact of Piracy and Delayed International Availability on DVD Sales」這邊可以看到。

用真實資料計算得到:

"When we run our regressions on Spain and Italy alone, we observe a 10% drop in sales for every 10-day delay in legal availability, as compared to a 2% drop in sales for every 10-day delay in the entire sample," the paper reads.

"Our results suggest that an additional 10-day delay between the availability of digital piracy and the legitimate DVD release date in a particular country is correlated with a 2-3% reduction in DVD sales in that country," the researchers write.

時效的重要...