データベースの作成

データベースの作成#

TimescaleDBのテーブルの作成方法は以下の手順に従います。

  1. PostgreSQLのデータベースの作成

  2. PostgreSQLのテーブル作成

  3. 2.で作成したテーブルを、TimescaleDBのハイパーテーブルへ変換

データベースの作成#

最初に、PostgreSQLのデータベースを作成します。下記のコマンドの通りpsqlコマンドでPostgreSQLターミナルにログインします。

PS > psql -U postgres
Password for user postgres: <インストール時に設定した管理者パスワード>
psql (16.3)
Type "help" for help.
postgres=#

以下のSQL文を発行し、データベースを作成します。ここでは例としてデータベース名を、machine_dataとします。

postgres=# CREATE database machine_data;
CREATE DATABSE

続いて、\cコマンドにて作成したmachine_dataデータベースに接続し、データベースが正しく作成されたか確認します。

postgres=# \c machine_data 
You are now connected to database "machine_data" as user "postgres".
machine_data=#

\qコマンドにより、いちどpsqlを終了します。

machine_data=# \q
PS >

作成したデータベースに、TimescaleDBのエクステンションを有効にするクエリを発行します。

PS > echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | psql -U postgres -d machine_data
Password for user postgres: <インストール時に設定した管理者パスワード>
CREATE EXTENSION
PS >

テーブル作成#

CREATE TABLE database_throughput (
    datetime BIGINT NOT NULL,
    db_insert_queue_count integer,
    current_index integer,
    next_index integer,
    buffer_usage real
);

-- ハイパーテーブル作成。1時間（マイクロ秒精度）毎にチャンクを分割する設定
SELECT create_hypertable('database_throughput', by_range('datetime', 3600000000));

-- 時刻列でのインデックス追加
CREATE INDEX ix_datetime ON database_throughput (datetime DESC);

注釈

時系列データは通常、timestamp型を用いますが、TF6420を通じてtimestamp型の列にインサートするデータはPLCではDT（DATE_AND_TIME）型となります。DT型は秒までの精度しかありません。それよりも高い精度時刻を記録する場合は、BigInt型の列に対して、ULINT型（8byte）に格納したUNIX時間をセットします。UNIX時間は、1970-01-01 00:00:00を起点としたマイクロ秒精度のシリアル値です。このデータ形式であればPostgreSQLのto_timestamp()関数を用いて簡単にtimestamp型の列を生成し、様々な時系列処理を行うことができます。

SELECT to_timestamp(datetime::double precision / 1000000) as timestamp, * FROM public.database_throughput ORDER BY datetime DESC Limit 500

たとえば上記のクエリを発行すると以下の結果が得られます。datetime列には、PLCからUNIX時刻形式のシリアル値が格納されます。新たにtimestamp列に、timestamptz型の列が生成され、一覧されます。精度はマイクロ秒ですが末尾が1/1000秒未満の’0’の部分は丸められて表示されますのでこの例ではミリ秒（PLCサイクルタイム）単位でのデータが記録できていることがわかります。

timestamp

datetime

db_insert_queue_count

current_index

next_index

buffer_usage

2024-06-01 04:02:42.229+00

1717214562229000

0

467

467

0

2024-06-01 04:02:42.228+00

1717214562228000

0

466

467

0

2024-06-01 04:02:42.227+00

1717214562227000

0

465

467

0

2024-06-01 04:02:42.226+00

1717214562226000

0

464

467

0

:

しかし、TwinCATはWindowsネイティブなOSですので、UNIX時間を提供するファンクションは提供されていません。デフォルトで高い精度の時刻型を扱う場合は、Windows Filetime時間（T_Filetime64型）と呼ばれる異なるシリアル値が用いられます。こちらは1601-01-01 00:00:00を起点とした100ns精度のシリアル値です。UNIX時間よりは高い精度を持つのですがデータベースシステム上では時刻の取り扱いが極めて困難です。よって、PLC内部で次のSTプログラムによりFiletime型をUNIX時刻型へ変換する必要があります。

METHOD Filetime_To_UnixT : ULINT
VAR_INPUT
	datetime : T_filetime64;
END_VAR

Filetime_To_UnixT := (datetime - 116444736000000000) / 10;

このコードは、ご紹介したライブラリ内のFB_DatetimeUtilitiesの中のFiletime_To_UnixTメソッドでご提供しています。

マイクロ秒未満の精度が必要となる場合は、TwinCATには、T_Filetime64型、1ナノ秒精度まで必要な場合はT_Dctime64型まで用意されており、いずれも8byteデータです。このため、データベース側にはBigInt列にそのまま記録することが可能です。データベースクエリを発行する際のアプリケーション側で時刻表現との間で相互にエンコード、デコードして運用してください。

目次