在查詢中使用 Timestamp 函數
您可以對「時間戳記」和「期間」值執行各種算術運算。
如果您想要跟隨範例,請參閱 以檢視範例資料,並使用指令碼載入測試的範例資料。命令檔會建立範例中使用的表格,並將資料載入表格中。
如果您想要跟隨範例,請參閱執行查詢的範例資料以檢視範例資料,並瞭解如何使用 OCI 主控台建立範例表格,並使用 JSON 檔案載入資料。
擷取表示式
extract_expression ::= EXTRACT "(" id FROM expression ")"
FROM 關鍵字之後的運算式最多只能傳回一個時戳或 NULL。如果此表示式的結果為空值或空白,則 EXTRACT 的結果會分別為空值或空白。否則,會傳回由 ID 指定的元件。此 ID 必須是下列關鍵字之一:YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、MILLISECOND、MICROSECOND、NANOSECOND、WEEK、ISOWEEK。
SELECT fullName,
EXTRACT (YEAR FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0)))
AS YEAR FROM BaggageInfo bag
WHERE ticketNo=1762383911861
說明:您首先使用 CAST 將 bagArrivalDate
轉換成 TIMESTAMP,然後從「時戳」擷取 YEAR 元件。
{"fullName":"Joanne Diaz","YEAR":2019}
SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc,
$t1 AS HOUR FROM BaggageInfo bag,
EXTRACT(HOUR FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t1,
EXTRACT(YEAR FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t2,
EXTRACT(MONTH FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t3
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "MIA" AND
$t2=2019 AND $t3=02 AND ($t1>10 AND $t1<20)
說明:您想知道在 2019 年 2 月上午 10:00 到下午 10:00 之間經由 MIA 往返的航班詳細資訊。您可以在這裡使用一些篩選條件。首先,航班的起源或經由 MIA 旅行。抵達年份應為 2019,抵達月份應為 2 (2 月)。然後篩選抵達時間是否介於上午 10:00 到下午 10:00 (20 小時) 之間。
{"tagNum":"17657806255240","fltRouteSrc":["MIA","LAX"],"HOUR":16}
{"tagNum":"17657806292518","fltRouteSrc":["MIA","LAX"],"HOUR":16}
SELECT fullName,
EXTRACT(YEAR FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0))) AS YEAR,
EXTRACT(MONTH FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0))) AS MONTH
FROM BaggageInfo bag WHERE bag.confNo="PQ1M8N"
說明:您首先使用 CAST 將 bagArrivalDate
轉換成 TIMESTAMP,然後從「時戳」擷取 YEAR 元件和 MONTH 元件。
{"fullName":"Kendal Biddle","YEAR":2019,"MONTH":3}
SELECT EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0))) AS MONTH,
count(EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0)))) AS COUNT
FROM BaggageInfo $bag, $bag.bagInfo[].bagArrivalDate $bag_arr_date
GROUP BY EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0)))
bagInfo
陣列為均一化,並從陣列擷取提取提袋到達日期的值。接著,您可以使用 CAST 將 bagArrivalDate
轉換成 TIMESTAMP,然後從「時戳」擷取 YEAR 元件和 MONTH 元件。然後,您可以使用 count
函數來取得每個月對應的行李總數。
附註:
資料中的一個假設是所有行李都已抵達同年。因此,您只會根據月份將資料分組。{"MONTH":2,"COUNT":11}
{"MONTH":3,"COUNT":10}
timestamp_add() 函數
新增持續時間至時戳值,然後傳回新的時戳。持續時間可以是正值或負值。結果類型為 TIMESTAMP(9)
。
TIMESTAMP(9) timestamp_add(TIMESTAMP timestamp, STRING duration)
- timestamp :可轉換為 TIMESTAMP 的 TIMESTAMP 值或值。
- duration :格式為 [-] (<n> <UNIT>) + 的字串,其中 'n' 是數字,而 <UNIT> 可以是 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、MILLISECOND、NANOSECOND 或這些關鍵字的複數形式 (例如 YEARS)。
附註:
UNIT 關鍵字不區分大小寫。 - returnvalue: TIMESTAMP (9)
SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag
WHERE ticketNo=1762399766476
說明:在航空公司應用模組中,根據來源與目的地,客戶可以有任意數目的航段。在上面的查詢中,您將在旅行的「第一段」中提取估計抵達。因此會擷取 flightsLeg
陣列的第一筆記錄,並從陣列擷取 estimatedArrival
時間,並將 「5 分鐘」緩衝區新增至該處並顯示。
{"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}
附註:
資料欄 estimatedArrival
為 STRING。如果資料欄的 STRING 值為 ISO-8601 格式,則會由 SQL 程式實際執行自動轉換為 TIMESTAMP 資料類型。
ISO8601 描述代表日期、時間和持續時間的國際接受方式。
Syntax: Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]
- YYYY 指定年份,以四位小數表示
- MM 將月份指定為兩位小數,00 到 12
- DD 指定日為小數,00 到 31
- hh 會將小時指定為兩位小數,從 00 到 23
- mm 以 00 到 59 的小數位數指定分鐘數
- ss[.s[s[s[s[s]]]] 指定秒數,作為 00 到 59 的兩個小數位數 (選擇性),後面接著一個小數點和 1 到 6 個小數位數 (代表第二個的小數部分)。
- Z 指定 UTC 時間 (時區 0)。(它也可以由 +00:00 指定,但不能由 -00:00 指定。)
- (+|-) hh:mm 指定時區與 UTC 的差異。(需要 + 或 - 其中之一。)
SELECT $s.ticketno, $value as estimate,
timestamp_add($value, '5 minute') AS add5min
FROM baggageinfo $s,
$s.bagInfo.flightLegs.estimatedArrival as $value
WHERE ticketNo=1762399766476
說明:您要在每個航段上顯示 estimatedArrival
時間。每個客戶的航段數可以不同。因此,變數參照是用於上述查詢中,baggageInfo
陣列和 flightLegs
陣列則不是巢狀來執行查詢。
{"ticketno":1762399766476,"estimate":"2019-02-03T06:00:00Z",
"add5min":"2019-02-03T06:05:00.000000000Z"}
{"ticketno":1762399766476,"estimate":"2019-02-03T08:22:00Z",
"add5min":"2019-02-03T08:27:00.000000000Z"}
SELECT count(*) AS COUNT_LASTWEEK FROM baggageInfo bag
WHERE EXISTS bag.bagInfo[$element.bagArrivalDate < current_time()
AND $element.bagArrivalDate > timestamp_add(current_time(), "-7 days")]
說明:您會在上週得到航空公司應用程式處理的行李數目。客戶可以有一個以上的包裹 (即 bagInfo
陣列可以有一個以上的記錄)。bagArrivalDate
的值應該介於今天與過去 7 天之間。對於 bagInfo
陣列中的每筆記錄,您可以決定行李抵達時間是否介於目前與一週前的時間之間。current_time
函數提供您現在的時間。EXISTS 條件是用來作為篩選,用來判斷包袋的抵達日期是否在上週。count
函數會決定此期間內的行李總數。
{"COUNT_LASTWEEK":0}
SELECT count(*) AS COUNT_NEXT6HOURS FROM baggageInfo bag
WHERE EXISTS bag.bagInfo[$element.bagArrivalDate > current_time()
AND $element.bagArrivalDate < timestamp_add(current_time(), "6 hours")]
說明:您會在接下來 6 小時內得到航空公司應用程式將處理的行李數目。客戶可以有一個以上的包裹 (即 bagInfo
陣列可以有一個以上的記錄)。bagArrivalDate
應介於目前與未來 6 小時之間。對於 bagInfo
陣列中的每筆記錄,您可以決定行李抵達時間是否介於現在到 6 小時之後的時間之間。current_time
函數提供您現在的時間。EXISTS 條件是用來作為篩選,以判斷包袋在接下來 6 小時內是否有抵達日期。count
函數會決定此期間內的行李總數。
{"COUNT_NEXT6HOURS":0}
timestamp_diff() 和 get_duration() 函數
timestamp_diff()
傳回兩個時間戳記值之間的毫秒數。結果類型是 LONG
。
LONG timestamp_diff(TIMESTAMP timestamp1, TIMESTAMP
timestamp2)
- timestamp1 :TIMESTAMP 值或可轉換為 TIMESTAMP 的值
- timestamp2 :TIMESTAMP 值或可轉換為 TIMESTAMP 的值
- returnvalue: LONG
get_duration()
將指定的毫秒數轉換為持續時間字串。結果類型是 STRING
。
STRING get_duration(LONG duration_millis)
- duration_millis:持續時間 (毫秒)
- returnvalue: STRING。傳回的持續時間字串格式為 [-] (<n> <UNIT>) +,其中 <UNIT> 可以是 DAY、HOUR、MINUTE、SECOND 以及 MILLISECOND,例如 "1 day 2 hours" 或 "-10 minutes 0 second 500 milliseconds"。
範例:
SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
FROM baggageinfo $s,
$s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762355527825
說明:在航空公司應用模組中,每個客戶在其來源與目的地之間可以有不同數目的躍點 / 航段。在此查詢中,您可以決定每個航段之間的時間。這取決於每個航段的 bagArrivalDate
與 flightDate
之間的差異。若要決定持續時間 (天、小時或分鐘),請將 timestamp_diff
函數的結果傳送至 get_duration
函數。
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:00:00Z",
"diff":"3 hours 17 minutes"}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:23:00Z",
"diff":"2 hours 54 minutes"}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T08:23:00Z",
"diff":"1 hour 54 minutes"}
timestamp_diff
函數。SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate) AS diff
FROM baggageinfo $s,
$s.bagInfo[] AS $bagInfo,
$bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762355527825
SELECT $flightLeg.flightNo,
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime AS checkinTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime AS bagScanTime,
get_duration(timestamp_diff(
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime
)) AS diff
FROM baggageinfo $s,
$s.bagInfo[].flightLegs[] AS $flightLeg
WHERE ticketNo=176234463813 AND
starts_with($s.bagInfo[].routing, $flightLeg.fltRouteSrc)
說明:在行李資料中,每個 flightLeg
都有一個動作陣列。動作陣列中有三個不同的動作。陣列中第一個元素的動作代碼為「存回 / 卸載」。針對第一個航段,作業代碼為「存回」,而針對其他航段,作業代碼為跳躍點的「卸載」。陣列之第二個元素的動作代碼為 BagTag 掃描。在上面的查詢中,您可以決定行李箱標籤掃描與入住時間之間的動作時間差異。只有在動作代碼為「存入」或 BagScan 時,才能使用 contains
函數來篩選動作時間。由於只有第一個航班航段具有辦理登機手續和行李掃描的詳細資料,因此您使用 starts_with
函數額外篩選資料,僅擷取原始碼 fltRouteSrc
。若要決定持續時間 (天、小時或分鐘),請將 timestamp_diff
函數的結果傳送至 get_duration
函數。
timestamp_diff
函數。SELECT $flightLeg.flightNo,
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime AS checkinTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime AS bagScanTime,
timestamp_diff(
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime
) AS diff
FROM baggageinfo $s,
$s.bagInfo[].flightLegs[] AS $flightLeg
WHERE ticketNo=176234463813 AND
starts_with($s.bagInfo[].routing, $flightLeg.fltRouteSrc)
{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":"- 1 hour 24 minutes"}
SELECT $bagInfo.flightLegs[1].actions[2].actionTime,
$bagInfo.flightLegs[0].actions[0].actionTime,
get_duration(timestamp_diff($bagInfo.flightLegs[1].actions[2].actionTime,
$bagInfo.flightLegs[0].actions[0].actionTime)) AS diff
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo
WHERE ticketNo=1762320369957
說明:在航空公司應用模組中,每個客戶在其來源與目的地之間可以有不同數目的躍點 / 航段。在上述範例中,您可以決定將袋子送達第一個運輸點所花費的時間。在行李資料中,flightLeg
是陣列。陣列中的第一筆記錄是指第一筆在途點詳細資料。第一個記錄中的 flightDate
是袋子離開來源的時間,而第一個航段記錄中的 estimatedArrival
表示到達第一個運輸點的時間。這兩者的差別在於讓行李到達第一個運輸點所花費的時間。若要決定持續時間 (天、小時或分鐘),請將 timestamp_diff
函數的結果傳送至 get_duration
函數。
timestamp_diff
函數。
SELECT $bagInfo.flightLegs[0].flightDate,
$bagInfo.flightLegs[0].estimatedArrival,
timestamp_diff($bagInfo.flightLegs[0].estimatedArrival,
$bagInfo.flightLegs[0].flightDate) AS diff
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo
WHERE ticketNo=1762320369957
{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:00:00Z","diff":"13 hours"}
{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:40:00Z","diff":"13 hours 40 minutes"}
使用 QueryRequest API 的範例
您可以使用 QueryRequest
API 並套用 SQL 函數,從 NoSQL 表格擷取資料。
若要執行查詢,請使用 NoSQLHandle.query()
API。
//Fetch rows from the table
private static void fetchRows(NoSQLHandle handle,String sqlstmt) throws Exception {
try (
QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
QueryIterableResult results = handle.queryIterable(queryRequest)){
for (MapValue res : results) {
System.out.println("\t" + res);
}
}
}
String ts_func1="SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, \"5 minutes\")"+
" AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625";
System.out.println("Using timestamp_add function ");
fetchRows(handle,ts_func1);
String ts_func2="SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate, "+
"get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff "+
"FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg "+
"WHERE ticketNo=1762344493810";
System.out.println("Using get_duration and timestamp_diff function ");
fetchRows(handle,ts_func2);
若要執行查詢,請使用 borneo.NoSQLHandle.query()
方法。
# Fetch data from the table
def fetch_data(handle,sqlstmt):
request = QueryRequest().set_statement(sqlstmt)
print('Query results for: ' + sqlstmt)
result = handle.query(request)
for r in result.get_results():
print('\t' + str(r))
ts_func1 = '''SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625'''
print('Using timestamp_add function:')
fetch_data(handle,ts_func1)
ts_func2 = '''SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
FROM baggageinfo $s,
$s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762344493810'''
print('Using get_duration and timestamp_diff function:')
fetch_data(handle,ts_func2)
若要執行查詢,請使用 Client.Query
函數。
//fetch data from the table
func fetchData(client *nosqldb.Client, err error, tableName string, querystmt string)(){
prepReq := &nosqldb.PrepareRequest{
Statement: querystmt,
}
prepRes, err := client.Prepare(prepReq)
if err != nil {
fmt.Printf("Prepare failed: %v\n", err)
return
}
queryReq := &nosqldb.QueryRequest{
PreparedStatement: &prepRes.PreparedStatement, }
var results []*types.MapValue
for {
queryRes, err := client.Query(queryReq)
if err != nil {
fmt.Printf("Query failed: %v\n", err)
return
}
res, err := queryRes.GetResults()
if err != nil {
fmt.Printf("GetResults() failed: %v\n", err)
return
}
results = append(results, res...)
if queryReq.IsDone() {
break
}
}
for i, r := range results {
fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
}
}
ts_func1 := `SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625`
fmt.Printf("Using timestamp_add function::\n")
fetchData(client, err,tableName,ts_func1)
ts_func2 := `SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
FROM baggageinfo $s,
$s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762344493810`
fmt.Printf("Using get_duration and timestamp_diff function:\n")
fetchData(client, err,tableName,ts_func2)
若要執行查詢,請使用 query
方法。
//fetches data from the table
async function fetchData(handle,querystmt) {
const opt = {};
try {
do {
const result = await handle.query(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}
interface StreamInt {
acct_Id: Integer;
profile_name: String;
account_expiry: TIMESTAMP;
acct_data: JSON;
}
/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: string) {
const opt = {};
try {
do {
const result = await handle.query<StreamInt>(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}
const ts_func1 = `SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625`
console.log("Using timestamp_add function:");
await fetchData(handle,ts_func1);
const ts_func2 = `SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
FROM baggageinfo $s,
$s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762344493810`
console.log("Using get_duration and timestamp_diff function:");
await fetchData(handle,ts_func2);
若要執行查詢,您可以呼叫 QueryAsync
方法或呼叫 GetQueryAsyncEnumerable
方法,然後重複產生的非同步可列舉。
private static async Task fetchData(NoSQLClient client,String querystmt){
var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
await DoQuery(queryEnumerable);
}
private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
Console.WriteLine(" Query results:");
await foreach (var result in queryEnumerable) {
foreach (var row in result.Rows)
{
Console.WriteLine();
Console.WriteLine(row.ToJsonString());
}
}
}
private const string ts_func1 =@"SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, ""5 minutes"")
AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625";
Console.WriteLine("\nUsing timestamp_add function!");
await fetchData(client,ts_func1);
private const string ts_func2 =@"SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
FROM baggageinfo $s,
$s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762344493810";
Console.WriteLine("\nUsing get_duration and timestamp_diff function!");
await fetchData(client,ts_func2);