Uso de funciones de registro de hora en consultas
Puede realizar varias operaciones aritméticas en los valores Timestamp y Duration.
Si desea seguir los ejemplos, consulte para ver un ejemplo de datos y utilizar los scripts para cargar datos de ejemplo para pruebas. Los scripts crean las tablas utilizadas en los ejemplos y cargan datos en las tablas.
Si desea seguir los ejemplos, consulte Datos de ejemplo para ejecutar consultas para ver datos de ejemplo y aprender a utilizar la consola de OCI para crear las tablas de ejemplo y cargar datos mediante archivos JSON.
Temas relacionados
Expresiones Extract
extract_expression ::= EXTRACT "(" id FROM expression ")"
La expresión después de la palabra clave FROM debe devolver como máximo un registro de hora o NULL. Si el resultado de esta expresión es NULL o está vacío, el resultado de EXTRACT también es NULL o vacío, respectivamente. De lo contrario, se devuelve el componente especificado por el ID. Este ID debe ser una de las siguientes palabras clave: 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
Explicación: primero utilice CAST para convertir bagArrivalDate
en un TIMESTAMP y, a continuación, recupere el componente YEAR del registro de hora.
{"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)
Explicación: desea conocer los detalles de los vuelos que viajaron por MIA entre las 10:00 a. m. y las 10:00 p. m. en febrero de 2019. Puede utilizar una serie de condiciones de filtro aquí. Primero, el vuelo debería haberse originado o atravesado a través de MIA. El año de llegada debe ser 2019 y el mes de llegada debe ser 2 (febrero). A continuación, filtre si la hora de llegada es entre las 10:00 am y las 10:00 pm (20 horas).
{"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"
Explicación: primero utilice CAST para convertir bagArrivalDate
en TIMESTAMP y, a continuación, recupere el componente YEAR y el componente MONTH del registro de hora.
{"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
se simplifica y el valor de la fecha de llegada de la bolsa se recupera de la matriz. A continuación, utilice CAST para convertir bagArrivalDate
en TIMESTAMP y, a continuación, recupere el componente YEAR y el componente MONTH del registro de hora. A continuación, utilice la función count
para obtener el equipaje total correspondiente a cada mes.
Note:
Una suposición en los datos es que todo el equipaje ha llegado en el mismo año. Por lo tanto, los datos se agrupan solo en función del mes.{"MONTH":2,"COUNT":11}
{"MONTH":3,"COUNT":10}
Función timestamp_add()
Agrega una duración a un valor de registro de hora y devuelve el nuevo registro de hora. La duración puede ser positiva o negativa. El tipo de resultado es TIMESTAMP(9)
.
TIMESTAMP(9) timestamp_add(TIMESTAMP timestamp, STRING duration)
- timestamp: un valor de TIMESTAMP o un valor que se puede convertir en TIMESTAMP.
- duración: cadena con formato [-](<n> <UNIT>)+, donde 'n' es un número y <UNIT> puede ser YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, NANOSECOND o la forma plural de estas palabras clave (por ejemplo, AÑOS).
Note:
La palabra clave UNIT no distingue entre mayúsculas y minúsculas. - returnvalue: TIMESTAMP(9)
SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag
WHERE ticketNo=1762399766476
Explicación: en la aplicación de línea aérea, un cliente puede tener cualquier número de tramos de vuelo según el origen y el destino. En la consulta anterior, está recuperando la llegada estimada en el "primer tramo" del viaje. Por lo tanto, se recupera el primer registro de la matriz flightsLeg
y se recupera la hora estimatedArrival
de la matriz y se agrega un buffer de "5 minutos" a eso y se muestra.
{"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}
Note:
La columna estimatedArrival
es STRING. Si la columna tiene valores STRING en formato ISO-8601, el tiempo de ejecución SQL la convertirá automáticamente en tipo de dato TIMESTAMP.
ISO8601 describe una forma aceptada internacionalmente de representar fechas, horas y duraciones.
Syntax: Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]
- AAAA especifica el año, como cuatro dígitos decimales
- MM especifica el mes, como dos dígitos decimales, de 00 a 12
- DD especifica el día, como dos dígitos decimales, de 00 a 31
- hh especifica la hora, como dos dígitos decimales, de 00 a 23
- mm especifica los minutos, como dos dígitos decimales, de 00 a 59
- ss[.s[s[s[s[s]]]]] especifica los segundos, como dos dígitos decimales, de 00 a 59, seguido opcionalmente por una coma decimal y de 1 a 6 dígitos decimales (que representan la parte fraccional de un segundo).
- Z especifica la hora UTC (zona horaria 0). (También se puede especificar por +00:00, pero no por -00:00).
- (+|-)hh:mm especifica la zona horaria como diferencia de UTC. (Se requiere uno de + o -).
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
Explicación: desea mostrar la hora estimatedArrival
en cada tramo. El número de patas puede ser diferente para cada cliente. Por lo tanto, la referencia de variable se utiliza en la consulta anterior y la matriz baggageInfo
y la matriz flightLegs
no se anidan para ejecutar la consulta.
{"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")]
Explicación: obtiene un recuento del número de bolsas procesadas por la aplicación de aerolínea en la última semana. Un cliente puede tener más de una bolsa (es decir, la matriz bagInfo
puede tener más de un registro). bagArrivalDate
debe tener un valor entre hoy y los últimos 7 días. Para cada registro de la matriz bagInfo
, determina si la hora de llegada de la bolsa está entre la hora actual y la de hace una semana. La función current_time
le da el tiempo ahora. Una condición EXISTS se utiliza como filtro para determinar si la bolsa tiene una fecha de llegada en la última semana. La función count
determina el número total de bolsas en este período de tiempo.
{"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")]
Explicación: obtiene un recuento del número de bolsas que la aplicación aérea procesará en las próximas 6 horas. Un cliente puede tener más de una bolsa (es decir, la matriz bagInfo
puede tener más de un registro). bagArrivalDate
debe estar entre la hora actual y las siguientes 6 horas. Para cada registro de la matriz bagInfo
, determina si la hora de llegada de la bolsa está entre la hora actual y seis horas después. La función current_time
le da el tiempo ahora. Una condición EXISTS se utiliza como filtro para determinar si la bolsa tiene una fecha de llegada en las próximas seis horas. La función count
determina el número total de bolsas en este período de tiempo.
{"COUNT_NEXT6HOURS":0}
Funciones timestamp_diff() y get_duration()
timestamp_diff()
Devuelve el número de milisegundos entre dos valores de registro de hora. El tipo de resultado es LONG
.
LONG timestamp_diff(TIMESTAMP timestamp1, TIMESTAMP
timestamp2)
- timestamp1: un valor de TIMESTAMP o un valor que se puede convertir en TIMESTAMP
- timestamp2: un valor de TIMESTAMP o un valor que se puede convertir en TIMESTAMP
- returnvalue: LONG
get_duration()
Convierte el número determinado de milisegundos en una cadena de duración. El tipo de resultado es STRING
.
STRING get_duration(LONG duration_millis)
- duration_millis: la duración en milisegundos
- returnvalue: STRING. El formato de cadena de duración devuelto es [-](<n> <UNIT>)+, donde <UNIT> puede ser DAY, HOUR, MINUTE, SECOND y MILLISECOND, por ejemplo, "1 día 2 horas" o "-10 minutos 0 segundo 500 milisegundos".
Ejemplos:
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
Explicación: en una aplicación de línea aérea, cada cliente puede tener un número diferente de saltos/patas entre el origen y el destino. En esta consulta, determina el tiempo que tarda cada tramo de vuelo. Esto se determina por la diferencia entre bagArrivalDate
y flightDate
para cada tramo de vuelo. Para determinar la duración en días, horas o minutos, transfiera el resultado de la función timestamp_diff
a la función 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)
Explicación: en los datos de equipaje, cada flightLeg
tiene una matriz de acciones. Hay tres acciones diferentes en la matriz de acciones. El código de acción para el primer elemento de la matriz es Protección/Descarga. Para el primer tramo, el código de acción es Protección y para los otros tramos, el código de acción es Descarga en el salto. El código de acción para el segundo elemento de la matriz es BagTag Scan. En la consulta anterior, puede determinar la diferencia en el tiempo de acción entre el escaneo de etiquetas de bolsa y la hora de entrada. Utilice la función contains
para filtrar la hora de acción solo si el código de acción es Protección o BagScan. Dado que solo el primer tramo de vuelo tiene detalles de check-in y exploración de bolsa, también filtra los datos mediante la función starts_with
para recuperar solo el código fuente fltRouteSrc
. Para determinar la duración en días, horas o minutos, transfiera el resultado de la función timestamp_diff
a la función 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
Explicación: en una aplicación de línea aérea, cada cliente puede tener un número diferente de saltos/patas entre el origen y el destino. En el ejemplo anterior, determina el tiempo que tarda la bolsa en llegar al primer punto de tránsito. En los datos de equipaje, flightLeg
es una matriz. El primer registro de la matriz hace referencia a los detalles del primer punto de tránsito. El valor flightDate
del primer registro es el momento en que la bolsa sale de la fuente y el valor estimatedArrival
del primer registro de tramo de vuelo indica el momento en que alcanza el primer punto de tránsito. La diferencia entre los dos da el tiempo necesario para que la bolsa llegue al primer punto de tránsito. Para determinar la duración en días, horas o minutos, transfiera el resultado de la función timestamp_diff
a la función 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"}
Ejemplos que utilizan la API QueryRequest
Puede utilizar la API QueryRequest
y aplicar funciones SQL para recuperar datos de una tabla NoSQL.
Para ejecutar la consulta, utilice la API NoSQLHandle.query()
.
//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);
Para ejecutar la consulta, utilice el método 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)
Para ejecutar una consulta, utilice la función 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)
Para ejecutar una consulta, utilice el método 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);
Para ejecutar una consulta, puede llamar al método QueryAsync
o llamar al método GetQueryAsyncEnumerable
e iterar sobre el elemento enumerable asíncrono resultante.
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);