【よく使われる関数】FORMAT_DATE関数

データ加工をする際に使用頻度の高い関数、知っておくと便利な関数について紹介しています。

今回は、FORMAT_DATE関数です。

FORMAT_DATE関数について

FORMAT_DATE関数とは、日付を指定した形で表示したいときに使用する関数です。
同じ日付でも、2024/03/28や2024-03-28といったように、たくさんの表示形式があります。

FORMAT_DATE関数を使えば、日時や日付の値を指定した形式に転換することができます。

また、「月だけ」「日だけ」「曜日だけ」といったように、希望の時間単位だけを取り出すこともできます。
例：2024/03/28の月だけを取り出す→3月

どんなときに使うのか？

日付の表示形式を揃えたいとき、見やすい形に変換したいときに、FORMAT_DATE関数は有用です。

また、上述したようにFORMAT_DATE関数は希望の時間単位を取り出すためにも使われます。
例えば、注文テーブルの日付カラムから月や曜日を取り出し、月ごと、曜日ごとの売り上げについて分析することもできます。

設定方法

では、Magiq上でFORMAT_DATE関数を設定する方法について紹介していきます。

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
【流れ】
①テーブル選択
②関数選択
③引数選択１：表示形式の選択
④引数選択２：フィールドの選択
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

注文テーブル内にある、注文日時フィールドを対象にやってみます。

①テーブル選択

取得したいフィールドに合わせてテーブルを選択してください。ここでは、注文日時を抽出するため、注文テーブル(“ec_sample_jp.order”)を選択します。

②関数選択

フィールド選択欄左のプルダウンから関数モードを選択し、“FORMAT_DATE”関数を選んでください。検索画面に関数名を入力することで、絞り込むことができます。(画像参照)

関数名を入力して絞り込む

③引数選択１：表示形式の選択

“FORMAT_DATE”関数の「引数１」の欄に、指定したい日時の表示形式を入力します。「引数２」のフィールド欄と逆にしてしまいエラーになるというケースが多いので、ご注意ください。

サンプルデータの注文テーブルにおいて、日付はハイフンを使った形式(例：2024-03-28)の形で格納されています。
今回は試しに、これを日本語表記(例：2024年03月28日)の形に変換してみることにします。

表示形式の指定は「文字列」モードを用いて行います。
「引数１」欄右のプルダウンから、「文字列」を選択します。

「文字列」を選択

「文字列」モードにして入力欄をクリックすると、Magiq上での日付指定フォーマットの一覧が表示されます。
一覧表とそれぞれについての説明は、以下のようになります。

例：2024年3月28日木曜日16時06分35秒

フォーマット	表示例	説明
%F	2024-03-28	ハイフンを使った一般的な日時の表示形式を指定します。
%Y	2024	日付から年部分の数字を抜き出します。
%m	03	日付から月部分の数字を抜き出します。 1～9月の場合は頭に0が付きます。
%d	28	日付から月部分の数字を抜き出します。 1～9日の場合は頭に0が付きます。
%H	16	時刻から時間部分の数字を抜き出します。 0～9時の場合は頭に0が付きます。
%M	06	時刻から分部分の数字を抜き出します。 0～9分の場合は頭に0が付きます。
%S	35	時刻から秒部分の数字を抜き出します。 0～9秒の場合には頭に0が付きます。
%T	16:06:35	コロン(：)を使った一般的な時刻の表示形式を指定します。
%A	Thursday	英語表記の曜日を抜き出します。
%a	Thu	英語表記の曜日を短縮形で抜き出します。
%w	5	曜日を数字表記(日曜日から数えて何日目か)で表示します。 頭に0はつきません。
%B	March	日付から月を英語表記で抜き出します。
%b	Mar	日付から月を短縮形の英語表記で抜き出します。
%V	13	ISO週番号を表示します。
%W	13	週番号を表示します。
%j	366	その年の日数を表示します。 基本は365ですが、うるう年の場合は366になります。
%P	PM	時刻がAMかPMかを表示します。
%n		改行をします。
%t		スペースを空けることができます。 (Tabキーと同じ)

今回は日本語表記(例：2024年03月28日)にしたいので、“%Y年%m月%d日”ということになります。

「引数１」の欄に入力します。

④引数選択２：フィールドの選択

フィールドを指定します。
関数の性格上、日付型またはタイムスタンプ型のフィールドを指定するようにしてください。

これで完成しました。

結果のプレビューは次のようになります。

表示形式を変えることができました。

指定の時間単位を抜き出す方法

上述したように、FORMAT_DATE関数を使うことで希望の時間単位を抜き出すことができます。

手順としては上とほぼ同じで、月だけを抜き出したい場合は「引数１」のところで“%m”などのように入力するだけです。

注文テーブルの注文日時から月だけを抜き出し、グループ化して月ごとの注文総額を集計してみます。

まず、FORMAT_DATE関数を設定します。月だけを抜き出したいので、「引数１」に入力するのは“%m”です。

月ごとの注文額の合計を出したいので、SUM関数を使います。引数は注文額フィールドです。

月ごとの集計なので、最後に忘れずにグループ化します。

無事に月ごとの注文総額を集計できました。

ここで、一つ注意点があります。
データが1年以上にわたる場合、月だけを抜き出すと例えば2022年10月と2023年10月が1つに集計されます。

サンプルデータは2022年10月～2022年12月のデータなので年をまたぎませんが、年をまたぐ場合には月だけではなく必要に応じて年も考慮するようにしましょう。

条件分岐を作りたい｜CASE関数の使い方