1. SQLリファレンス・ガイド
  2. 組込み関数
  3. タイムスタンプの関数
  4. サポートされている単位

サポートされている単位

unitは、入力タイムスタンプの丸め処理時に考慮される精度を指定する識別子です。単位はSTRING値を取ります。タイムスタンプ関数timestamp_ceil、timestamp_floor、timestamp_roundおよびtimestamp_truncの2番目の引数として単位を指定できます。

次の単位を指定できます。指定しない場合、単位はデフォルトでDAYに設定されます。

ノート:

単位の大/小文字は区別されません。

表12-6 単位の説明と例

単位 説明
YEAR、IYEAR
  • YEAR: 年の最初の日。
  • IYEAR: ISO 8601標準で定義されている最初の暦週の月曜日。

例:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'YEAR') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'YEAR') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'YEAR') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'YEAR') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'YEAR') AS ROUND1, 
timestamp_round('2019-10-21T14:16:00Z', 'YEAR') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'YEAR') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'YEAR') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

出力:

{
  "CEIL1" : "2020-01-01T00:00:00Z",
  "CEIL2" : "2020-01-01T00:00:00Z",
  "FLOOR1" : "2019-01-01T00:00:00Z",
  "FLOOR2" : "2019-01-01T00:00:00Z",
  "ROUND1" : "2019-01-01T00:00:00Z",
  "ROUND2" : "2020-01-01T00:00:00Z",
  "TRUNC1" : "2019-01-01T00:00:00Z",
  "TRUNC2" : "2019-01-01T00:00:00Z"
}
QUARTER ISO 8601標準で定義されている四半期の初日。

例:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'QUARTER') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'QUARTER') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'QUARTER') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'QUARTER') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'QUARTER') AS ROUND1, 
timestamp_round('2019-11-21T14:16:00Z', 'QUARTER') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'QUARTER') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'QUARTER') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

出力:

{
  "CEIL1" : "2019-07-01T00:00:00Z",
  "CEIL2" : "2020-01-01T00:00:00Z",
  "FLOOR1" : "2019-04-01T00:00:00Z",
  "FLOOR2" : "2019-10-01T00:00:00Z",
  "ROUND1" : "2019-04-01T00:00:00Z",
  "ROUND2" : "2020-01-01T00:00:00Z",
  "TRUNC1" : "2019-04-01T00:00:00Z",
  "TRUNC2" : "2019-10-01T00:00:00Z"
}
MONTH 月の最初の日。

例:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'MONTH') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'MONTH') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'MONTH') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'MONTH') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'MONTH') AS ROUND1, 
timestamp_round('2019-11-21T14:16:00Z', 'MONTH') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'MONTH') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'MONTH') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

出力:

{
  "CEIL1" : "2019-06-01T00:00:00Z",
  "CEIL2" : "2019-11-01T00:00:00Z",
  "FLOOR1" : "2019-05-01T00:00:00Z",
  "FLOOR2" : "2019-10-01T00:00:00Z",
  "ROUND1" : "2019-05-01T00:00:00Z",
  "ROUND2" : "2019-12-01T00:00:00Z",
  "TRUNC1" : "2019-05-01T00:00:00Z",
  "TRUNC2" : "2019-10-01T00:00:00Z"
}
WEEK、IWEEK
  • WEEK: 1月の1日と同じ曜日。

    たとえば、特定のタイムスタンプの年の最初の日が火曜日である場合、WEEK単位を使用すると、タイムスタンプは、使用するタイムスタンプ関数に応じて最も近い火曜日まで切り上げまたは切り下げられます。

  • IWEEK: ISO 8601標準で定義されている、暦週の最初の日(月曜日)。

例:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'WEEK') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'WEEK') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'WEEK') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'WEEK') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'WEEK') AS ROUND1, 
timestamp_round('2019-11-15T05:00:00Z', 'WEEK') AS ROUND2, 
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'WEEK') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'WEEK') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

出力:

{
  "CEIL1" : "2019-05-21T00:00:00Z",
  "CEIL2" : "2019-10-22T00:00:00Z",
  "FLOOR1" : "2019-05-14T00:00:00Z",
  "FLOOR2" : "2019-10-15T00:00:00Z",
  "ROUND1" : "2019-05-14T00:00:00Z",
  "ROUND2" : "2019-11-12T00:00:00Z",
  "TRUNC1" : "2019-05-14T00:00:00Z",
  "TRUNC2" : "2019-10-15T00:00:00Z"
}
DAY 曜日。

例:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'DAY') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'DAY') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'DAY') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'DAY') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'DAY') AS ROUND1, 
timestamp_round('2019-11-15T15:00:00Z', 'DAY') AS ROUND2, 
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'DAY') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'DAY') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

出力:

{
  "CEIL1" : "2019-05-15T00:00:00Z",
  "CEIL2" : "2019-10-22T00:00:00Z",
  "FLOOR1" : "2019-05-14T00:00:00Z",
  "FLOOR2" : "2019-10-21T00:00:00Z",
  "ROUND1" : "2019-05-14T00:00:00Z",
  "ROUND2" : "2019-11-16T00:00:00Z",
  "TRUNC1" : "2019-05-14T00:00:00Z",
  "TRUNC2" : "2019-10-21T00:00:00Z"
}
HOUR 時刻。

例:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'HOUR') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'HOUR') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'HOUR') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'HOUR') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'HOUR') AS ROUND1, 
timestamp_round('2019-11-15T15:30:00Z', 'HOUR') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'HOUR') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'HOUR') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

出力:

{
  "CEIL1" : "2019-05-14T12:00:00Z",
  "CEIL2" : "2019-10-21T15:00:00Z",
  "FLOOR1" : "2019-05-14T11:00:00Z",
  "FLOOR2" : "2019-10-21T14:00:00Z",
  "ROUND1" : "2019-05-14T12:00:00Z",
  "ROUND2" : "2019-11-15T16:00:00Z",
  "TRUNC1" : "2019-05-14T11:00:00Z",
  "TRUNC2" : "2019-10-21T14:00:00Z"
}
MINUTE 分。

例:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'MINUTE') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:24Z', 'MINUTE') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'MINUTE') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:24Z', 'MINUTE') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'MINUTE') AS ROUND1, 
timestamp_round('2019-11-15T15:30:24Z', 'MINUTE') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'MINUTE') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:24Z', 'MINUTE') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

出力:

{
  "CEIL1" : "2019-05-14T11:48:00Z",
  "CEIL2" : "2019-10-21T14:17:00Z",
  "FLOOR1" : "2019-05-14T11:47:00Z",
  "FLOOR2" : "2019-10-21T14:16:00Z",
  "ROUND1" : "2019-05-14T11:48:00Z",
  "ROUND2" : "2019-11-15T15:30:00Z",
  "TRUNC1" : "2019-05-14T11:47:00Z",
  "TRUNC2" : "2019-10-21T14:16:00Z"
}
SECOND 秒。

例:

SELECT timestamp_ceil('2019-05-14T11:47:56.4999Z', 'SECOND') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:24.99999Z', 'SECOND') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.6999Z', 'SECOND') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:24.500Z', 'SECOND') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.4999Z', 'SECOND') AS ROUND1, 
timestamp_round('2019-11-15T15:30:24.6999Z', 'SECOND') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.355Z', 'SECOND') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:24.7000Z', 'SECOND') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

出力:

{
  "CEIL1" : "2019-05-14T11:47:57Z",
  "CEIL2" : "2019-10-21T14:16:25Z",
  "FLOOR1" : "2019-05-14T11:47:56Z",
  "FLOOR2" : "2019-10-21T14:16:24Z",
  "ROUND1" : "2019-05-14T11:47:56Z",
  "ROUND2" : "2019-11-15T15:30:25Z",
  "TRUNC1" : "2019-05-14T11:47:56Z",
  "TRUNC2" : "2019-10-21T14:16:24Z"
}