Forguncy（フォーガンシー）のお悩み解決！日付が数字になるときの対処方法

Excel関数もグラフも使えて画面も変更できる、ノンプログラミングWebシステム開発ツール「Forguncy（フォーガンシー）」。

Forguncyを使用していて「日付を出力したいのに数字が出てしまう」「ビューで範囲指定を行いたいのにうまくいかない」といったことはないでしょうか。
今回はForguncyを使った際の内部データベースの仕様で『日付』の扱いについて取り上げていきたいと思います。

↓↓　前回の記事はこちら　↓↓

今回の項目は以下の４つです。

1. ビューを作成したときの日付のデータ型について
2. strftime関数を使ってSQLで日付を範囲指定する
3. サーバーサイドコマンドで出力したときに少数になってしまう
4. strftime関数の応用

１．ビューを作成したときの日付のデータ型について

Forguncyで作成するビューには以下の仕様があります。

ビューを作成すると、作成時に使用したテーブルのいくつかのデータ型は保持されず、以下の表にように変更されます。この変更によって、表示する内容に不都合がある場合は、ページ上でセルの書式設定等の機能を使用してください。

テーブルでのデータ型『日付／時刻』はビューを作成すると、データ型は『少数』に変更されるということです。
早速見ていきましょう。

まずは、2022年の7月～8月の金曜日の日付をテーブルに追加してみます。

日付のカラムを選択し、フィールドを確認するとデータ型は『日付／時刻』となっています。

次にビューを作成してみましょう。
作成タブからビューの▼を押下し、ビューの作成をクリックします。

次のようなウィンドウが表示されます。

今回、上記で作成したテーブルは「テーブル1」という名前なのでこのままOKボタンを押下します。

テーブルの欄にビュー1が追加され、下図のような表が表示されます。

日付の欄はIDが「1」のとき「2022/7/1」ではなく「44743」となっています。
日付のカラムを選択し、フィールドを確認するとデータ型は『少数』となっています。

自動でビューのデータ型が変更されていますね。

他にもビューを見ると「FGC_」から始まる列が表示されていますが、こちらはForguncyの内部テーブルに独自で持っている以下のカラムです。

ビューの日付の値は少数に変更されていますが、ページで表示する際は日付にすることができます。

ビュー1をソースとしてリストビューを配置し、[ID]と[日付]をデータ連結します。

日付の欄はビューを元に表示しているため少数で表示されています。
「ホーム」タブの右上にある数値のドロップダウンメニューを開き、「短い日付形式」を選択します。

日付形式に変更されました。

データ型が変更されていても、Forguncyの画面では日付として表示することができました。

２．SQLで日付を範囲指定する

先ほどのテーブル「テーブル1」を使って2022/7/1～2022/7/31を範囲指定し、ビューとして作成してみましょう。

SQLiteの範囲指定はWHERE文で下記のように記述します。

WHERE カラム BETWEEN ‘2022/07/01’ AND ‘2022/07/31’;

もしくは

WHERE カラム <= ‘2022/07/01’ AND カラム >= ‘2022/07/31’;

新しくビューを作成し、SQL文を下図のように変更し、OKを押下します。

「ビュー2」が追加されましたが、肝心の表が出てきません。

先ずは、WHERE文の範囲指定が正しいか確認します。
先ほど作ったビュー1から下記を読み取り、SQL文を修正します。
「2022/07/01」　→　「44743」
「2022/07/29」　→　「44771」

修正後、OKボタンを押下します。
すると表がでてきました。WHERE文は正しいようです。

 

まずは「日付」のデータ型を「少数」から「文字列」に修正してみます。
strftime関数を用い、以下のようにSQL文を修正します。
WHERE文はコメントアウトしています。

SELECT id,strftime('%Y/%m/%d',日付) as "日付"
FROM "テーブル1"
--WHERE 日付 BETWEEN "44743" AND "44771"
;

OKボタンを押下して表を確認します。

日付のようなフォーマットにはなりましたが、もともとの値とは異なりますね。

これは、SQLiteのstrftime関数の第2引数には、YYYY-MM-DD形式で日付を指定するかユリウス日での設定が必要になるためです。
ビューで「日付／時刻」→「少数」にデータ型が変換されましたが、この「少数」はシリアル値となっておりそれが原因で正しい日付が表示されていないようです。

正しい日付を得るためには、シリアル値からユリウス日に変換してあげる必要があります。
シリアル値である日付に「2415018.5」を足すことにより、ユリウス日が求められます。

ユリウス日＝シリアル値＋2415018.5

早速下記のようにSQLを修正しましょう。

SELECT id,strftime('%Y/%m/%d',日付+2415018.5) as "日付"
FROM "テーブル1"
--WHERE 日付 BETWEEN "44743" AND "44771"
;

 

OKボタンを押下して、表を確認します。

正しい日付が表示されました。

WHERE文も下記のように修正を行います。

SELECT id,strftime('%Y/%m/%d',日付+2415018.5) as "日付"
FROM "テーブル1"
WHERE strftime('%Y/%m/%d',日付+2415018.5) BETWEEN "2022/07/01" AND "2022/07/31";

OKボタンを押下して、表を確認します。

「テーブル1」に格納されている7月分の日付が表示されましたね。
日付のデータ型は「テキスト」に変更されています。

SQLでの日付の範囲指定ができました。
ユリウス日とシリアル値の関係は知っていないと躓くポイントですので、ぜひこの機会にマスターしていただければと思います。

３．サーバーサイドコマンドで出力したときに少数になってしまう

１．で作成した画面に２つボタンを追加します。
以下は画面イメージです。

「CSV出力」ボタンのコマンドは以下のように設定します。

ビルドして実行し、「CSV出力」ボタンを押下してみましょう。

CSV出力ができました。

次にサーバーサイドコマンドを作成します。
「作成」タブの「サーバーサイドコマンド」をクリックします。

表示されたウィンドウから「作成」ボタンを押下します。

サーバーサイドコマンドの設定ウィンドウが表示されました。
「コマンド」タブに遷移し、「コマンド…」を押下します。

コマンドの設定で「CSVインポート／エクスポート」を設定します。
この時、CSVファイルのパスを必ず設定してください。
サンプルではデスクトップに「TEMP」というフォルダを作成し、「csv1」という名前で保存されるようにしてみました。

「新しいコマンド」を追加し、「リターン（コマンドの終了）」を設定します。
今回は特にメッセージは入れず、リターンコードを「0」としてサーバーサイドコマンドが完了したことを画面に表示させたいと思います。

「OK」ボタンを押下し、ウィンドウを閉じます。

サーバサイドコマンドマネージャーには先ほど追加した「サーバーサイドコマンド１」が「有効」の状態で表示されていることを確認します。

画面に戻り、「サーバーコマンド」ボタンにサーバーサイドコマンドを呼び出す処理を追加します。
「サーバーサイドコマンドの呼び出し」を選択し、先ほど作成した「サーバーサイドコマンド1」を選択します。
リターンコードセルは、今回はボタンの下のセルに表示するように設定をしました。

「OK」ボタンを押下し、ウィンドウを閉じます。
ビルドを行い、「サーバーコマンド」ボタンを押下します。

リターンコードが表示されたら、処理は完了です。
先ほど指定したフォルダにアクセスして確認してみます。

CSVが出力されていますね。
テキストで開いて中を確認してみます。

日付が数値になっていますね。このように、サーバーサイドコマンドで出力した日付は「少数」に変換されているようです。
２．で作業したビューのように日付を文字列に置き換えてみましょう。
新しくビュー3を作成します。
IDと日付だけのビューを作成します。

SELECT ID,strftime('%Y/%m/%d',日付+2415018.5) as "日付"
FROM "テーブル1";

「OK」ボタンを押下し、ビューが作成されていることを確認します。

日付がテキストで表示されていますね。
この「ビュー3」をサーバーサイドコマンドの「CSVインポート／エクスポート」の対象テーブルに設定します。

「OK」ボタンを押下し、ビルドを行い「サーバーコマンド」ボタンを押下します。

リターンコード「0」が返ってきたら、先ほどと同様にフォルダを確認します。

CSVが上書きされていますね。テキストで中を確認します。

日付が表示されました。

このようにサーバーサイドコマンドでは日付が「少数」に変換されてしまうので、ビューを作り、strftime関数を使って「少数」から「テキスト」に変換させるといった作業が必要です。
困った際にはぜひ活用してみてくださいね。

４．strftime関数の応用

例えば、SQLを使って月末や月初を求めたいときはないでしょうか。
以下の方法で簡単にできますので是非ご活用してくださいね。
※加算は+を省きますが、減算は-を付けてください

SELECT
ID,strftime('%Y/%m/%d',日付+2415018.5) as "日付"
,strftime('%Y/%m/%d',日付+2415018.5,'start of month') as "当月月初日"
,strftime('%Y/%m/%d',日付+2415018.5,'1 months','start of month','-1 day') as "当月末日"
,strftime('%Y/%m/%d',日付+2415018.5,'1 months','start of month') as "翌月月初日"
,strftime('%Y/%m/%d',日付+2415018.5,'2 months','start of month','-1 day') as "翌月末日"
FROM "テーブル1";

「OK」ボタンを押下すると、図のような表が表示されます。

当月月初日・末日、翌月月初日・末日が求められましたね。

いかがでしたか。
今回は記事を通して、少し難しいSQLiteの日付の型変換やstrftime関数の使い方を見てきました。
今後のシステム開発に役立てていただけますと幸いです。