9.8. 日付/時刻関数と演算子

表9-26は、日付/時刻型の値の処理で使用可能な関数を示しています。 詳細は、以下の副節で説明します。 表9-25は、(+*等の)基本的な算術演算子の振舞いを説明しています。 書式設定関数については項9.7を参照してください。 項8.5を参照して、日付/時刻データ型についての背景となっている情報に精通していなければなりません。

後述のtimeもしくはtimestamp型の入力を受け取る関数および演算子はすべて、実際には2つの種類があります。 ひとつはtime with time zone型またはtimestamp with time zone型を取るもので、もう1つはtime without time zone型もしくはtimestamp without time zone型を取るものです。 省略のため、これらの種類の違いは個別に示していません。

表 9-25. 日付/時刻演算子

演算子名結果
+ date '2001-09-28' + integer '7'date '2001-10-05'
+ date '2001-09-28' + interval '1 hour'timestamp '2001-09-28 01:00'
+ date '2001-09-28' + time '03:00'timestamp '2001-09-28 03:00'
+ time '03:00' + date '2001-09-28'timestamp '2001-09-28 03:00'
+ interval '1 day' + interval '1 hour'interval '1 day 01:00'
+ timestamp '2001-09-28 01:00' + interval '23 hours'timestamp '2001-09-29 00:00'
+ time '01:00' + interval '3 hours'time '04:00'
+ interval '3 hours' + time '01:00'time '04:00'
- - interval '23 hours'interval '-23:00'
- date '2001-10-01' - date '2001-09-28'integer '3'
- date '2001-10-01' - integer '7'date '2001-09-24'
- date '2001-09-28' - interval '1 hour'timestamp '2001-09-27 23:00'
- time '05:00' - time '03:00'interval '02:00'
- time '05:00' - interval '2 hours'time '03:00'
- timestamp '2001-09-28 23:00' - interval '23 hours'timestamp '2001-09-28 00:00'
- interval '1 day' - interval '1 hour'interval '23:00'
- interval '2 hours' - time '05:00'time '03:00'
- timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'interval '1 day 15:00'
* double precision '3.5' * interval '1 hour'interval '03:30'
* interval '1 hour' * double precision '3.5'interval '03:30'
/ interval '1 hour' / double precision '1.5'interval '00:40'

表 9-26. 日付/時刻関数

関数名返り値型説明結果
age(timestamp)interval今日の日付からの減算age(timestamp '1957-06-13')43 years 8 mons 3 days
age(timestamp, timestamp)interval引数から減算age('2001-04-10', timestamp '1957-06-13')43 years 9 mons 27 days
current_datedate本日の日付、項9.8.4を参照。   
current_timetime with time zone本日の時刻、項9.8.4を参照。   
current_timestamptimestamp with time zone日付と時刻、項9.8.4を参照。   
date_part(text, timestamp)double precision部分フィールドの取得。 (extractと同じ)。 項9.8.1を参照。 date_part('hour', timestamp '2001-02-16 20:38:40')20
date_part(text, interval)double precision部分フィールドの取得。 (extractと同じ)。 項9.8.1を参照。 date_part('month', interval '2 years 3 months')3
date_trunc(text, timestamp)timestamp指定された精度で切捨て。 項9.8.2も参照。 date_trunc('hour', timestamp '2001-02-16 20:38:40')2001-02-16 20:00:00
extract(field from timestamp)double precision部分フィールドの取得。項9.8.1を参照。 extract(hour from timestamp '2001-02-16 20:38:40')20
extract(field from interval)double precision部分フィールドの取得。項9.8.1を参照。 extract(month from interval '2 years 3 months')3
isfinite(timestamp)booleanタイムスタンプが有限(無限ではない)かどうかのテストisfinite(timestamp '2001-02-16 21:28:30')true
isfinite(interval)boolean時間間隔が有限かどうかのテストisfinite(interval '4 hours')true
localtimetime本日の時刻。項9.8.4を参照。   
localtimestamptimestamp日付と時刻。項9.8.4を参照。   
now()timestamp with time zone現在の日付と時刻(current_timestampと同じ)。 項9.8.4を参照。   
timeofday()text現在の日付と時刻。項9.8.4を参照。   

これらの関数に加え、OVERLAPS SQL演算子がサポートされています。

( start1, end1 ) OVERLAPS ( start2, end2 )
( start1, length1 ) OVERLAPS ( start2, length2 )

この式は、2つの時間間隔が重なる(その終端で定義されます)時に真を返します。 重ならない場合は偽を返します。 終端は日付、時刻、タイムスタンプ、もしくは、日付/時刻/タイムスタンプに続く時間間隔で指定されます。

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false

9.8.1. EXTRACT, date_part

EXTRACT (field FROM source)

extract関数は、日付/時刻の値から年や時などの部分フィールドを抽出します。 sourcetimestamp型またはinterval型を評価する評価式です。 (date型またはtime型の式はtimestamp型にキャストされますので、同様に使用可能です。) fieldはsourceの値からどのフィールドを抽出するかを選択する識別子もしくは文字列です。 extract関数はdouble precision型の値を返します。 以下に有効なフィールド名を示します。

century

年フィールドを100で割ったもの

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 20

centuryフィールドは、年フィールドを単に100で除算した結果を返すもので、1900年代のほとんどの年を20世紀とする従来の定義とは異なります。

day

(月内の)日付フィールド (1 - 31)

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 16
decade

年フィールドを10で割ったもの

SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 200
dow

曜日(0〜6、日曜日が0、timestampの値のみで使用可)

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 5
doy

年内での通算日数(1〜365/366)(timestampの値のみ使用可)

SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 47
epoch

date型とtimestamp型の値において、1970-01-01 00:00:00からの秒数(負の数の場合もあり)。 interval型の値ではその時間間隔における秒の合計

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');
Result: 982384720

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800
hour

時のフィールド(0 - 23)

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 20
microseconds

端数部分も含み、1,000,000を乗じられた秒フィールド。 すべての秒を含むことに注意

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
Result: 28500000
millennium

年フィールドを1000で割ったもの

SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2

millenniumフィールドは年フィールドを単に1000で割算した結果で、1900年代の年を第2ミレニアムとする従来の定義とは異なります。

milliseconds

端数部分も含み、1000を乗じられた秒フィールド。 すべての秒を含むことに注意

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
Result: 28500
minute

分フィールド (0 - 59)

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 38
month

timestamp型の値に対しては年内の月番号(1 - 12)。 interval型の値に対しては月番号で、12の剰余(0 - 11)

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
Result: 3

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
Result: 1
quarter

その日が含まれる年の四半期(1 - 4)(timestampの値に対してのみ)

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 1
second

端数を含んだ秒フィールド(0 - 59) [1]

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 40

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
Result: 28.5
timezone

秒単位のUTCからの時間帯オフセット。 正の値はUTCより東の時間帯に対応し、負の値はUTCより西の時間帯に対応。

timezone_hour

時間帯オフセットの時の成分

timezone_minute

時間帯オフセットの分の成分

week

その日の年間通算での週を計算します。 (ISO 8601の)定義では、その年の1月4日の週を第1週とします。 (ISO-8601では、週は月曜日から始まるとしています。) つまり、年の最初の木曜日がある週がその年の第1週となります。 (timestampの値のみ)

SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 7
year

年フィールド

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2001

extract関数はもともと計算処理の目的でした。 日付/時刻の値を表示する目的での書式については項9.7を参照してください。

date_part関数は伝統的なIngres上で設計されたもので、標準SQLextract関数と等価です。

date_part('field', source)

ここでfieldパラメータが名前ではなく文字列値である必要があることに注意してください。 date_partで有効なフィールド名はextractと同じです。

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Result: 4

9.8.2. date_trunc

date_trunc関数は概念的に数値に対するtrunc関数と類似しています。

date_trunc('field', source)

sourceは、データ型timestampもしくはintervalの評価式です(データ型datetimeはそれぞれ自動的にtimestampもしくはintervalにキャストされます)。 fieldは、入力値の値をどの精度で切捨てるかを選択します。 返り値の値は、選択されたもの以下をゼロに設定(日と月の場合は1に設定)した、すべてのフィールドを持つtimestampもしくはinterval型です。

field の有効値には次のものがあります。

microseconds
milliseconds
second
minute
hour
day
month
year
decade
century
millennium

例:

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00

9.8.3. AT TIME ZONE

AT TIME ZONE構文を使用することにより、タイムスタンプを異なる時間帯に変換することができます。 表9-27にその種類を示します。

表 9-27. AT TIME ZONE の種類

返り値説明
timestamp without time zone AT TIME ZONE zone timestamp with time zone与えられた時間帯のローカル時間をUTCに変換します。
timestamp with time zone AT TIME ZONE zone timestamp without time zoneUTCを与えられた時間帯のローカル時間に変換します。
time with time zone AT TIME ZONE zone time with time zone時間帯に関係なくローカル時間を変換します。

上記の式では、設定する時間帯zoneは、('PST'のような)テキスト文字列、または(INTERVAL '-08:00'のような)時間間隔で指定することができます。

以下に例を示します(ローカルな時間帯をPST8PDTに設定しています)。

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
Result: 2001-02-16 19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40

最初の例では、時間帯に関係のないタイムスタンプが使用され、それがMST時間(UTC-7)として解釈され、UTCタイムスタンプを生成します。 それから、UTCタイムスタンプが、表示上PST(UTC-8)に置き換えられます。 2番目の例では、EST(UTC-5)に指定されたタイムスタンプが使用され、それがMST(UTC-7)でのローカル時間に変換されています。

関数timezone(zone, timestamp)は、SQL準拠の構文timestamp AT TIME ZONE zoneと等価です。

9.8.4. 現在の日付/時刻

以下の関数は、現在の日付および/または時間を取得するための関数です。

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME ( precision )
CURRENT_TIMESTAMP ( precision )
LOCALTIME
LOCALTIMESTAMP
LOCALTIME ( precision )
LOCALTIMESTAMP ( precision )

CURRENT_TIMEおよびCURRENT_TIMESTAMP関数では、時間帯を伴う値を扱います。 一方、LOCALTIMEおよびLOCALTIMESTAMP関数では、時間帯を伴わない値を扱います。

CURRENT_TIMECURRENT_TIMESTAMPLOCALTIME、およびLOCALTIMESTAMP関数では、精度のパラメータをオプションで与えることができ、それに合わせて秒フィールドの端数桁を丸める結果をもたらします。 精度のパラメータがない場合、結果は使用可能な最大精度で出力されます。

注意: PostgreSQL 7.2より前までは、精度パラメータは実装されておりませんでした。 そして結果は常に整数による秒となりました。

以下にいくつか例を示します。

SELECT CURRENT_TIME;
Result: 14:39:53.662522-05

SELECT CURRENT_DATE;
Result: 2001-12-23

SELECT CURRENT_TIMESTAMP;
Result: 2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Result: 2001-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;
Result: 2001-12-23 14:39:53.662522

now()関数はCURRENT_TIMESTAMPと等価のPostgreSQLに於ける因習です。

同時にtimeofday()関数があって、歴史的理由によりtimestampの値ではなくtext型の文字列を返します。

SELECT timeofday();
Result: Sat Feb 17 19:07:32.000126 2001 EST

CURRENT_TIMESTAMPと、それに関連する関数は、すべて現在のトランザクションが開始された時間を返すことを理解することは重要です。 この値は、トランザクションが実行されている間は変化しません。 これは、次の機能を検討した結果です。 単一トランザクションで、"current"時間を一貫性を持った表現を行なうことができるようにすることを目的とし、このため、同一トランザクションで何回変更を行なっても同一のタイムスタンプを生成します。 timeofday()は、壁時計時刻を返し、トランザクションが実行されている間にも増加します。

注意: 他の多くのデータベースシステムでは、これらの値をより頻繁に増加させます。

すべての日付/時刻データ型は同時にnowという現在の日付と時刻を特定する特殊なリテラル値を受け付けます。 したがって、下記の3つの実行結果はすべて同じものとなります。

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';

注意: テーブルを作成する時、DEFAULT句を指定するのに3番目の形式を使おうとは思わないでしょう。 定数が解析された時、システムがnowtimestampに変換するので、デフォルト値としてテーブルが作成された時刻が使われます。 最初の2つの形式は関数呼び出しのためデフォルト値が使用されるまで評価されません。 ですから、これらの関数は列の挿入時間をデフォルトとする、望ましい振舞いをします。

注意

[1]

オペレーティングシステムでうるう秒が実装されている場合は 60 まで

アダルトレンタルサーバー