在查询中使用时间戳函数
可以对“时间戳”和“持续时间”值执行各种算术运算。
如果要随示例一起执行操作,请参见以查看示例数据并使用脚本加载示例数据进行测试。这些脚本将创建示例中使用的表,并将数据加载到表中。
如果要随示例一起操作,请参阅用于运行查询的示例数据以查看示例数据,并了解如何使用 OCI 控制台创建示例表并使用 JSON 文件加载数据。
提取表达式
extract_expression ::= EXTRACT "(" id FROM expression ")"
FROM 关键字后面的表达式最多必须返回一个时间戳或 NULL。如果此表达式的结果为 NULL 或空,则 EXTRACT 的结果也分别为 NULL 或空。否则,将返回 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 minutes" 并显示。
{"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}
注意:
列 estimatedArrival
是一个字符串。如果列具有 ISO-8601 格式的 STRING 值,则 SQL 运行时会自动将其转换为 TIMESTAMP 数据类型。
ISO8601 介绍了表示日期、时间和工期的一种国际公认方式。
Syntax: Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]
- YYYY 将年份指定为 4 位小数
- MM 将月份指定为两位小数,00 到 12
- DD 将日期指定为两位小数,00 到 31
- hh 将小时指定为两位小数,00 至 23
- mm 指定分钟,以两位小数表示,00 到 59
- ss[.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
数组中的每条记录,您可以确定包到达时间是否介于现在和六小时之后的时间之间。函数 current_time
现在为您提供时间。EXISTS 条件用作过滤器,用于确定袋子在未来六小时内是否有到达日期。count
函数确定此时段内的包总数。
{"COUNT_NEXT6HOURS":0}
timestamp_diff() 和 get_duration() 函数
timestamp_diff()
返回两个时间戳值之间的毫秒数。结果类型为 LONG
。
LONG timestamp_diff(TIMESTAMP timestamp1, TIMESTAMP
timestamp2)
- timestamp1 :TIMESTAMP 值或可以转换为 TIMESTAMP 的值
- timestamp2 :TIMESTAMP 值或可以转换为 TIMESTAMP 的值
- 返回值: 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
都有一个操作数组。操作数组中有三个不同的操作。数组中第一个元素的操作代码是 Checkin/Offload。对于第一回合,操作代码是“检入”,对于其他回合,操作代码是“在跃点卸载”。数组的第二个元素的操作代码为 BagTag Scan。在上面的查询中,确定包标签扫描与检入时间之间的操作时间差异。仅当操作代码为“检入”或 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);