SQLの時間データの取り扱い

データベース内で扱う最も重要なデータ型の一つは時間データです.時間データは、途中の状態を表すデータである.時間データを扱う複雑さのいくつかは、単一の日付時刻を記述する多数の方法によって引き起こされる.他の複雑なものはこの種のデータを操作することです.この記事では、データ型とこのデータ型を扱ういくつかの機能の概要を説明します.

概要
PostgreSQLの6つの日付/時刻型があります.日付は、日付、時刻、タイムスタンプ、および間隔です.


タイムスタンプ、日付と時刻
PostgreSQLでは、タイムスタンプデータ型はISO - 8601形式に従います.


間隔
SQLでは、2つの日付/時刻のソースの違いを保存することができます.この値を保持するデータ型はintervalと呼ばれます.簡単に2つの日付または時刻の値を減算することにより、アクションの間隔を見ることができます

SELECT CAST('2021-12-16' AS timestamp) - CAST('2021-12-11' AS timestamp) as interval;

 interval
---------------
 5 days

間隔のキーワードを使用して間隔を定義することができます量、単位、および任意の方向が続きます.

次のクエリは、間隔を定義できるいくつかの方法を示します.

SELECT INTERVAL '1 day' as i1,
       INTERVAL '2 days 2 hours' as i2,
       INTERVAL '1D 2H' as i3;

  i1 | i2 | i3
------------+-----------------+----------------
 1 day | 2 days 02:00:00 | 1 day 02:00:00

日、時間、分、秒の量は、単位のマークなしで定義することができます.例えば、' 2 01 : 35 : 09 'は' 2日1時間35分10秒'と同じです.
間隔を定義する他の方法があります.たとえば、年と月の組み合わせをダッシュで指定することができます例えば「1 - 2」は「1年2ヶ月」と同じです.

SELECT INTERVAL '1-2',
       INTERVAL '1-2' YEAR TO MONTH;

   interval | interval
--------------------+---------------
 1 year 2 mons | 1 year 2 mons

しかし、私はあなたがinterval '<quantity> <unit>'フォーマットに固執しなければならないと思います、そして、それは読んで、理解するのがより簡単です.

タイムゾーン
タイムゾーンは複雑なトピックです、そして、彼らは単に地球幾何学でありません;政治的な決定があります.それで、私はここで短くなります.
世界はタイムゾーンと呼ばれる24の虚数部に分けられる.一部の地域は、1年に2回(夏時間として知られていることを実行する)時間で彼らの時間をシフトさせるので、地球上の2つの点の間の時間差は、年の半分のための4時間と1年の残りの半分のための5時間であるかもしれません.タイムゾーンの中でさえ、異なる地域は夏時間に従うことができません.そして、同じタイムゾーンで異なる時計が1年の半分の間に同意するが、1年の残りのために1時間異なることを引き起こします.
この問題に対処するために、人々はタイムキーピングのためのリファレンスを使用しました.15世紀のナビゲータは、グリニッジ標準時やGMTとして知られているグリニッジ、イングランドで一日の時間に自分たちの時計を設定します.
今日、我々は原子時計(または、より正確には、世界的な50の場所で200の原子時計の平均時間、普遍的な時間と呼ばれる)に基づく協調ユニバーサル時間またはUTCを使用します.あなたはUTCから時間差の数で他のタイムゾーンを記述することができますたとえば、インドネシアのジャカルタのタイムゾーンをUTC + 7 : 00と定義できます.
SQLでは、日付型にはタイムゾーン情報がありませんが、時刻とタイムスタンプの種類の両方にはタイムゾーン情報があります.これは時間帯は、特に時間を節約するために日光を使用するときに日付なしでほとんど意味がないので、混乱を得ることができます.タイムゾーンで作業する必要がある場合は、タイムスタンプ値で動作します.
内部的には、全てのタイムゾーンを意識した日付と時刻をUTCに格納する.表示される前に現地時間に変換されます.タイムゾーンの設定でローカル時刻が指定されます.

演算子
SQLでは、日付と整数の間に、算術演算子を適用できます.

上記の算術演算子は別として、比較演算子( <, >, <= , >, =, <>, != )を使うこともできます.日付とタイムスタンプのために、それらはすべてタイムゾーンの有無に匹敵する.しかし、時間(時間帯の有無にかかわらず)と間隔は、同じデータ型の他の値と比較することができます.
タイムゾーンのないタイムスタンプをタイムゾーンで比較する場合、タイムゾーンのないタイムスタンプはタイムゾーン設定で指定されたタイムゾーンに与えられます.その後、タイムゾーン(UTCとして内部的に格納されます)を持つタイムスタンプと比較するためにUTCに変換されます.

関数

現在の日付/時刻の取得

タイムスタンプ
現在のタイムスタンプを取得する

SELECT CURRENT_TIMESTAMP;

       current_timestamp
------------------------------------
 2021-11-12 13:02:56.338088+07

CurrentCountタイムスタンプの構文では、末尾にペアのペアを必要としません.しかし、あなたは精度パラメータで関数を呼ぶことができます.

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(0);

       current_timestamp | current_timestamp
------------------------------------+------------------------
 2021-11-12 13:26:25.138509+07 | 2021-11-12 13:26:25+07

奇妙に、括弧で関数を呼び出すことはできません.あなたがこれを試みるならば、あなたは構文エラーメッセージを得ています.

現在の日付
現在の日付を取得します.

SELECT CURRENT_DATE;

 current_date
-------------------
 2021-11-12

時限
現在の時刻を取得します.

SELECT CURRENT_TIME;

    current_time
-------------------------
 13:00:30.898362+07

CurrentCountタイムスタンプのように、currentRange時間は精度パラメータを受け入れることもできます.

SELECT CURRENT_TIME, CURRENT_TIME(0);

    current_time | current_time
-------------------------+--------------
 13:31:13.307465+07 | 13:31:13+07

現在
現在のタイムスタンプを取得します.

SELECT NOW();

              now
------------------------------------
 2021-11-12 13:03:04.731898+07

返り値はcurrentCountタイムスタンプの出力と同じです.

SELECT NOW(), CURRENT_TIMESTAMP;

              now | current_timestamp
 -----------------+-------------------------------
 2021-11-12 13:07:29.248457+07 | 2021-11-12 13:07:29.248457+07

タイムゾーンの処理
タイムゾーンに対処するには、タイムゾーン演算子で利用できます.タイムゾーンでタイムスタンプをタイムスタンプでタイムスタンプに変換します.この演算子で使用する2つの形式があります.

次のクエリは、動作中のATタイムゾーン条項を示します.
形式1

SELECT TIMESTAMP '2021-11-16 15:00' AS time_no_tz,
       TIMESTAMP '2021-11-16 15:00' AT TIME ZONE 'America/Montreal' AS local_time;

          time_no_tz | local_time
  -------------------+------------------------
 2021-11-16 15:00:00 | 2021-11-17 03:00:00+07

形式2

SELECT CURRENT_TIMESTAMP AS time_with_tz,
       CURRENT_TIMESTAMP AT TIME ZONE 'America/Montreal' AS in_montreal_no_tz;

                  time_with_tz | in_montreal_no_tz
------------------------------------+----------------------------
 2021-11-16 15:32:03.210247+07 | 2021-11-16 03:32:03.210247

年齢
age関数は2つのタイムスタンプの違いを返します.

AGE(timestamp1, timestamp2)


SELECT AGE(timestamp '2021-11-12 00:00:00', timestamp '2020-09-09 12:00:00');

              age
------------------------------------
 1 year 2 mons 2 days 12:00:00

関数の1つだけのタイムスタンプを指定した場合、AGE ()は現在の時刻から真夜中までのタイムスタンプを減算します.

SELECT AGE(timestamp '2009-09-12 10:11:00');

               age
--------------------------------------
 12 years 1 mon 29 days 13:49:00

抽出日時
分析のためのデータを扱うとき、Timestamp精度を使用するコラムはしばしば時間の大部分で役に立ちません.または日付/時刻の値を標準化するタイムスタンプを変換/切り捨てする必要があります.PostgreSQLでは、extract ()、dateound part ()およびdatedent trunct ()関数を使用してこれを実現できます.

抽出とデータ部分
extract ()およびdateound part ()関数は日付/時刻の値の一部を取得します.

以下は、請求書の支払いを年間でグループ化した後、InvoiceRank日付欄から抽出したクォーターを受け取るためのクエリです.

SELECT 
    EXTRACT(year FROM invoice_date) AS year, 
    EXTRACT(quarter FROM invoice_date) AS quarter,
    SUM(total) as total_payments
FROM invoice
GROUP BY 1, 2
ORDER BY 1, 2;

 year | quarter | total_payments
-----------+---------+----------------
 2009 | 1 | 110.88
 2009 | 2 | 112.86
 2009 | 3 | 112.86
 2009 | 4 | 112.86
 2010 | 1 | 143.86
 2010 | 2 | 112.86
 2010 | 3 | 111.87
 2010 | 4 | 112.86
 2011 | 1 | 112.86
 2011 | 2 | 144.86
 2011 | 3 | 112.86
 2011 | 4 | 99.00
<---------- TRUNCATED ---------->

DATHERIGHT部分はExtractに似ています.部分を除いて、文字列である必要があります.DateHeight部分の有効な部分名は、Extractに対して同じです.
次のクエリは、前のセクションから同じクエリを取得します.

SELECT 
    DATE_PART('year', invoice_date) AS year, 
    DATE_PART('quarter', invoice_date) AS quarter,
    SUM(total) as total_payments
FROM invoice
GROUP BY 1, 2
ORDER BY 1, 2;

結果は同じである.

デカルトトラン
datetle trunct ()関数は、タイムスタンプまたはインターバルデータ型を切り捨てます.

フィールドは、ソース値を切り捨てる精度です.返り値はtimestampまたはinterval型です.選択された部分より重要でない戻り値は、0か1(日と月の間)に設定されます.

SELECT DATE_TRUNC('year', TIMESTAMP '2021-11-12 13:03:04') as year_truncated,
       DATE_TRUNC('month', TIMESTAMP '2021-11-12 13:03:04') as month_truncated;

     date_trunc | date_trunc
--------------------------+---------------------
 2021-01-01 00:00:00 | 2021-11-01 00:00:0000