Usando funções de Timestamp em consultas

Você pode executar várias operações nos valores de timestamp e duração.

Você pode adicionar uma duração a um timestamp, localizar a diferença entre dois timestamps e arredondar o timestamp para uma unidade especificada. Você pode converter um timestamp de/para string com padrões personalizados. Algumas das funções suportam a extração da parte de data de um timestamp. Você também pode usar essas funções para exibir a hora atual.

As seguintes funções de timestamp são suportadas:

Tabela 1 - Funções de Timestamp

Função Descrição
timestamp_add Adiciona uma duração a um valor de marcador de data/hora.
timestamp_diff Retorna o número de milissegundos entre dois valores de timestamp.
obter_duração Converte o número fornecido de milissegundos em uma string de duração.
timestamp_ceil Arredonda o valor do marcador de data/hora para a unidade especificada.
timestamp_floor/timestamp_trunc Arredonda para baixo o valor do timestamp para a unidade especificada.
timestamp_round Arredonda o valor do marcador de data/hora para a unidade especificada.
timestamp_bucket Arredonda o valor de timestamp para o início do intervalo especificado, começando de um valor de origem especificado.
carimbo de data/hora Converte um timestamp em uma string de acordo com o padrão especificado e o fuso horário.
fazer parse_to_timestamp Converte uma string no padrão especificado em um valor de timestamp.
ao_último_dia_do_mês Retorna o último dia do mês de um determinado timestamp.
Funções de extração de timestamp

Extrai a parte de data correspondente de um determinado timestamp. As seguintes funções são suportadas:

  • ano
  • mês
  • dia
  • hora
  • minuto
  • segundo
  • milissegundo
  • microssegundo
  • nanossegundo

Retorna o número da semana dentro do ano. As seguintes funções são suportadas:

  • semana
  • sem dono

Retorna o índice correspondente de um determinado timestamp. As seguintes funções são suportadas:

  • trimestre
  • dia_de_semana
  • dia_do_mês
  • dia_do_ano
milissegundos_atuais Retorna a hora atual como o número de milissegundos.
current_time Retorna a hora atual como um valor de timestamp.

Se você quiser acompanhar os exemplos, consulte Dados de amostra para executar consultas 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 acompanhar os exemplos, consulte Dados de amostra para executar consultas para exibir dados de amostra e aprender a usar a console do OCI para criar as tabelas de exemplo e carregar dados usando arquivos JSON.

Funções Aritméticas de Timestamp

Você pode usar as funções timestamp_add, timestamp_diff ou get_duration para executar operações aritméticas nos valores de timestamp e duração.

Exemplo 1 - No aplicativo da companhia aérea, um buffer de cinco minutos de atraso é considerado "a tempo". Imprima a hora estimada de chegada no primeiro trecho com um buffer de cinco minutos para o passageiro com o número de bilhete 1762399766476.

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á obtendo 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.

Saída:

{"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}

Observação:

A coluna estimatedArrival é uma STRING. Se a coluna tiver valores STRING no formato ISO-8601, ela será automaticamente convertida pelo runtime SQL no tipo de dados TIMESTAMP.

A ISO8601 descreve uma maneira internacionalmente aceita de representar datas, horários e durações.

Sintaxe: Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

Onde:

Exemplo 2 - Imprima a hora estimada de chegada em cada trecho com um buffer de cinco minutos para o passageiro com o número de bilhete 1762399766476.

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 não são aninhados para executar a consulta.

Saída:

{"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"}

Exemplo 3 - Quantas malas chegaram na última semana?

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 pela solicitação de companhia aérea na última semana. Um cliente pode ter mais de um repositório (ou seja, o array bagInfo pode ter mais de um registro). ObagArrivalDate 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 você o tempo 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 bolsas nesse período.

Saída:

{"COUNT_LASTWEEK":0}

Exemplo 4 - Encontre o número de malas que chegam nas próximas 6 horas.

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 pela solicitação de 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 atual 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 agora e seis horas depois. A função current_time fornece a você o tempo 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 bolsas nesse período.

Saída:

{"COUNT_NEXT6HOURS":0}

Exemplo 5 - Qual é a duração entre a hora em que a bagagem foi embarcada em um trecho e chegou ao próximo trecho para o passageiro com o número de passagem 1762355527825?

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 sua origem e destino. Nesta consulta, você determina o tempo necessário entre cada trecho de voo. Isto é determinado pela diferença entre bagArrivalDate e flightDate para cada trecho de voo. Para determinar a duração em dias, horas ou minutos, informe o resultado da função timestamp_diff para a função get_duration.

Saída:

{"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"}

Para determinar a duração em milissegundos, use a função 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

Saída:

{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:00:00Z","diff":11820000}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:23:00Z","diff":10440000}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T08:23:00Z","diff":6840000}

Exemplo 6 - Quanto tempo leva desde o momento do check-in até o momento em que a mala é escaneada no ponto de embarque do passageiro com o número de bilhete 176234463813?

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 de 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 no array é Check-in/Descarregamento. Para o primeiro trecho, o código de ação é Check-in e para os outros trechos, o código de ação é Offload no hop. O código de ação para o 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 de bolsa e o tempo de check-in. Use a função contains para filtrar o horário da ação somente se o código da ação for Check-in ou BagScan. Como apenas o primeiro trecho de voo tem detalhes de check-in e varredura de bolsa, você também filtra os dados usando a função starts_with para buscar apenas o código-fonte fltRouteSrc. Para determinar a duração em dias, horas ou minutos, informe o resultado da função timestamp_diff para a função get_duration.

Para determinar a duração em milissegundos, use a função 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)

Saída:

{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":"- 1 hour 24 minutes"}

Exemplo 7 - Quanto tempo leva para as malas de um cliente com o tíquete nº 1762320369957 chegarem ao primeiro ponto de trânsito?

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 sua origem e destino. No exemplo acima, você determina o tempo necessário para a bolsa chegar ao primeiro ponto de trânsito. Nos dados de bagagem, o flightLeg é uma matriz. O primeiro registro no array se refere aos primeiros detalhes do ponto de trânsito. O flightDate no primeiro registro é o horário em que o saco 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 o saco atinja o primeiro ponto de trânsito. Para determinar a duração em dias, horas ou minutos, informe o resultado da função timestamp_diff para a função get_duration.

Para determinar a duração em milissegundos, use a função 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

Saída:

{"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"}

Funções de Arredondamento de Timestamp

Você pode usar as funções timestamp_ceil, timestamp_floor, timestamp_trunc, timestamp_round e timestamp_bucket para arredondar os valores de timestamp.

Para as funções timestamp_ceil, timestamp_floor, timestamp_trunc e timestamp_round, você deve fornecer um unit como segundo argumento. O unit especifica a precisão a ser considerada ao arredondar o timestamp de entrada.

As seguintes unidades são suportadas no formato singular ou plural: YEAR, IYEAR, QUARTER, MONTH, WEEK, IWEEK, DAY, HOUR, MINUTE, SECOND.

Você pode usar a função timestamp_bucket para arredondar o valor de timestamp fornecido para o início do intervalo especificado (bloco). O intervalo começa em uma origem especificada na linha do tempo.

O timestamp_bucket suporta os seguintes intervalos no formato singular ou plural: WEEK, DAY, HOUR, MINUTE, SECOND.

Exemplo 1 - A partir dos dados de rastreamento de bagagem de companhia aérea, imprima a data de chegada da bagagem e a data de leilão da bagagem de um passageiro com o número de passagem 1762344493810, considerando 90 dias como o período de retenção da bagagem.

SELECT $b.bagArrivalDate AS BagArrival,
timestamp_ceil(timestamp_add($b.bagArrivalDate, "90 Days"), 'day') AS BagCollection
FROM BaggageInfo bag, bag.bagInfo AS $b
WHERE ticketNo=1762344493810

Explicação: Esta consulta mostra como aninhar as funções de timestamp. Para determinar a data em que um repositório não reclamado é retido, adicione 90 dias ao bagArrivalDate usando a função timestamp_add. A função timestamp_ceil arredonda o valor para o início do dia seguinte.

Saída:

{"BagArrival":"2019-02-01T16:13:00Z","BagCollection":"2019-05-03T00:00:00Z"}

Exemplo 2 - Imprima o nome, o número do voo e a data de viagem de todos os passageiros que embarcaram no aeroporto de origem JFK no mês de março de 2019.

SELECT bag.fullName, $f.flightNo, $f.flightDate
FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[0] AS $f
WHERE $f.fltRouteSrc = "JFK" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-03-01'

Explicação: você usa a função timestamp_floor com o valor unitário como MONTH para arredondar as datas de deslocamento para o início do mês. Em seguida, você compara o valor do timestamp resultante com a string "2019-03-01" para selecionar os passageiros desejados. Esta consulta não considera os passageiros em trânsito.

Este exemplo fornece a data em uma string formatada em ISO-8601, que é transformada implicitamente em um valor de TIMESTAMP.

Para evitar a duplicação de resultados devido a várias malas despachadas por um passageiro, considere apenas o primeiro elemento do array bagInfo nesta consulta.

Saída:

{"fullName":"Kendal Biddle","flightNo":"BM127","flightDate":"2019-03-04T06:00:00Z"}
{"fullName":"Dierdre Amador","flightNo":"BM495","flightDate":"2019-03-07T07:00:00Z"}

Exemplo 3 – A partir dos dados de rastreamento de bagagem de companhia aérea, imprima todas as atividades realizadas nas malas despachadas na estação de origem MEL. Alinhe as ações a um intervalo de um minuto.

SELECT $b.actionAt,
       $b.actionCode,
       timestamp_round($b.actionTime, 'MINUTE') as actionTime
FROM baggageInfo bag, bag.bagInfo[0].flightLegs[0].actions[] AS $b
WHERE bag.bagInfo[0].flightLegs[0].fltRouteSrc = "MEL"

Explicação: Nesta consulta, você usa a função timestamp_round com unidade como MINUTE para arredondar o actionTime para o minuto mais próximo.

Para evitar a duplicação de resultados devido a várias bagagens despachadas por um passageiro, você considera apenas o primeiro elemento da matriz bagInfo nesta consulta.

Saída:

{"actionAt":"MEL","actionCode":"ONLOAD to LAX","actionTime":"2019-03-01T12:20:00Z"}
{"actionAt":"MEL","actionCode":"BagTag Scan at MEL","actionTime":"2019-03-01T11:52:00Z"}
{"actionAt":"MEL","actionCode":"Checkin at MEL","actionTime":"2019-03-01T11:43:00Z"}

Exemplo 4 - Obter as estatísticas do número de passageiros que partem do aeroporto IST a cada 12 horas com baldes a partir de 1º de janeiro de 2019. Considere os dados somente do mês de fevereiro de 2019.

SELECT $t AS DATE,
count($t) AS FLIGHTCOUNT
FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[] $f,
timestamp_bucket($f.flightDate, '12 HOURS', '2019-01-01T00') $t
WHERE $f.fltRouteSrc =any "IST" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-02-01T00:00:00Z'
GROUP BY $t
ORDER BY $t

Explicação: Para considerar os passageiros que viajam em fevereiro de 2019, use a função timestamp_floor e arredonde o flightDate para o início do mês. Compare o resultado com a string "2019-02-01T00:00:00Z". Este exemplo fornece a data em uma string formatada em ISO-8601, que é transformada implicitamente em um valor de TIMESTAMP.

Para incluir os voos de trânsito do aeroporto IST, use o construtor de array [ ] para indicar que o flightLegs é uma matriz e considere cada elemento da matriz fltRouteSrc na pesquisa.

Use a função timsestamp_bucket nos campos flightDate com intervalo de 12 horas e origem de 1º de janeiro de 2019.

Saída:

{"DATE":"2019-02-02T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-04T00:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-04T12:00:00.000000000Z","FLIGHTCOUNT":2}
{"DATE":"2019-02-07T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-11T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-12T00:00:00.000000000Z","FLIGHTCOUNT":2}
{"DATE":"2019-02-12T12:00:00.000000000Z","FLIGHTCOUNT":1}

Funções de Formato de Timestamp

Você pode usar as funções format_timestamp e parse_to_timestamp para formatar valores de timestamp. Além disso, você pode usar a função to_last_day_of_month para extrair o último dia do mês de um determinado timestamp.

Exemplo 1 - Para um passageiro com um número de bilhete específico, imprima a hora estimada de chegada na primeira mão de acordo com o pattern e o timezone inserido.

SELECT $info.estimatedArrival,
format_timestamp($info.estimatedArrival, "MMM dd, yyyy HH:mm:ss O", "America/Vancouver") AS FormattedTimestamp
FROM BaggageInfo bag, bag.bagInfo.flightLegs[0] AS $info
WHERE ticketNo= 1762399766476

Explicação: Nesta consulta, você especifica o campo estimatedArrival, pattern e o nome completo do timezone como argumentos para a função format_timestamp para converter a string timestamp no padrão "MMM dd, yyyy HH:mm:ss" especificado.

Observação: A letra 'O' no argumento pattern representa o ZoneOffset, que imprime o tempo que difere de Greenwich/UTC na string resultante.

Saída:

{"estimatedArrival":"2019-02-03T06:00:00Z","FormattedTimestamp":"Feb 02, 2019 22:00:00 GMT-8"}

Exemplo 2 - Faça parse do string fornecido com o pattern especificado, que inclui um deslocamento de zona, em um timestamp.

SELECT format_timestamp(parse_to_timestamp('2024/02/12 18:30:54 GMT+02:00', "yyyy/dd/MM HH:mm:ss OOOO"),"yyyy-MM-dd HH:mm:ss OOOO","GMT+02:00")AS TIMESTAMP
FROM BaggageInfo
WHERE ticketNo=1762390789239

Explicação: Nesta consulta, o argumento string tem um TimeZoneID, GMT+02:00, portanto o argumento pattern deve incluir um símbolo de zona ou um ZoneOffset. Quando encapsulado na função format_timestamp, o timestamp de saída será exibido no fuso horário GMT+02:00.

Saída:

{"TIMESTAMP":"2024-12-02 18:30:54 GMT+02:00"}

Exemplo 3 - Para um assinante, imprima o último dia do mês em que a assinatura da conta expira.

SELECT sa.acct_id, to_last_day_of_month(sa.account_expiry) AS lastday FROM stream_acct sa WHERE profile_name="DM"

Saída:

{"acct_id":4,"lastday":"2024-03-31T00:00:00Z"}

Funções de Extração de Timestamp

As funções de extração de timestamp extraem a data, a semana ou o valor de índice correspondente de um determinado timestamp.

As funções de extração de data retornam o ano/mês/dia/hora/minuto/segundo/milissegundo/microssegundo/nanossegundo correspondente de um timestamp.

Exemplo 1 - Obter detalhes consolidados de viagem dos passageiros dos dados de rastreamento de bagagem da companhia aérea.

Em um aplicativo de companhia aérea, é benéfico para os passageiros ter um resumo rápido de seus próximos detalhes de viagem. Você pode usar diversas funções de tempo para obter detalhes de viagem consolidados dos passageiros na tabela BaggageInfo.

SELECT DISTINCT
$s.fullName,
$s.bagInfo[].flightLegs[].flightNo AS flightnumbers,
$s.bagInfo[].flightLegs[].fltRouteSrc AS From,
concat ($t1,":", $t2,":", $t3) AS Traveldate
FROM baggageinfo $s, $s.bagInfo[].flightLegs[].flightDate AS $bagInfo,
day(CAST($bagInfo AS Timestamp(0))) $t1,
month(CAST($bagInfo AS Timestamp(0))) $t2,
year(CAST($bagInfo AS Timestamp(0))) $t3

Explicação:

Você pode usar as funções de tempo para recuperar a data, o mês e o ano de deslocamento. A função de string concat é usada para concatenar os registros de deslocamento recuperados para exibi-los no formato desejado no aplicativo. Primeiro, use a expressão CAST para converter o flightDates em um TIMESTAMP e, em seguida, extraia os detalhes de data, mês e ano do TIMESTAMP.

Saída:

{"fullName":"Adam Phillips","flightnumbers":["BM604","BM667"],"From":["MIA","LAX"],"Traveldate":"1:2:2019"}

{"fullName":"Adelaide Willard","flightnumbers":["BM79","BM907"],"From":["GRU","ORD"],"Traveldate":"15:2:2019"}

A consulta retorna os detalhes do voo, que podem servir como uma rápida pesquisa para os passageiros.

As funções de extração de semana retornam a semana/semana/semana correspondente de um timestamp.

Exemplo 2 - Determine a semana e o número da semana ISO a partir da data de viagem de um passageiro.

SELECT
$s.fullName,
$s.contactPhone,
week(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS TravelWeek,
isoweek(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS ISO_TravelWeek
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo

Explicação: primeiro use a expressão CAST para converter o flightDate em um TIMESTAMP e, em seguida, extraia a semana e o isoweek do TIMESTAMP.

Saída:

{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","TravelWeek":7,"ISO_TravelWeek":7}

{"fullName":"Adam Phillips","contactPhone":"893-324-1064","TravelWeek":5,"ISO_TravelWeek":5}

As funções de extração de índice de timestamp retornam o índice de trimestre/semana/mês/ano correspondente de um timestamp.

Exemplo 3 - Localize o dia da semana para determinados timestamps.

SELECT day_of_week("2024-06-19") AS DAYVAL1,
day_of_week(parse_to_timestamp('06/19/24', 'MM/dd/yy')) AS DAYVAL2
FROM BaggageInfo
WHERE ticketNo=1762344493810

Explicação: O segundo timestamp da consulta está em um formato não suportado '06/19/24' por si só, portanto, encapsule-o na função parse_to_timestamp para torná-lo válido.

Saída:

{
  "DAYVAL1" : 3,
  "DAYVAL2" : 3
}

Funções de Horário Atuais

Você pode usar as funções current_time_millis e current_time para extrair o horário atual. A função current_time_millis retorna o tempo como o número de milissegundos. A função current_time retorna o horário como um valor de timestamp.

Exemplo 1 - Determina o lapso de tempo entre a data da última viagem de um passageiro e a data atual.

Em um aplicativo de companhia aérea, alguns clientes viajam com muita frequência e têm direito a recompensas de milhas de passageiro frequente. Você pode determinar o lapso de tempo entre a última data de viagem de um passageiro e a data atual para avaliar se eles podem ser considerados para esse programa de recompensa.

SELECT
$s.fullName,
$s.contactPhone,
get_duration(timestamp_diff(current_time(), CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0)))) AS LastTravel
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo

Explicação:

Você pode usar a função current_time para obter o horário atual. Para determinar o intervalo de tempo entre a data do último deslocamento e a data atual, você pode fornecer o tempo atual à função get_duration/timestamp_diff junto com o último tempo de deslocamento. Para obter mais detalhes sobre as funções timestamp_diff e get_duration.

Saída:

{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","LastTravel":"1453 days 6 hours 20 minutes 56 seconds 601 milliseconds"}

{"fullName":"Adam Phillips","contactPhone":"893-324-1064","LastTravel":"1451 days 23 hours 19 minutes 39 seconds 543 milliseconds"}

Você usa a função current_time para calcular o horário atual. Use a função timestamp_diff para calcular a diferença de tempo entre a hora atual e a data do último voo. Primeiro, use a expressão CAST para converter o flightDates em um TIMESTAMP e, em seguida, extraia os detalhes de dia, mês e ano do TIMESTAMP. Como a função timestamp_diff retorna o número de milissegundos entre dois valores de timestamp, você usa a função get_duration para converter os milissegundos em uma string de duração.

A função get_duration converte os milissegundos em dias, horas, minutos, segundos e milissegundos com base no valor de retorno. As seguintes conversões são consideradas para fins de cálculo:

1000 milliseconds = 1 second
60 seconds = 1 minute
60 minutes = 1 hour
24 hours = 1 day

Por exemplo: Se a função timestamp_diff retornar o valor 129084684821 milissegundos, a função get_duration a converterá em 1494 dias 52 minutos 4 segundos 687 milissegundos.

Exemplos de uso da 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().

Faça download do código completo SQLFunctions.java pelos exemplos aqui.

 //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().

Faça download do código completo SQLFunctions.py nos exemplos aqui.

# 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.

Faça download do código completo SQLFunctions.go nos exemplos aqui.

 //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.

JavaScript: Faça download do código completo SQLFunctions.js dos exemplos aqui.

  //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);
   }
}

TypeScript: Faça download do código completo SQLFunctions.ts dos exemplos aqui.

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.

Faça download do código completo SQLFunctions.cs nos exemplos aqui.

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);

Tópicos Relacionados