Alea jacta est

SERIAL型の代わりにUUIDをつかえとか色々あるんだけど
SERIAL型だとBIGINTなので8バイト、UUIDだとUUID型で内部16バイトって事でストレージコストは倍。
これじゃ困るので、UUIDv1は日付+時間から生成されているので
そこから取り出せば、レコードの生成日時カラムを省略できるってことで
UUIDv1を与えるとtimestamp型を返します。
UUIDv1以外のUUIDを与えるとnullが返ります。


SELECT uuidtodate('a97d624a-7040-11e6-9542-000000000000');
uuidtodate
----------------------------
2016-09-01 21:36:10.516334
(1 row)


コードは下記の通り
CREATE OR REPLACE FUNCTION uuidToDate(uuid) RETURNS timestamp with time zone
IMMUTABLE RETURNS NULL ON NULL INPUT
AS
$BODY$
DECLARE
uuid alias FOR $1;
uuidtable VARCHAR[];
timeHi VARCHAR;
datetime timestamp ;
BEGIN
uuidtable := regexp_split_to_array(uuid::VARCHAR,'-');
IF (substr(uuidtable[3], 1,1) != '1') THEN
return null;
END IF;
timeHi := substr(uuidtable[3], 2);
return to_timestamp((('x' || lpad(concat(timeHi,uuidtable[2],uuidtable[1]), 16, '0'))::bit(64)::BIGINT::NUMERIC/10000000::NUMERIC)-12219292800);
END
$BODY$
LANGUAGE plpgsql;


逆パターン。timestampからUUIDを生成
内部的にuuid_generate_v1mc()を必要としてるので注意

CREATE OR REPLACE FUNCTION DateToUUID(timestamp) RETURNS uuid
IMMUTABLE RETURNS NULL ON NULL INPUT
AS
$BODY$
DECLARE
datetime alias FOR $1;
uuidtable VARCHAR[];
epochTime BIGINT;
epochTimeMicro BIGINT;
uuidTime BIGINT;
uuidHex VARCHAR;
uuid UUID;
BEGIN
uuid := uuid_generate_v1mc();
uuidtable := regexp_split_to_array(uuid::VARCHAR,'-');
epochTime := FLOOR(EXTRACT(epoch FROM datetime));
epochTimeMicro := EXTRACT(microseconds FROM datetime)-EXTRACT(second FROM date_trunc('second',datetime));

uuidTime := ((epochTime+12219292800)*10000000)+(epochTimeMicro*10); -- 補正 1582年からの通算ナノ秒
uuidHex := lpad(to_hex(uuidTime),15,'0');

return concat(substr(uuidHex,8),'-',substr(uuidHex,4,4),'-1',substr(uuidHex,1,3),'-',uuidtable[4],'-',uuidtable[5]);

END
$BODY$
LANGUAGE plpgsql;


当然ながら無保証です

なんで1582年なんだよぉぃって思ってたら
グレゴリオ暦使ってるだけの話だったオチ