タイムスタンプを「年-月-日　時:分:秒」形式にするクエリ。PostgreSQLとSQLiteそれぞれで

最近仕事でSQLiteも使っており、その備忘録。せっかくなのでPostgreSQLの場合もメモ。バージョンはそれぞれ3.12.2、9.5.2。ドキュメントの日付・時刻関数は ↓ です。 SQLite Date And Time Functions PostgreSQL Date/Time Functions and Operators まずSQLiteで、現在時刻を「年-月-日　時:分:秒」ゼロ埋め形式で出そうと、次のクエリを試しました。

sqlite> select datetime('now');
2016-06-13 03:20:59

形式はOKだけどUTCで、日本だと9時間前になってしまう。ローカル時刻にする方法を調べた結果、

sqlite> select datetime(strftime('%s', 'now'), 'unixepoch', 'localtime');
2016-06-13 12:27:56

もっと簡単なクエリがあるかもしれませんが、とりあえずこれで。当面SQLiteで必要なのは、現在時刻をこの形式でINSERTするだけだし。ゼロ埋めされてない年月日・時分秒があると、SQLiteで扱うにはちょっと困ります。タイムスタンプとして認識されないので。PostgreSQLはこのへん便利（後掲）。

sqlite> select strftime('%s', '2016-06-01 01:23:45');       
-- return unixepoch

sqlite> select strftime('%s', '2016-6-1 1:23:45');
-- return empty

PostgreSQLは、関数now()または定数current_timestampがあるのですが（どちらも同じ）、マイクロ秒まで表示、かつローカル時刻のタイムゾーンがUTCとの差で明示されます。詳しすぎるというか。

# select current_timestamp;       
+-------------------------------+
|              now              |
+-------------------------------+
| 2016-06-13 20:08:14.463732+09 |
+-------------------------------+
(1 row)

# select now();
+------------------------------+
|             now              |
+------------------------------+
| 2016-06-13 20:08:24.43215+09 |
+------------------------------+
(1 row)

秒の小数点以下とタイムゾーンを省くには、先日書いたdate_trunc関数を使う、または文字になるけどto_char関数でフォーマットするとか。前者は、タイムゾーンを省くのに型変換が要ります。

# select date_trunc('second', now() :: timestamp);       
+---------------------+
|     date_trunc      |
+---------------------+
| 2016-06-13 12:33:57 |
+---------------------+
(1 row)

-- now()そのままだとタイムゾーンが入る
# select date_trunc('second', now());
+------------------------+
|       date_trunc       |
+------------------------+
| 2016-06-13 12:37:12+09 |
+------------------------+
(1 row)

# select to_char(now(), 'YYYY-mm-dd HH:mm:ss');
+---------------------+
|       to_char       |
+---------------------+
| 2016-06-13 12:06:48 |
+---------------------+
(1 row)

ちょっと細かい話で、二つのクエリとも秒の小数点以下の丸めは四捨五入ででなく切り捨て。date_truncはその名の通りですが、to_charで数値を丸めた時は四捨五入なので、てっきり秒もそうかと思ってました。

-- 秒の小数点以下は、切り捨て
# select date_trunc('second', timestamp '2016-7-8 1:23:45.999');
+---------------------+
|     date_trunc      |
+---------------------+
| 2016-07-08 01:23:45 |
+---------------------+
(1 row)

-- 同じく
# select to_char(timestamp '2016-7-8 1:23:45.999', 'YYYY-mm-dd HH:mm:ss');
+---------------------+
|       to_char       |
+---------------------+
| 2016-07-08 01:07:45 |
+---------------------+
(1 row)

-- to_charの数値フォーマットは四捨五入
# select to_char(98.7, 'FM00');
+---------+
| to_char |
+---------+
| 99      |
+---------+
(1 row)

上のようにゼロ埋めされてない日付・時刻文字列（2016-7-8 1:23:45）も、タイムスタンプ型として普通に渡せるのがPostgreSQLの便利なところ。date_truncなどの関数を通さず単に出力するだけで、自動的にゼロ埋めに統一表示されます。

# select timestamp '2016-7-8 1:23:45';       
+---------------------+
|      timestamp      |
+---------------------+
| 2016-07-08 01:23:45 |
+---------------------+
(1 row)

最後に念のため。date_trunc関数とto_char関数で一見同じ形にしたけど、前者はタイムスタンプ型、後者はテキスト型なので後の扱いが違ってきます。タイムスタンプなら + '1day'のように時間演算が可能。

-- データの型を見るにはpg_typeof関数
# select
    pg_typeof(
        date_trunc('second', now() :: timestamp)),
    pg_typeof(
        to_char(now(), 'YYYY-mm-dd HH:mm:ss'));
+-----------------------------+-----------+
|          pg_typeof          | pg_typeof |
+-----------------------------+-----------+
| timestamp without time zone | text      |
+-----------------------------+-----------+
(1 row)


-- フォーマットを整形、そして1日後にずらす
# select date_trunc('second', now() :: timestamp)
    + '1day';
+---------------------+
|      ?column?       |
+---------------------+
| 2016-06-14 12:35:05 |
+---------------------+
(1 row)