EPOCH TIME変換

EPOCH TIME：

UNIX時間またはUNIX時刻とはシステム上での時刻表現の一種。協定世界時 (UTC) での1970年1月1日午前0時0分0秒（UNIXエポック）から形式的な経過秒数として表現しています。システムを構築する中で幅広く使われている時間を表現する単位になります。

In Case Of MySQL

MySQLでは以下の様に変換する。MySQL Cluserを利用する場合はEPOCH TIMEを頻繁に利用します。

root@localhost [mysql]> select now(),UNIX_TIMESTAMP(now()) as epoc;
+---------------------+------------+
| now()               | epoc       |
+---------------------+------------+
| 2022-01-10 05:19:34 | 1641791974 |
+---------------------+------------+
1 row in set (0.00 sec)


root@localhost [mysql]> select from_unixtime(1641791974);
+---------------------------+
| from_unixtime(1641791974) |
+---------------------------+
| 2022-01-10 05:19:34       |
+---------------------------+
1 row in set (0.00 sec)

参照： 12.7 日付および時間関数

In Case Of PostgreSQL

postgres=# select now(),current_timestamp;
              now              |              now              
-------------------------------+-------------------------------
 2022-01-10 05:29:17.294922+00 | 2022-01-10 05:29:17.294922+00
(1 row)

postgres=# select extract(epoch from current_timestamp);
    date_part     
------------------
 1641792560.86197
(1 row)

postgres=# select to_timestamp(1641792560.86197);
         to_timestamp         
------------------------------
 2022-01-10 05:29:20.86197+00
(1 row)

postgres=# select to_timestamp(1641792560);
      to_timestamp      
------------------------
 2022-01-10 05:29:20+00
(1 row)

参照：9.9. 日付/時刻関数と演算子

例：活用方法

PostgreSQLのログには、以下の様にログに記録されています。

[2022-03-25 06:09:45 JST][29131][623cd1d8.71cb-1]

623cd1d8.71cb-1は以下のSQLで求められた値になります。

SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
 to_hex(pid)
FROM pg_stat_activity;

%cエスケープは、2つの4バイトの16進数（先頭のゼロは省略）をドットで区切った構成の、準一意なセッション識別子を表示します。 この数値はプロセスの起動時間とそのプロセスIDです。 したがって、%cを使用して、これらの項目を出力するための文字数を省略することができます。例として、pg_stat_activityからセッション識別子を生成するには以下の問い合わせを行ないます。

19.8. エラー報告とログ取得

上記のログを確認すると、プロセスの開始時間を確認する事が可能です。

POC=# create or replace function to_dec(text)
POC-#            returns integer as $$
POC$#            declare r int;
POC$#            begin
POC$#              execute E'select x\''||$1|| E'\'::integer' into r;
POC$#              return r;
POC$#            end
POC$#            $$ language plpgsql;
CREATE FUNCTION
Time: 9.086 ms
POC=# select pid,backend_start from pg_stat_activity;
 pid  |         backend_start
------+-------------------------------
   30 | 2022-03-23 08:27:36.658423+09
   32 | 2022-03-23 08:27:36.658804+09
   36 | 2022-03-23 08:28:40.631679+09
 2897 | 2022-03-25 07:27:36.925085+09
   28 | 2022-03-23 08:27:36.658038+09
   27 | 2022-03-23 08:27:36.657903+09
   29 | 2022-03-23 08:27:36.65824+09
(7 rows)

Time: 2.703 ms
POC=# SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' || to_hex(pid) FROM pg_stat_activity;
   ?column?
--------------
 623a5b68.1e
 623a5b68.20
 623a5ba8.24
 623cf058.b51
 623a5b68.1c
 623a5b68.1b
 623a5b68.1d
(7 rows)

Time: 3.078 ms
POC=# select to_timestamp(to_dec('623a5b68'));
      to_timestamp
------------------------
 2022-03-23 08:27:36+09
(1 row)

Time: 6.636 ms
POC=#

to_hexされた値を変換するには、to_decというfunctionを以下のページを参考に作成しています。

参照：Conversion between hex and dec numbers

623cd1d8.71cb-1をDecodeすると以下の様になります。. 以降はPIDなので除外して下さい。

POC=# select to_timestamp(to_dec('623cd1d8'));
      to_timestamp
------------------------
 2022-03-25 05:17:28+09
(1 row)

その他：Epoch & Unix Timestamp Conversion Tools

ShellでUNIX TIMEを変換する場合（memcachedのExpireのUnixTimeの確認等で利用可能)

~ [16:39:33]> date --date "@1104505200"
Sat Jan  1 00:00:00 JST 2005
~ [18:21:26]> date --date "@1650019737"
Fri Apr 15 19:48:57 JST 2022
~ [18:54:47]>