日付/時間および間隔関数と演算子

Elasticsearch SQLは、日付/時間の操作を行うための幅広い機能を提供します。

間隔

一般的に日付/時間を扱う際の共通の要件は、intervalの概念に関連しています。これは、ElasticsearchおよびElasticsearch SQLの文脈で探求する価値のあるトピックです。

Elasticsearchは、日付数学をインデックス名やクエリの中で包括的にサポートしています。Elasticsearch SQL内では、前者はテーブル名に式を渡すことでそのままサポートされ、後者は標準SQLのINTERVALを通じてサポートされています。

以下の表は、ElasticsearchとElasticsearch SQLの間のマッピングを示しています：

Elasticsearch Elasticsearch SQL
インデックス/テーブル日付数学
<index-{now/M{YYYY.MM}}>
クエリ日付/時間数学
1y INTERVAL 1 YEAR
2M INTERVAL 2 MONTH
3w INTERVAL 21 DAY
4d INTERVAL 4 DAY
5h INTERVAL 5 HOUR
6m INTERVAL 6 MINUTE
7s INTERVAL 7 SECOND

INTERVALは、YEARとMONTHを混ぜることができるまたはDAY、HOUR、MINUTE、SECONDを許可します。

Elasticsearch SQLは、各時間単位の複数形も受け入れます（例：YEARとYEARSの両方が有効です）。

以下は可能な組み合わせの例です：


| | |
| —- | —- |
| 間隔 | 説明 |
| INTERVAL '1-2' YEAR TO MONTH | 1年と2ヶ月 |
| INTERVAL '3 4' DAYS TO HOURS | 3日と4時間 |
| INTERVAL '5 6:12' DAYS TO MINUTES | 5日、6時間、12分 |
| INTERVAL '3 4:56:01' DAY TO SECOND | 3日、4時間、56分、1秒 |
| INTERVAL '2 3:45:01.23456789' DAY TO SECOND | 2日、3時間、45分、1秒、234567890ナノ秒 |
| INTERVAL '123:45' HOUR TO MINUTES | 123時間と45分 |
| INTERVAL '65:43:21.0123' HOUR TO SECONDS | 65時間、43分、21秒、12300000ナノ秒 |
| INTERVAL '45:01.23' MINUTES TO SECONDS | 45分、1秒、230000000ナノ秒 |

比較

日付/時間フィールドは、等号（=）およびIN演算子を使用して日付数学式と比較できます：

Sql

  1. SELECT hire_date FROM emp WHERE hire_date = '1987-03-01||+4y/y';
  3.    hire_date
  4. ------------------------
  5. 1991-01-26T00:00:00.000Z
  6. 1991-10-22T00:00:00.000Z
  7. 1991-09-01T00:00:00.000Z
  8. 1991-06-26T00:00:00.000Z
  9. 1991-08-30T00:00:00.000Z
  10. 1991-12-01T00:00:00.000Z

Sql

  1. SELECT hire_date FROM emp WHERE hire_date IN ('1987-03-01||+2y/M', '1987-03-01||+3y/M');
  3.    hire_date
  4. ------------------------
  5. 1989-03-31T00:00:00.000Z
  6. 1990-03-02T00:00:00.000Z

演算子

基本的な算術演算子（+、-、*）は、以下に示すように日付/時間パラメータをサポートしています：

Sql

  1. SELECT INTERVAL 1 DAY + INTERVAL 53 MINUTES AS result;
  3.    result
  4. ---------------
  5. +1 00:53:00

Sql

  1. SELECT CAST('1969-05-13T12:34:56' AS DATETIME) + INTERVAL 49 YEARS AS result;
  3.    result
  4. --------------------
  5. 2018-05-13T12:34:56Z

Sql

  1. SELECT - INTERVAL '49-1' YEAR TO MONTH result;
  3.    result
  4. ---------------
  5. -49-1

Sql

  1. SELECT INTERVAL '1' DAY - INTERVAL '2' HOURS AS result;
  3.    result
  4. ---------------
  5. +0 22:00:00

Sql

  1. SELECT CAST('2018-05-13T12:34:56' AS DATETIME) - INTERVAL '2-8' YEAR TO MONTH AS result;
  3.    result
  4. --------------------
  5. 2015-09-13T12:34:56Z

Sql

  1. SELECT -2 * INTERVAL '3' YEARS AS result;
  3.    result
  4. ---------------
  5. -6-0

関数

日付/時間を対象とした関数。

CURRENT_DATE/CURDATE

概要：

Sql

  1. CURRENT_DATE
  2. CURRENT_DATE()
  3. CURDATE()

入力: なし

出力: 日付

説明: 現在のクエリがサーバーに到達したときの日付（時間部分なし）を返します。キーワードとしても使用できます：CURRENT_DATEまたは引数なしの関数として：CURRENT_DATE()。

CURRENT_DATEとは異なり、CURDATE()は引数なしの関数としてのみ使用でき、キーワードとしては使用できません。

このメソッドは、同じクエリ内のすべての出現に対して常に同じ値を返します。

Sql

  1. SELECT CURRENT_DATE AS result;
  3.    result
  4. ------------------------
  5. 2018-12-12

Sql

  1. SELECT CURRENT_DATE() AS result;
  3.    result
  4. ------------------------
  5. 2018-12-12

Sql

  1. SELECT CURDATE() AS result;
  3.    result
  4. ------------------------
  5. 2018-12-12

通常、この関数（およびその双子の[TODAY()]関数）は、相対的な日付フィルタリングに使用されます：

Sql

  1. SELECT first_name FROM emp WHERE hire_date > TODAY() - INTERVAL 35 YEARS ORDER BY first_name ASC LIMIT 5;
  3.  first_name
  4. ------------
  5. Alejandro
  6. Amabile
  7. Anoosh
  8. Basil
  9. Bojan

CURRENT_TIME/CURTIME

概要：

Sql

  1. CURRENT_TIME
  2. CURRENT_TIME([precision])
  3. CURTIME

入力:

小数点以下の桁数; オプション

出力: 時間

説明: 現在のクエリがサーバーに到達したときの時間を返します。関数として、CURRENT_TIME()は精度をオプションのパラメータとして受け入れ、秒の小数点以下の桁数（ナノ秒）を丸めます。デフォルトの精度は3で、ミリ秒精度の現在の時間が返されます。

このメソッドは、同じクエリ内のすべての出現に対して常に同じ値を返します。

Sql

  1. SELECT CURRENT_TIME AS result;
  3.    result
  4. ------------------------
  5. 12:31:27.237Z

Sql

  1. SELECT CURRENT_TIME() AS result;
  3.    result
  4. ------------------------
  5. 12:31:27.237Z

Sql

  1. SELECT CURTIME() AS result;
  3.    result
  4. ------------------------
  5. 12:31:27.237Z

Sql

  1. SELECT CURRENT_TIME(1) AS result;
  3.    result
  4. ------------------------
  5. 12:31:27.2Z

通常、この関数は相対的な日付/時間フィルタリングに使用されます：

Sql

  1. SELECT first_name FROM emp WHERE CAST(hire_date AS TIME) > CURRENT_TIME() - INTERVAL 20 MINUTES ORDER BY first_name ASC LIMIT 5;
  3.   first_name
  4. ---------------
  5. Alejandro
  6. Amabile
  7. Anneke
  8. Anoosh
  9. Arumugam

現在、精度が6を超える場合、関数の出力には何の違いもありません。返される秒の小数点以下の最大桁数は6です。

CURRENT_TIMESTAMP

概要：

Sql

  1. CURRENT_TIMESTAMP
  2. CURRENT_TIMESTAMP([precision])

入力:

小数点以下の桁数; オプション

出力: 日付/時間

説明: 現在のクエリがサーバーに到達したときの日付/時間を返します。関数として、CURRENT_TIMESTAMP()は精度をオプションのパラメータとして受け入れ、秒の小数点以下の桁数（ナノ秒）を丸めます。デフォルトの精度は3で、ミリ秒精度の現在の日付/時間が返されます。

このメソッドは、同じクエリ内のすべての出現に対して常に同じ値を返します。

Sql

  1. SELECT CURRENT_TIMESTAMP AS result;
  3.    result
  4. ------------------------
  5. 2018-12-12T14:48:52.448Z

Sql

  1. SELECT CURRENT_TIMESTAMP() AS result;
  3.    result
  4. ------------------------
  5. 2018-12-12T14:48:52.448Z

Sql

  1. SELECT CURRENT_TIMESTAMP(1) AS result;
  3.    result
  4. ------------------------
  5. 2018-12-12T14:48:52.4Z

通常、この関数（およびその双子の[NOW()]関数）は、相対的な日付/時間フィルタリングに使用されます：

Sql

  1. SELECT first_name FROM emp WHERE hire_date > NOW() - INTERVAL 100 YEARS ORDER BY first_name ASC LIMIT 5;
  3.   first_name
  4. ---------------
  5. Alejandro
  6. Amabile
  7. Anneke
  8. Anoosh
  9. Arumugam

現在、精度が6を超える場合、関数の出力には何の違いもありません。返される秒の小数点以下の最大桁数は6です。

DATE_ADD/DATEADD/TIMESTAMP_ADD/TIMESTAMPADD

概要：

Sql

  1. DATE_ADD(
  2.    string_exp,
  3.    integer_exp,
  4.    datetime_exp)

入力:

日付/日付時刻に追加する日付/時間単位を示す文字列式。
もしnullの場合、関数はnullを返します。
上記の単位を日付/日付時刻に追加する回数を示す整数式。負の値が使用されると、日付/日付時刻からの引き算になります。もしnullの場合、関数はnullを返します。
日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 日付時刻

説明: 指定された数の日付/時間単位を日付/日付時刻に追加します。単位の数が負の場合は、日付/日付時刻から引かれます。

第二引数が長整数の場合、整数値がその長整数から抽出されて使用されるため、切り捨ての可能性があります。


| 追加/引き算する日付時刻単位 |
| :-: |
| 単位 | 略語 |
| 年 | 年、yy、yyyy |
| 四半期 | 四半期、qq、q |
| 月 | 月、mm、m |
| 年の日 | dy、y |
| 日 | 日、dd、d |
| 週 | 週、wk、ww |
| 曜日 | 曜日、dw |
| 時間 | 時間、hh |
| 分 | 分、mi、n |
| 秒 | 秒、ss、s |
| ミリ秒 | ミリ秒、ms |
| マイクロ秒 | マイクロ秒、mcs |
| ナノ秒 | ナノ秒、ns |

Sql

  1. SELECT DATE_ADD('years', 10, '2019-09-04T11:22:33.000Z'::datetime) AS "+10 years";
  3.    +10 years
  4. ------------------------
  5. 2029-09-04T11:22:33.000Z

Sql

  1. SELECT DATE_ADD('week', 10, '2019-09-04T11:22:33.000Z'::datetime) AS "+10 weeks";
  3.    +10 weeks
  4. ------------------------
  5. 2019-11-13T11:22:33.000Z

Sql

  1. SELECT DATE_ADD('seconds', -1234, '2019-09-04T11:22:33.000Z'::datetime) AS "-1234 seconds";
  3.    -1234 seconds
  4. ------------------------
  5. 2019-09-04T11:01:59.000Z

Sql

  1. SELECT DATE_ADD('qq', -417, '2019-09-04'::date) AS "-417 quarters";
  3.    -417 quarters
  4. ------------------------
  5. 1915-06-04T00:00:00.000Z

Sql

  1. SELECT DATE_ADD('minutes', 9235, '2019-09-04'::date) AS "+9235 minutes";
  3.    +9235 minutes
  4. ------------------------
  5. 2019-09-10T09:55:00.000Z

DATE_DIFF/DATEDIFF/TIMESTAMP_DIFF/TIMESTAMPDIFF

概要：

Sql

  1. DATE_DIFF(
  2.    string_exp,
  3.    datetime_exp,
  4.    datetime_exp)

入力:

次の2つの日付/日付時刻式の間の差を示す文字列式。もしnullの場合、関数はnullを返します。
開始日/日付時刻式。もしnullの場合、関数はnullを返します。
終了日/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 第2引数を第3引数から引き、その差を第1引数で指定された単位の倍数で返します。第2引数（開始）が第3引数（終了）より大きい場合、負の値が返されます。


| 日付時刻の差の単位 |
| :-: |
| 単位 | 略語 |
| 年 | 年、yy、yyyy |
| 四半期 | 四半期、qq、q |
| 月 | 月、mm、m |
| 年の日 | dy、y |
| 日 | 日、dd、d |
| 週 | 週、wk、ww |
| 曜日 | 曜日、dw |
| 時間 | 時間、hh |
| 分 | 分、mi、n |
| 秒 | 秒、ss、s |
| ミリ秒 | ミリ秒、ms |
| マイクロ秒 | マイクロ秒、mcs |
| ナノ秒 | ナノ秒、ns |

Sql

  1. SELECT DATE_DIFF('years', '2019-09-04T11:22:33.000Z'::datetime, '2032-09-04T22:33:11.000Z'::datetime) AS "diffInYears";
  3.    diffInYears
  4. ------------------------
  5. 13

Sql

  1. SELECT DATE_DIFF('week', '2019-09-04T11:22:33.000Z'::datetime, '2016-12-08T22:33:11.000Z'::datetime) AS "diffInWeeks";
  3.    diffInWeeks
  4. ------------------------
  5. -143

Sql

  1. SELECT DATE_DIFF('seconds', '2019-09-04T11:22:33.123Z'::datetime, '2019-07-12T22:33:11.321Z'::datetime) AS "diffInSeconds";
  3.    diffInSeconds
  4. ------------------------
  5. -4625362

Sql

  1. SELECT DATE_DIFF('qq', '2019-09-04'::date, '2025-04-25'::date) AS "diffInQuarters";
  3.    diffInQuarters
  4. ------------------------
  5. 23
  3. #### Sql
  5. ``````sql
  6. SELECT DATEDIFF('hours', '2019-11-10T12:10:00.000Z'::datetime, '2019-11-10T23:59:59.999Z'::datetime) AS "diffInHours";
  8.    diffInHours
  9. ------------------------
  10. 11
  11. `

Sql

  1. SELECT DATEDIFF('minute', '2019-11-10T12:10:00.000Z'::datetime, '2019-11-10T12:15:59.999Z'::datetime) AS "diffInMinutes";
  3.    diffInMinutes
  4. ------------------------
  5. 5

Sql

  1. SELECT DATE_DIFF('minutes', '2019-09-04'::date, '2015-08-17T22:33:11.567Z'::datetime) AS "diffInMinutes";
  3.    diffInMinutes
  4. ------------------------
  5. -2128407

DATE_FORMAT

概要：

Sql

  1. DATE_FORMAT(
  2.    date_exp/datetime_exp/time_exp,
  3.    string_exp)

入力:

日付/日付時刻/時間式。もしnullの場合、関数はnullを返します。
フォーマットパターン。もしnullまたは空文字列の場合、関数はnullを返します。

出力: 文字列

説明: 第2引数で指定されたフォーマットを使用して、日付/日付時刻/時間を文字列として返します。フォーマットパターンは、MySQL DATE_FORMAT()関数で使用される指定子の1つです。

第1引数がtime型の場合、第2引数で指定されたパターンには日付関連の単位（例：dd、MM、yyyyなど）を含めることはできません。そのような単位が含まれている場合、エラーが返されます。月と日指定子の範囲（%c、%D、%d、%e、%m）は1から始まりますが、MySQLでは0から始まるため、MySQLは2014-00-00のような不完全な日付の保存を許可します。この場合、Elasticsearchはエラーを返します。

Sql

  1. SELECT DATE_FORMAT(CAST('2020-04-05' AS DATE), '%d/%m/%Y') AS "date";
  3.    date
  4. ------------------
  5. 05/04/2020

Sql

  1. SELECT DATE_FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), '%d/%m/%Y %H:%i:%s.%f') AS "datetime";
  3.    datetime
  4. ------------------
  5. 05/04/2020 11:22:33.987654

Sql

  1. SELECT DATE_FORMAT(CAST('23:22:33.987' AS TIME), '%H %i %s.%f') AS "time";
  3.    time
  4. ------------------
  5. 23 22 33.987000

DATE_PARSE

概要：

Sql

  1. DATE_PARSE(
  2.    string_exp,
  3.    string_exp)

入力:

文字列としての日付式。もしnullまたは空文字列の場合、関数はnullを返します。
解析パターン。もしnullまたは空文字列の場合、関数はnullを返します。

出力: 日付

説明: 第1引数を第2引数で指定されたフォーマットを使用して解析することにより、日付を返します。使用される解析フォーマットパターンは、java.time.format.DateTimeFormatterのものです。

解析パターンにすべての有効な日付単位（例：HH:mm:ss、dd-MM HH:mm:ssなど）が含まれていない場合、関数はdate型の値を返す必要があるため、エラーが返されます。

Sql

  1. SELECT DATE_PARSE('07/04/2020', 'dd/MM/yyyy') AS "date";
  3.    date
  4. -----------
  5. 2020-04-07

結果のdateは、ユーザーがtime_zone/timezone REST/ドライバパラメータを通じて指定したタイムゾーンを持ち、変換は適用されません。

Sql

  1. {
  2.    "query" : "SELECT DATE_PARSE('07/04/2020', 'dd/MM/yyyy') AS \"date\"",
  3.    "time_zone" : "Europe/Athens"
  4. }
  6.    date
  7. ------------
  8. 2020-04-07T00:00:00.000+03:00

DATETIME_FORMAT

概要：

Sql

  1. DATETIME_FORMAT(
  2.    date_exp/datetime_exp/time_exp,
  3.    string_exp)

入力:

日付/日付時刻/時間式。もしnullの場合、関数はnullを返します。
フォーマットパターン。もしnullまたは空文字列の場合、関数はnullを返します。

出力: 文字列

説明: 第2引数で指定されたフォーマットを使用して、日付/日付時刻/時間を文字列として返します。使用されるフォーマットパターンは、java.time.format.DateTimeFormatterのものです。

第1引数がtime型の場合、第2引数で指定されたパターンには日付関連の単位（例：dd、MM、yyyyなど）を含めることはできません。そのような単位が含まれている場合、エラーが返されます。

Sql

  1. SELECT DATETIME_FORMAT(CAST('2020-04-05' AS DATE), 'dd/MM/yyyy') AS "date";
  3.    date
  4. ------------------
  5. 05/04/2020

Sql

  1. SELECT DATETIME_FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'dd/MM/yyyy HH:mm:ss.SS') AS "datetime";
  3.    datetime
  4. ------------------
  5. 05/04/2020 11:22:33.98

Sql

  1. SELECT DATETIME_FORMAT(CAST('11:22:33.987' AS TIME), 'HH mm ss.S') AS "time";
  3.    time
  4. ------------------
  5. 11 22 33.9

DATETIME_PARSE

概要：

Sql

  1. DATETIME_PARSE(
  2.    string_exp,
  3.    string_exp)

入力:

文字列としての日付時刻式。もしnullまたは空文字列の場合、関数はnullを返します。
解析パターン。もしnullまたは空文字列の場合、関数はnullを返します。

出力: 日付時刻

説明: 第1引数を第2引数で指定されたフォーマットを使用して解析することにより、日付時刻を返します。使用される解析フォーマットパターンは、java.time.format.DateTimeFormatterのものです。

解析パターンに日付または時間の単位のみ（例：dd/MM/yyyy、HH:mm:ssなど）が含まれている場合、関数はdatetime型の値を返す必要があるため、エラーが返されます。

Sql

  1. SELECT DATETIME_PARSE('07/04/2020 10:20:30.123', 'dd/MM/yyyy HH:mm:ss.SSS') AS "datetime";
  3.    datetime
  4. ------------------------
  5. 2020-04-07T10:20:30.123Z

Sql

  1. SELECT DATETIME_PARSE('10:20:30 07/04/2020 Europe/Berlin', 'HH:mm:ss dd/MM/yyyy VV') AS "datetime";
  3.    datetime
  4. ------------------------
  5. 2020-04-07T08:20:30.000Z

日付時刻文字列式および解析パターンにタイムゾーンが指定されていない場合、結果のdatetimeは、ユーザーがtime_zone/timezone REST/ドライバパラメータを通じて指定したタイムゾーンを持ち、変換は適用されません。

Sql

  1. {
  2.    "query" : "SELECT DATETIME_PARSE('10:20:30 07/04/2020', 'HH:mm:ss dd/MM/yyyy') AS \"datetime\"",
  3.    "time_zone" : "Europe/Athens"
  4. }
  6.    datetime
  7. -----------------------------
  8. 2020-04-07T10:20:30.000+03:00

TIME_PARSE

概要：

Sql

  1. TIME_PARSE(
  2.    string_exp,
  3.    string_exp)

入力:

文字列としての時間式。もしnullまたは空文字列の場合、関数はnullを返します。
解析パターン。もしnullまたは空文字列の場合、関数はnullを返します。

出力: 時間

説明: 第1引数を第2引数で指定されたフォーマットを使用して解析することにより、時間を返します。使用される解析フォーマットパターンは、java.time.format.DateTimeFormatterのものです。

解析パターンに日付の単位のみ（例：dd/MM/yyyy）が含まれている場合、関数はtime型の値を返す必要があるため、エラーが返されます。

Sql

  1. SELECT TIME_PARSE('10:20:30.123', 'HH:mm:ss.SSS') AS "time";
  3.    time
  4. ---------------
  5. 10:20:30.123Z

Sql

  1. SELECT TIME_PARSE('10:20:30-01:00', 'HH:mm:ssXXX') AS "time";
  3.    time
  4. ---------------
  5. 11:20:30.000Z

時間文字列式および解析パターンにタイムゾーンが指定されていない場合、結果のtimeは、ユーザーがtime_zone/timezone REST/ドライバパラメータを通じて指定したタイムゾーンのオフセットを持ち、Unixエポック日（1970-01-01）で変換は適用されません。

Sql

  1. {
  2.    "query" : "SELECT DATETIME_PARSE('10:20:30', 'HH:mm:ss') AS \"time\"",
  3.    "time_zone" : "Europe/Athens"
  4. }
  6.    time
  7. ------------------------------------
  8. 10:20:30.000+02:00

DATE_PART/DATEPART

概要：

Sql

  1. DATE_PART(
  2.    string_exp,
  3.    datetime_exp)

入力:

日付/日付時刻から抽出する単位を示す文字列式。もしnullの場合、関数はnullを返します。
日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日付時刻から指定された単位を抽出します。これは、EXTRACTに似ていますが、単位の名前とエイリアスが異なり、より多くのオプションを提供します（例：TZOFFSET）。


| 抽出する日付時刻単位 |
| :-: |
| 単位 | 略語 |
| 年 | 年、yy、yyyy |
| 四半期 | 四半期、qq、q |
| 月 | 月、mm、m |
| 年の日 | dy、y |
| 日 | 日、dd、d |
| 週 | 週、wk、ww |
| 曜日 | 曜日、dw |
| 時間 | 時間、hh |
| 分 | 分、mi、n |
| 秒 | 秒、ss、s |
| ミリ秒 | ミリ秒、ms |
| マイクロ秒 | マイクロ秒、mcs |
| ナノ秒 | ナノ秒、ns |
| tzoffset | tz |

Sql

  1. SELECT DATE_PART('year', '2019-09-22T11:22:33.123Z'::datetime) AS "years";
  3.    years
  4. ----------
  5. 2019

Sql

  1. SELECT DATE_PART('mi', '2019-09-04T11:22:33.123Z'::datetime) AS mins;
  3.    mins
  4. -----------
  5. 22

Sql

  1. SELECT DATE_PART('quarters', CAST('2019-09-24' AS DATE)) AS quarter;
  3.    quarter
  4. -------------
  5. 3

Sql

  1. SELECT DATE_PART('month', CAST('2019-09-24' AS DATE)) AS month;
  3.    month
  4. -------------
  5. 9
  3. #### Sql
  5. ``````sql
  6. SELECT DATE_PART('week', '2019-09-22T11:22:33.123Z'::datetime) AS week;
  8.    week
  9. ----------
  10. 39
  11. `
  3. #### Sql
  5. ``````sql
  6. SELECT DATE_PART('tzoffset', '2019-09-04T11:22:33.123+05:15'::datetime) AS tz_mins;
  8.    tz_mins
  9. --------------
  10. 315
  11. `

Sql

  1. SELECT DATE_PART('tzoffset', '2019-09-04T11:22:33.123-03:49'::datetime) AS tz_mins;
  3.    tz_mins
  4. --------------
  5. -229

DATE_TRUNC/DATETRUNC

概要：

Sql

  1. DATE_TRUNC(
  2.    string_exp,
  3.    datetime_exp/interval_exp)

入力:

日付/日付時刻/間隔を切り捨てる単位を示す文字列式。もしnullの場合、関数はnullを返します。
日付/日付時刻/間隔式。もしnullの場合、関数はnullを返します。

出力: 日付時刻/間隔

説明: 指定された単位に切り捨てることにより、日付/日付時刻/間隔を指定された単位に切り捨てます。指定された単位よりも重要度の低いすべてのフィールドをゼロ（または、日、曜日、月の場合は1）に設定します。最初の引数がweekで、第二引数がinterval型の場合、intervalデータ型はweek時間単位をサポートしていないため、エラーが発生します。


| 日付時刻の切り捨て単位 |
| :-: |
| 単位 | 略語 |
| 千年 | 千年 |
| 世紀 | 世紀 |
| 十年 | 十年 |
| 年 | 年、yy、yyyy |
| 四半期 | 四半期、qq、q |
| 月 | 月、mm、m |
| 週 | 週、wk、ww |
| 日 | 日、dd、d |
| 時間 | 時間、hh |
| 分 | 分、mi、n |
| 秒 | 秒、ss、s |
| ミリ秒 | ミリ秒、ms |
| マイクロ秒 | マイクロ秒、mcs |
| ナノ秒 | ナノ秒、ns |

Sql

  1. SELECT DATE_TRUNC('millennium', '2019-09-04T11:22:33.123Z'::datetime) AS millennium;
  3.    millennium
  4. ------------------------
  5. 2000-01-01T00:00:00.000Z

Sql

  1. SELECT DATETRUNC('week', '2019-08-24T11:22:33.123Z'::datetime) AS week;
  3.    week
  4. ------------------------
  5. 2019-08-19T00:00:00.000Z

Sql

  1. SELECT DATE_TRUNC('mi', '2019-09-04T11:22:33.123Z'::datetime) AS mins;
  3.    mins
  4. ------------------------
  5. 2019-09-04T11:22:00.000Z

Sql

  1. SELECT DATE_TRUNC('decade', CAST('2019-09-04' AS DATE)) AS decades;
  3.    decades
  4. ------------------------
  5. 2010-01-01T00:00:00.000Z

Sql

  1. SELECT DATETRUNC('quarters', CAST('2019-09-04' AS DATE)) AS quarter;
  3.    quarter
  4. ------------------------
  5. 2019-07-01T00:00:00.000Z

Sql

  1. SELECT DATE_TRUNC('centuries', INTERVAL '199-5' YEAR TO MONTH) AS centuries;
  3.    centuries
  4. ------------------
  5.  +100-0

Sql

  1. SELECT DATE_TRUNC('hours', INTERVAL '17 22:13:12' DAY TO SECONDS) AS hour;
  3.    hour
  4. ------------------
  5. +17 22:00:00

Sql

  1. SELECT DATE_TRUNC('days', INTERVAL '19 15:24:19' DAY TO SECONDS) AS day;
  3.    day
  4. ------------------
  5. +19 00:00:00

FORMAT

概要：

Sql

  1. FORMAT(
  2.    date_exp/datetime_exp/time_exp,
  3.    string_exp)

入力:

日付/日付時刻/時間式。もしnullの場合、関数はnullを返します。
フォーマットパターン。もしnullまたは空文字列の場合、関数はnullを返します。

出力: 文字列

説明: 第2引数で指定されたフォーマットを使用して、日付/日付時刻/時間を文字列として返します。使用されるフォーマットパターンは、Microsoft SQL Server Format Specificationのものです。

第1引数がtime型の場合、第2引数で指定されたパターンには日付関連の単位（例：dd、MM、yyyyなど）を含めることはできません。そのような単位が含まれている場合、エラーが返されます。

フォーマット指定子Fは、フォーマット指定子fと同様に機能します。秒の小数部分を返し、桁数は入力として提供されたFsの数と同じになります（最大9桁）。結果には、0が末尾に追加され、Fの数と一致します。例えば、時間部分が10:20:30.1234で、パターンがHH:mm:ss.FFFFFFの場合、関数の出力文字列は10:20:30.123400になります。

フォーマット指定子yは、1桁または2桁の低位数字の代わりに年の時代を返します。例えば、年2009の場合、yは2009を返し、43の場合、yフォーマット指定子は43を返します。 - "、\、%のような特殊文字は、変更なしでそのまま返されます。例えば、17-sep-2020を%Mでフォーマットすると、%9が返されます。

Sql

  1. SELECT FORMAT(CAST('2020-04-05' AS DATE), 'dd/MM/yyyy') AS "date";
  3.    date
  4. ------------------
  5. 05/04/2020

Sql

  1. SELECT FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'dd/MM/yyyy HH:mm:ss.ff') AS "datetime";
  3.    datetime
  4. ------------------
  5. 05/04/2020 11:22:33.98

Sql

  1. SELECT FORMAT(CAST('11:22:33.987' AS TIME), 'HH mm ss.f') AS "time";
  3.    time
  4. ------------------
  5. 11 22 33.9

TO_CHAR

概要：

Sql

  1. TO_CHAR(
  2.    date_exp/datetime_exp/time_exp,
  3.    string_exp)

入力:

日付/日付時刻/時間式。もしnullの場合、関数はnullを返します。
フォーマットパターン。もしnullまたは空文字列の場合、関数はnullを返します。

出力: 文字列

説明: 第2引数で指定されたフォーマットを使用して、日付/日付時刻/時間を文字列として返します。使用されるフォーマットパターンは、PostgreSQL Template Patterns for Date/Time Formattingに準拠しています。

第1引数がtime型の場合、第2引数で指定されたパターンには日付関連の単位（例：dd、MM、YYYYなど）を含めることはできません。そのような単位が含まれている場合、エラーが返されます。

パターンTZ、tz（タイムゾーンの略語）の結果は、場合によってはPostgreSQLで返されるTO_CHARの結果と異なることがあります。理由は、JDKで指定されたタイムゾーンの略語がPostgreSQLで指定されたものと異なるためです。この関数は、PostgreSQL実装によって返される一般的なLMTまたは空文字列またはオフセットの代わりに、実際のタイムゾーンの略語を示す場合があります。夏時間/昼間のマーカーも、2つの実装間で異なる場合があります（例：ハワイの場合、HTの代わりにHSTを示します）。 

  3. #### Sql
  5. ``````sql
  6. SELECT TO_CHAR(CAST('2020-04-05' AS DATE), 'DD/MM/YYYY') AS "date";
  8.    date
  9. ------------------
  10. 05/04/2020
  11. `

Sql

  1. SELECT TO_CHAR(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'DD/MM/YYYY HH24:MI:SS.FF2') AS "datetime";
  3.    datetime
  4. ------------------
  5. 05/04/2020 11:22:33.98

Sql

  1. SELECT TO_CHAR(CAST('23:22:33.987' AS TIME), 'HH12 MI SS.FF1') AS "time";
  3.    time
  4. ------------------
  5. 11 22 33.9

DAY_OF_MONTH/DOM/DAY

概要：

Sql

  1. DAY_OF_MONTH(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日付時刻から月の日を抽出します。

Sql

  1. SELECT DAY_OF_MONTH(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;
  3.    day
  4. ---------------
  5. 19

DAY_OF_WEEK/DAYOFWEEK/DOW

概要：

Sql

  1. DAY_OF_WEEK(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日付時刻から週の日を抽出します。日曜日は1、月曜日は2などです。

Sql

  1. SELECT DAY_OF_WEEK(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;
  3.    day
  4. ---------------
  5. 2

DAY_OF_YEAR/DOY

概要：

Sql

  1. DAY_OF_YEAR(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日付時刻から年の日を抽出します。

Sql

  1. SELECT DAY_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;
  3.    day
  4. ---------------
  5. 50

DAY_NAME/DAYNAME

概要：

Sql

  1. DAY_NAME(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 文字列

説明: 日付/日付時刻から曜日をテキスト形式で抽出します（Monday、Tuesday…）。

Sql

  1. SELECT DAY_NAME(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;
  3.    day
  4. ---------------
  5. Monday

HOUR_OF_DAY/HOUR

概要：

Sql

  1. HOUR_OF_DAY(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日付時刻から日の時間を抽出します。

Sql

  1. SELECT HOUR_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS hour;
  3.    hour
  4. ---------------
  5. 10

ISO_DAY_OF_WEEK/ISODAYOFWEEK/ISODOW/IDOW

概要：

Sql

  1. ISO_DAY_OF_WEEK(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日時から曜日を抽出します。これはISO 8601標準に従います。月曜日は1、火曜日は2、などです。

Sql

  1. SELECT ISO_DAY_OF_WEEK(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;
  3.    day
  4. ---------------
  5. 1

ISO_WEEK_OF_YEAR/ISOWEEKOFYEAR/ISOWEEK/IWOY/IW

概要：

Sql

  1. ISO_WEEK_OF_YEAR(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日時から年の週を抽出します。これはISO 8601標準に従います。年の最初の週は、1月に4日以上ある最初の週です。

Sql

  1. SELECT ISO_WEEK_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS week;
  3.    week
  4. ---------------
  5. 8

MINUTE_OF_DAY

概要：

Sql

  1. MINUTE_OF_DAY(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日時からその日の分を抽出します。

Sql

  1. SELECT MINUTE_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS minute;
  3.    minute
  4. ---------------
  5. 623

MINUTE_OF_HOUR/MINUTE

概要：

Sql

  1. MINUTE_OF_HOUR(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日時からその時間の分を抽出します。

Sql

  1. SELECT MINUTE_OF_HOUR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS minute;
  3.    minute
  4. ---------------
  5. 23

MONTH_OF_YEAR/MONTH

概要：

Sql

  1. MONTH(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日時からその年の月を抽出します。

Sql

  1. SELECT MONTH_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS month;
  3.    month
  4. ---------------
  5. 2

MONTH_NAME/MONTHNAME

概要：

Sql

  1. MONTH_NAME(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 文字列

説明: 日付/日時からテキスト形式で月を抽出します（January、February…）。

Sql

  1. SELECT MONTH_NAME(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS month;
  3.    month
  4. ---------------
  5. February

NOW

概要：

Sql

  1. NOW()

入力: なし

出力: 日付時刻

説明: この関数はCURRENT_TIMESTAMP()関数と同じ機能を提供します：現在のクエリがサーバーに到達したときの日時を返します。このメソッドは、同じクエリ内のすべての出現に対して常に同じ値を返します。

Sql

  1. SELECT NOW() AS result;
  3.    result
  4. ------------------------
  5. 2018-12-12T14:48:52.448Z

通常、この関数（およびその双子のCURRENT_TIMESTAMP()関数）は、相対的な日付/時間フィルタリングに使用されます：

Sql

  1. SELECT first_name FROM emp WHERE hire_date > NOW() - INTERVAL 100 YEARS ORDER BY first_name ASC LIMIT 5;
  3.   first_name
  4. ---------------
  5. Alejandro
  6. Amabile
  7. Anneke
  8. Anoosh
  9. Arumugam

SECOND_OF_MINUTE/SECOND

概要：

Sql

  1. SECOND_OF_MINUTE(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日時からその分の秒を抽出します。

Sql

  1. SELECT SECOND_OF_MINUTE(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS second;
  3.    second
  4. ---------------
  5. 27

QUARTER

概要：

Sql

  1. QUARTER(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日時が属する年の四半期を抽出します。

Sql

  1. SELECT QUARTER(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS quarter;
  3.    quarter
  4. ---------------
  5. 1

TODAY

概要：

Sql

  1. TODAY()

入力: なし

出力: 日付

説明: この関数はCURRENT_DATE()関数と同じ機能を提供します：現在のクエリがサーバーに到達したときの日付を返します。このメソッドは、同じクエリ内のすべての出現に対して常に同じ値を返します。

Sql

  1. SELECT TODAY() AS result;
  3.    result
  4. ------------------------
  5. 2018-12-12

通常、この関数（およびその双子のCURRENT_TIMESTAMP()関数）は、相対的な日付フィルタリングに使用されます：

Sql

  1. SELECT first_name FROM emp WHERE hire_date > TODAY() - INTERVAL 35 YEARS ORDER BY first_name ASC LIMIT 5;
  3.  first_name
  4. ------------
  5. Alejandro
  6. Amabile
  7. Anoosh
  8. Basil
  9. Bojan

WEEK_OF_YEAR/WEEK

概要：

Sql

  1. WEEK_OF_YEAR(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日時から年の週を抽出します。

Sql

  1. SELECT WEEK(CAST('1988-01-05T09:22:10Z' AS TIMESTAMP)) AS week, ISOWEEK(CAST('1988-01-05T09:22:10Z' AS TIMESTAMP)) AS isoweek;
  3.    week     |   isoweek
  4. ---------------+---------------
  5. 2              |1

YEAR

概要：

Sql

  1. YEAR(datetime_exp)

入力:

日付/日付時刻式。もしnullの場合、関数はnullを返します。

出力: 整数

説明: 日付/日時から年を抽出します。

Sql

  1. SELECT YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS year;
  3.    year
  4. ---------------
  5. 2018

EXTRACT

概要：

Sql

  1. EXTRACT(
  2.    datetime_function
  3.    FROM datetime_exp)

入力:

日付/時間関数名
日付/日時式

出力: 整数

説明: 日時関数の名前を指定して、日付/日時からフィールドを抽出します。以下は

Sql

  1. SELECT EXTRACT(DAY_OF_YEAR FROM CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;
  3.    day
  4. ---------------
  5. 50

これは次のものと同等です

Sql

  1. SELECT DAY_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;
  3.    day
  4. ---------------
  5. 50