表9-26は、日付/時刻型の値の処理で使用可能な関数を示しています。詳細は、以下の副節で説明します。表9-25は、(+、*等の)基本的な算術演算子の振舞いを説明しています。書式設定関数については項9.8を参照してください。項8.5を参照して、日付/時刻データ型についての背景となっている情報に精通していなければなりません。
後述のtimeもしくはtimestamp型の入力を受け取る関数および演算子は全て、実際には2つの種類があります。1つはtime with time zone型またはtimestamp with time zone型を取るもので、もう1つはtime without time zone型もしくはtimestamp without time zone型を取るものです。省略のため、これらの種類の違いは個別に示していません。また、+と*演算子は交代演算子を持ちます(例えばdate + integerとinteger + date)。こうした組み合わせは片方のみ示します。
表 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:00' |
+ | date '2001-09-28' + time '03:00' | timestamp '2001-09-28 03:00:00' |
+ | interval '1 day' + interval '1 hour' | interval '1 day 01:00:00' |
+ | timestamp '2001-09-28 01:00' + interval '23 hours' | timestamp '2001-09-29 00:00:00' |
+ | time '01:00' + interval '3 hours' | time '04:00:00' |
- | - interval '23 hours' | interval '-23:00: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:00' |
- | time '05:00' - time '03:00' | interval '02:00' |
- | time '05:00' - interval '2 hours' | time '03:00:00' |
- | timestamp '2001-09-28 23:00' - interval '23 hours' | timestamp '2001-09-28 00:00:00' |
- | interval '1 day' - interval '1 hour' | interval '1 day -01:00:00' |
- | timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' | interval '1 day 15:00:00' |
* | 900 * interval '1 second' | interval '00:15:00' |
* | 21 * interval '1 day' | interval '21 days' |
* | double precision '3.5' * interval '1 hour' | interval '03:30:00' |
/ | interval '1 hour' / double precision '1.5' | interval '00:40:00' |
表 9-26. 日付/時刻関数
関数名 | 戻り値型 | 説明 | 例 | 結果 | |
---|---|---|---|---|---|
age (timestamp, timestamp) | interval | 引数間の減算。年と月を使用した"シンボルによる"結果を生成 | age(timestamp '2001-04-10', timestamp '1957-06-13') | age(timestamp '2001-04-10', timestamp '1957-06-13') | 43 years 9 mons 27 days |
age (timestamp) | interval | current_date から減算 | age(timestamp '1957-06-13') | 43 years 8 mons 3 days | |
clock_timestamp () | timestamp with time zone | 現在の日付時刻です。(文実行時に変わります。) 項9.9.4を参照。 | |||
current_date | date | 現在の日付、項9.9.4を参照。 | |||
current_time | time with time zone | 現在の時刻、項9.9.4を参照 | |||
current_timestamp | timestamp with time zone | 現在の日付と時刻(現在のトランザクションの開始日付時刻)、項9.9.4を参照。 | |||
date_part (text, timestamp) | double precision | 部分フィールドの取得(extract と同じ)。項9.9.1を参照。
| date_part('hour', timestamp '2001-02-16 20:38:40') | 20 | |
date_part (text, interval) | double precision | 部分フィールドの取得(extract と同じ)。項9.9.1を参照。
| date_part('month', interval '2 years 3 months') | 3 | |
date_trunc (text, timestamp) | timestamp | 指定された精度で切り捨て。項9.9.2も参照。 | date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00 | |
extract (field from
timestamp) | double precision | 部分フィールドの取得。項9.9.1を参照。 | extract(hour from timestamp '2001-02-16 20:38:40') | 20 | |
extract (field from
interval) | double precision | 部分フィールドの取得。項9.9.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 | |
justify_days (interval) | interval | 30日周期が1月を表すように時間間隔を調整します。 | justify_days(interval '30 days') | 1 month | |
justify_hours (interval) | interval | 24時間を1日とする時間間隔の調整 | justify_hours(interval '24 hours') | 1 day | |
justify_interval (interval) | interval | justify_days およびjustify_hours を使用した時間間隔の調整。符号による調整を行います。 | justify_interval(interval '1 mon -1 hour') | 29 days 23:00:00 | |
localtime | time | 現在の時刻。項9.9.4を参照。 | |||
localtimestamp | timestamp | 現在の日付時刻。(現在のトランザクションの開始日付時刻)項9.9.4を参照。 | |||
now () | timestamp with time zone | 現在の日付と時刻(現在のトランザクションの開始)。 項9.9.4を参照。 | |||
statement_timestamp () | timestamp with time zone | 現在の日付と時刻(現在のトランザクションの開始)。 項9.9.4を参照。 | |||
timeofday () | text | 現在の日付と時刻。
(clock_timestamp と同様ですが、こちらはtext型文字列として返します。)
項9.9.4を参照。
| |||
transaction_timestamp () | timestamp with time zone | 現在の日付と時刻(現在のトランザクションの開始)。 項9.9.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
timestamp with time zoneの値にintervalの値を加える時(またはtimestamp with time zoneの値からintervalの値を差し引く時)日にちの部分は、日にちの数で示された timestamp with time zoneの日付を先に進めます(もしくは後に戻します)。夏時間への移行に跨っての変更に関しては(セッションの時間帯がDSTを認識するようになっていれば)、interval '1 day'がinterval '24 hours'に等しい必要はありません。例えば、セッションの時間帯が CST7CDTに設定されている時に、 timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' は、timestamp with time zone '2005-04-03 12:00-06'をもたらします。一方同じ初期timestamp with time zoneにinterval '24 hours'を加えると、timestamp with time zone '2005-04-03 13:00-06'という結果になります。その理由はCST7CDT時間帯で2005-04-03 02:00に夏時間への変更があるからです。
EXTRACT
, date_part
EXTRACT(field FROM source)
extract
関数は、日付/時刻の値から年や時などの部分フィールドを抽出します。sourceはtimestamp型、time型、またはinterval型の評価式でなければなりません(date型の式はtimestamp型にキャストされますので、同様に使用可能です)。fieldはsourceの値からどのフィールドを抽出するかを選択する識別子もしくは文字列です。extract
関数はdouble precision型の値を返します。以下に有効なフィールド名を示します。
世紀
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13'); Result: 20 SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 21
常にそのように知られていませんが、最初の世紀は0001-01-01 00:00:00 ADから始まります。 この定義は全てのグレゴリアン暦を使用する国で適用されています。 0という値の世紀はありません。-1の次は1です。 この定義に納得できなければ、苦情をバチカンローマ聖パウロ大聖堂のローマ法王に伝えてください
PostgreSQLリリース8.0以前では、世紀の番号付けの慣習に従っていませんでした。単に年を100で除算したものを返していました。
(月内の)日付フィールド(1〜31)
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 16
年フィールドを10で割ったもの
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 200
曜日(0〜6、日曜日が0、timestampの値のみで使用可)
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 5
extract
関数の曜日指定番号はto_char
関数の番号と異なる点に注意してください。
年内での通算日数(1〜365/366)(timestampの値のみ使用可)
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 47
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
以下に、この経過秒数をタイムスタンプ値に変換する方法を示します。
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
時のフィールド(0〜23)
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 20
端数部分も含み、1,000,000を乗じられた秒フィールド。全ての秒を含むことに注意。
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5'); Result: 28500000
ミレニアム(1千年期間)
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 3
1900年代の年は第2ミレニアムです。第3ミレニアムは2001年1月1日から始まります。
PostgreSQLリリース8.0以前では、ミレニアムの番号付けの慣習に従っていませんでした。単に年フィールドを1000で割った値を返していました。
端数部分も含み、1000を乗じられた秒フィールド。全ての秒を含むことに注意
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5'); Result: 28500
分フィールド(0〜59)
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 38
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
その日が含まれる年の四半期(1〜4)(timestampの値に対してのみ)。
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 1
端数を含んだ秒フィールド(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
秒単位のUTCからの時間帯オフセット。正の値はUTCより東の時間帯に対応し、負の値はUTCより西の時間帯に対応。
時間帯オフセットの時の成分
時間帯オフセットの分の成分
その日の年間通算での週を計算します。(ISO 8601の)定義では、その年の1月4日の週を第1週とします(ISO 8601では、週は月曜日から始まるとしています)。つまり、年の最初の木曜日がある週がその年の第1週となります(timestampの値のみ)。
このことによって、1月の早い日にちは前年の第52週もしくは第53週となることがあります。例えば、2005-01-01は2004年の第53週であり、2006-01-01は2005年の第52週の一部です。
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 7
年フィールド。0 ADが存在しないことは忘れないでください。このためADの年からBCの年を減する時には注意が必要です。
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 2001
extract
関数は元々演算処理の目的でした。日付/時刻の値を表示する目的での書式については項9.8を参照してください。
date_part
関数は伝統的なIngres上で設計されたもので、標準SQLのextract
関数と等価です。
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
date_trunc
date_trunc
関数は概念的に数値に対するtrunc
関数と類似しています。
date_trunc('field', source)
sourceは、データ型timestampもしくはintervalの評価式です(データ型dateとtimeはそれぞれ自動的にtimestampもしくはintervalにキャストされます)。fieldは、入力値の値をどの精度で切り捨てるかを選択します。戻り値の値は、選択されたもの以下をゼロに設定(日と月の場合は1に設定)した、全てのフィールドを持つtimestampもしくはinterval型です。
fieldの有効値には次のものがあります。
microseconds |
milliseconds |
second |
minute |
hour |
day |
week |
month |
quarter |
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
AT TIME ZONE
構文を使用することにより、タイムスタンプを異なる時間帯に変換することができます。表9-27にその種類を示します。
表 9-27. AT TIME ZONE Variants
式 | 戻り値 | 説明 |
---|---|---|
timestamp without time zone AT TIME ZONE zone | timestamp with time zone | 与えられたタイムスタンプwithout time zoneを指定された時間帯にあるとして取り扱います。 |
timestamp with time zone AT TIME ZONE zone | timestamp without time zone | 与えられたタイムスタンプwith time zoneを新規の時間帯に変換します。 |
time with time zone AT TIME ZONE zone | time with time zone | 与えられた時刻with time zoneを新規時間帯に変換します。 |
上記の式では、設定する時間帯zoneは、('PST'のような)テキスト文字列、または(INTERVAL '-08:00'のような)時間間隔で指定することができます。 テキストの場合、項8.5.3に示した方法で時間帯名称を指定することができます。
以下に例を示します(ローカルな時間帯を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-7)に置き換えられます。2番目の例は、EST(UTC-5)で指定されたタイムスタンプを使用し、MST(UTC-7)でのローカル時間に変換しています。
関数timezone
(zone, timestamp)は、SQL準拠の構文timestamp AT TIME ZONE zoneと等価です。
PostgreSQLは、現在の日付時刻に関した値を返す多くの関数を提供します。 これらの標準SQL関数はすべて、現在のトランザクションの開始時刻に基づいた値を返します。
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_TIME
、CURRENT_TIMESTAMP
、LOCALTIME
、およびLOCALTIMESTAMP
関数では、精度のパラメータをオプションで与えることができ、それに合わせて秒フィールドの端数桁を丸める結果をもたらします。精度のパラメータがない場合、結果は使用可能な最大精度で出力されます。
以下にいくつか例を示します。
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
これらの関数は、現在のトランザクションの開始時刻を返します。 この値は、トランザクションが実行されている間は変化しません。 これは、次の機能を検討した結果です。 単一トランザクションで、"current"時間を一貫性を持った表現を行うことができるようにすることを目的とし、このため、同一トランザクションで何回変更を行っても同一のタイムスタンプを生成します。
注意: 他の多くのデータベースシステムでは、これらの値をより頻繁に増加させます。
PostgreSQLはまた、関数を呼び出した時の実際の現在時刻や現在の文の開始時刻を返す関数も提供します。 非標準SQLの関数の全一覧を以下に示します。
now() transaction_timestamp() statement_timestamp() clock_timestamp() timeofday()
now()
はCURRENT_TIMESTAMP
と同じもので、伝統的なPostgreSQL関数です。
transaction_timestamp()
はCURRENT_TIMESTAMP
同様のものですが、明確に何が返されるかを示す名前になっています。
statement_timestamp()
は現在の文の実行開始時刻を返すものです(より具体的にいうと、直前のコマンドメッセージをクライアントから受け取った時刻です)。
statement_timestamp()
およびtransaction_timestamp()
はトランザクションの最初のコマンドでは同じ値を返しますが、その後のコマンドでは異なる可能性があります。
clock_timestamp()
は実際の現在時刻を返しますので、その値は単一のSQLコマンドであっても異なります。
timeofday()
はPostgreSQLの歴史的な関数です。
clock_timestamp()
同様、実際の現在時刻を返しますが、timestamp with time zone型の値ではなく、整形されたtext文字列を返します。
すべての日付/時刻型はまた、現在の日付と時刻(繰り返しますがトランザクションの開始時刻として解釈されます)を表す特殊なリテラル値 nowを受け付けます。 したがって、下記の3つの実行結果は全て同じものとなります。
SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP 'now'; -- DEFAULTと一緒に使用してはいけません
ティップ: テーブルを作成する時、DEFAULT句を指定するのに3番目の形式を使おうとは思わないでしょう。定数が解析された時、システムがnowをtimestampに変換するので、デフォルト値としてテーブルが作成された時刻が使われます。最初の2つの形式は関数呼び出しのためデフォルト値が使用されるまで評価されません。ですから、これらの関数は列の挿入時間をデフォルトとする、望ましい振舞いをします。
以下の関数は、サーバプロセスの実行を遅延させるために使用可能です。
pg_sleep(seconds)
pg_sleep
は、seconds秒経過するまで、現在のセッションのプロセスを休止させます。
secondsはdouble precision型の値です。
そのため、少数単位で休止秒数を指定することができます。
以下に例を示します。
SELECT pg_sleep(1.5);
注意: 休止時間の有効な分解能はプラットフォームに依存します。 0.01秒が一般的な値です。 遅延は少なくとも指定した通り行われます。 サーバの負荷が要因となり、より長くなる可能性があります。
警告 |
|
[1] | オペレーティングシステムでうるう秒が実装されている場合は60まで。 |