Usando funções de Timestamp em consultas
Você pode executar várias operações aritméticas nos valores Timestamp e Duration.
Se quiser acompanhar os exemplos, consulte para exibir dados de amostra e usar os scripts para carregar dados de amostra para teste. Os scripts criam as tabelas usadas nos exemplos e carregam dados nas tabelas.
Se quiser seguir os exemplos, consulte Dados de amostra para executar consultas para exibir uma amostra de dados e aprender a usar a console do OCI para criar as tabelas de exemplo e carregar dados usando arquivos JSON.
Tópicos Relacionados
Expressões de Extração
extract_expression ::= EXTRACT "(" id FROM expression ")"
A expressão após a palavra-chave FROM deve retornar no máximo um timestamp ou NULL. Se o resultado desta expressão for NULL ou vazio, o resultado de EXTRACT também será NULL ou vazio, respectivamente. Caso contrário, o componente especificado pelo id será retornado. Esse ID deve ser uma das seguintes palavras-chave: 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
Explicação: Você primeiro usa CAST para converter o bagArrivalDate
em um TIMESTAMP e, em seguida, extrai o componente YEAR do Timestamp.
{"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)
Explicação: Você deseja saber os detalhes dos voos que viajaram pelo MIA entre as 10:00 e as 10:00 em fevereiro de 2019. Você usa várias condições de filtro aqui. Primeiro, o voo deve ter se originado ou atravessado pelo MIA. O ano de chegada deve ser 2019 e o mês de chegada deve ser 2 (fevereiro). Em seguida, filtre se a hora de chegada estiver entre as 10:00 e as 10:00 (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"
Explicação: Você primeiro usa CAST para converter o bagArrivalDate
em um TIMESTAMP e, em seguida, extrai o componente YEAR e o componente MONTH do Timestamp.
{"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
é nivelado e o valor da data de chegada do repositório é extraído do array. Em seguida, use CAST para converter o bagArrivalDate
em um TIMESTAMP e extraia o componente YEAR e o componente MONTH do Timestamp. Em seguida, use a função count
para obter a bagagem total correspondente a cada mês.
Observação:
Uma suposição nos dados é que toda a bagagem chegou no mesmo ano. Portanto, você agrupa os dados apenas com base no mês.{"MONTH":2,"COUNT":11}
{"MONTH":3,"COUNT":10}
Função timestamp_add()
Adiciona uma duração a um valor de timestamp e retorna o novo timestamp. A duração pode ser positiva ou negativa. O tipo de resultado é TIMESTAMP(9)
.
TIMESTAMP(9) timestamp_add(TIMESTAMP timestamp, STRING duration)
- timestamp: Um valor TIMESTAMP ou um valor que pode ser convertido em TIMESTAMP.
- duração: Uma string com o formato [-](<n> <UNIT>)+, onde 'n' é um número e <UNIT> pode ser YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, NANOSECOND ou a forma plural dessas palavras-chave (por exemplo, YEARS).
Observação:
A palavra-chave UNIT não faz distinção entre maiúsculas e minúsculas. - valor de retorno: TIMESTAMP(9)
SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag
WHERE ticketNo=1762399766476
Explicação: no aplicativo de companhia aérea, um cliente pode ter qualquer número de trechos de voo, dependendo da origem e do destino. Na consulta acima, você está extraindo a chegada estimada na "primeira etapa" da viagem. Portanto, o primeiro registro do array flightsLeg
é extraído e o tempo estimatedArrival
é extraído do array e um buffer de "5 minutos" é adicionado a ele e exibido.
{"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}
Observação:
A coluna estimatedArrival
é STRING. Se a coluna tiver valores STRING no formato ISO-8601, ela será convertida automaticamente pelo runtime do SQL no tipo de dados TIMESTAMP.
ISO8601 descreve uma maneira aceita internacionalmente de representar datas, horas e durações.
Syntax: Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]
- AAAA especifica o ano, com quatro dígitos decimais
- MM especifica o mês, como dois dígitos decimais, de 00 a 12
- DD especifica o dia, como dois dígitos decimais, de 00 a 31
- hh especifica a hora, como dois dígitos decimais, de 00 a 23
- mm especifica os minutos, como dois dígitos decimais, de 00 a 59
- ss[.s[s[s[s]]]] especifica os segundos, como dois dígitos decimais, de 00 a 59, opcionalmente seguido por um ponto decimal e de 1 a 6 dígitos decimais (representando a parte fracional de um segundo).
- Z especifica o horário UTC (fuso horário 0). (Também pode ser especificado por +00:00, mas não por -00:00.)
- (+|-)hh:mm especifica o fuso horário como diferença do UTC. (Um de + ou - é obrigatório.)
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
Explicação: Você deseja exibir o tempo estimatedArrival
em cada trecho. O número de pernas pode ser diferente para cada cliente. Portanto, a referência de variável é usada na consulta acima e o array baggageInfo
e o array flightLegs
são aninhados para executar a 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")]
Explicação: Você obtém uma contagem do número de malas processadas pelo aplicativo da companhia aérea na última semana. Um cliente pode ter mais de um repositório (o array bagInfo
pode ter mais de um registro). O bagArrivalDate
deve ter um valor entre hoje e os últimos 7 dias. Para cada registro no array bagInfo
, você determina se a hora de chegada da bolsa está entre a hora agora e uma semana atrás. A função current_time
fornece a hora agora. Uma condição EXISTS é usada como filtro para determinar se a bolsa tem uma data de chegada na última semana. A função count
determina o número total de sacos nesse período.
{"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")]
Explicação: Você obtém uma contagem do número de malas que serão processadas pelo aplicativo da companhia aérea nas próximas 6 horas. Um cliente pode ter mais de um repositório (ou seja, o array bagInfo
pode ter mais de um registro). O bagArrivalDate
deve estar entre o horário agora e as próximas 6 horas. Para cada registro no array bagInfo
, você determina se a hora de chegada da bolsa está entre a hora atual e seis horas depois. A função current_time
fornece a hora agora. Uma condição EXISTS é usada como filtro para determinar se a bolsa tem uma data de chegada nas próximas seis horas. A função count
determina o número total de sacos nesse período.
{"COUNT_NEXT6HOURS":0}
Funções timestamp_diff() e get_duration()
timestamp_diff()
Retorna o número de milissegundos entre dois valores de timestamp. O tipo de resultado é LONG
.
LONG timestamp_diff(TIMESTAMP timestamp1, TIMESTAMP
timestamp2)
- timestamp1: Um valor TIMESTAMP ou um valor que pode ser convertido em TIMESTAMP
- timestamp2: Um valor TIMESTAMP ou um valor que pode ser convertido em TIMESTAMP
- returnvalue: LONG
get_duration()
Converte o número fornecido de milissegundos em uma string de duração. O tipo de resultado é STRING
.
STRING get_duration(LONG duration_millis)
- duration_millis: a duração em milissegundos
- returnvalue: STRING. O formato de string de duração retornado é [-](<n> <UNIT>)+, onde o <UNIT> pode ser DAY, HOUR, MINUTE, SECOND e MILLISECOND, por exemplo, "1 dia 2 horas" ou "-10 minutos 0 segundo 500 milissegundos".
Exemplos de:
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
Explicação: Em um aplicativo de companhia aérea, cada cliente pode ter um número diferente de saltos/pernas entre a origem e o destino. Nesta consulta, você determina o tempo entre cada trecho de voo. Isso é determinado pela diferença entre bagArrivalDate
e flightDate
para cada trecho de voo. Para determinar a duração em dias, horas ou minutos, passe o resultado da função timestamp_diff
para a função 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)
Explicação: Nos dados da bagagem, cada flightLeg
tem um array de ações. Há três ações diferentes no array de ações. O código de ação do primeiro elemento da matriz é Check-in/Descarregamento. Para a primeira perna, o código de ação é Check-in e para as outras pernas, o código de ação é Offload no hop. O código de ação do segundo elemento do array é BagTag Scan. Na consulta acima, você determina a diferença no tempo de ação entre a verificação da tag do repositório e o horário de check-in. Use a função contains
para filtrar o tempo de ação somente se o código de ação for Check-in ou BagScan. Como apenas a primeira etapa do voo tem detalhes de check-in e verificação de bagagem, você também filtra os dados usando a função starts_with
para extrair apenas o código-fonte fltRouteSrc
. Para determinar a duração em dias, horas ou minutos, passe o resultado da função timestamp_diff
para a função 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
Explicação: Em um aplicativo de companhia aérea, cada cliente pode ter um número diferente de saltos/pernas entre a origem e o destino. No exemplo acima, você determina o tempo necessário para que a bolsa atinja o primeiro ponto de trânsito. Nos dados da bagagem, flightLeg
é um array. O primeiro registro da matriz refere-se aos detalhes do primeiro ponto de trânsito. O flightDate
no primeiro registro é o horário em que a bolsa sai da origem e o estimatedArrival
no primeiro registro de trecho de voo indica o horário em que atinge o primeiro ponto de trânsito. A diferença entre os dois dá o tempo necessário para que a bolsa atinja o primeiro ponto de trânsito. Para determinar a duração em dias, horas ou minutos, passe o resultado da função timestamp_diff
para a função 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"}
Exemplos usando a API QueryRequest
Você pode usar a API QueryRequest
e aplicar funções SQL para extrair dados de uma tabela NoSQL.
Para executar sua consulta, use a 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 executar sua consulta, use o 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 executar uma consulta, use a função 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 executar uma consulta, use o 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 executar uma consulta, você pode chamar o método QueryAsync
ou chamar o método GetQueryAsyncEnumerable
e iterar sobre o enumerável assí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);