Uso de Funciones de Registro de Hora en Consultas

Puede realizar varias operaciones en los valores de registro de hora y duración.

Puede agregar una duración a un registro de hora, buscar la diferencia entre dos registros de hora y redondear el registro de hora a una unidad especificada. Puede emitir un registro de hora de entrada/salida de cadena con patrones personalizados. Algunas de las funciones admiten la extracción de la parte de fecha de un registro de hora. También puede utilizar estas funciones para mostrar la hora actual.

Se admiten las siguientes funciones de registro de hora:

Tabla 1: Funciones de registro de hora

Función Descripción
registro de hora_add Agrega una duración a un valor de registro de hora.
registro de hora_diff Devuelve el número de milisegundos entre dos valores de registro de hora.
get_duración Convierte el número dado de milisegundos en una cadena de duración.
registro de hora_ceil Redondea el valor de registro de hora a la unidad especificada.
registro de hora_planta/registro de hora_trunc Redondea a la baja el valor de registro de hora a la unidad especificada.
registro de hora_round Redondea el valor del registro de hora a la unidad especificada.
registro de hora_cubo Redondea el valor del registro de hora al inicio del intervalo especificado, empezando por un valor de origen especificado.
format_timestamp Convierte un registro de hora en una cadena según el patrón especificado y la zona horaria.
parse_to_timestamp Convierte una cadena en el patrón especificado en un valor de registro de hora.
a_último día_de_mes Devuelve el último día del mes a partir de un registro de hora determinado.
Funciones de extracción de registro de hora

Extrae la parte de fecha correspondiente de un registro de hora determinado. Están soportadas las siguientes funciones:

  • año
  • mes
  • día
  • hora
  • minuto
  • segundo
  • milisegundo
  • microsegundo
  • nanosegundo

Devuelve el número de semana dentro del año. Están soportadas las siguientes funciones:

  • semana
  • isoweek

Devuelve el índice correspondiente de un registro de hora determinado. Están soportadas las siguientes funciones:

  • quarter
  • día_de_semana
  • día_de_mes
  • día_de_año
current_time_millis Devuelve el tiempo actual como número de milisegundos.
hora_actual Devuelve la hora actual como valor de registro de hora.

Si desea seguir los ejemplos, consulte Datos de ejemplo para ejecutar consultas para ver un ejemplo de datos y utilizar los scripts para cargar datos de ejemplo para la prueba. Los scripts crean las tablas que se utilizan en los ejemplos y cargan los datos en las tablas.

Si desea seguir los ejemplos, consulte Datos de ejemplo para ejecutar consultas para ver un ejemplo de datos y aprender a utilizar la consola de OCI para crear tablas de ejemplo y cargar datos mediante archivos JSON.

Funciones Aritméticas de Registro de Hora

Puede utilizar las funciones timestamp_add, timestamp_diff o get_duration para realizar operaciones aritméticas en los valores de registro de hora y duración.

Ejemplo 1 - En la aplicación de la aerolínea, un buffer de cinco minutos de retraso se considera "a tiempo". Imprima la hora de llegada estimada en el primer tramo con un buffer de cinco minutos para el pasajero con el número de ticket 1762399766476.

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 aerolínea, 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 el tiempo estimatedArrival de la matriz y se agrega un buffer de "5 minutos" a ese registro y se muestra.

Salida:

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

Nota:

La columna estimatedArrival es STRING. Si la columna tiene valores STRING en formato ISO-8601, el tiempo de ejecución SQL lo convertirá automáticamente en el tipo de dato TIMESTAMP.

ISO8601 describe una forma internacionalmente aceptada de representar fechas, horas y duraciones.

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

Dónde:

Ejemplo 2: imprima la hora de llegada estimada en cada tramo con un buffer de cinco minutos para el pasajero con el número de ticket 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

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.

Salida:

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

Ejemplo 3 - ¿Cuántas bolsas llegaron en la ú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")]

Explicación: obtiene un recuento del número de bolsas procesadas por la aplicación de la aerolínea en la última semana. Un cliente puede tener más de una bolsa (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, puede determinar 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.

Salida:

{"COUNT_LASTWEEK":0}

Ejemplo 4: busque el número de bolsas que llegarán en las 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")]

Explicación: obtienes un recuento del número de bolsas que procesará la aplicación de la aerolínea en las próximas 6 horas. Un cliente puede tener más de una bolsa (la matriz bagInfo puede tener más de un registro). bagArrivalDate debe estar entre la hora actual y las próximas 6 horas. Para cada registro de la matriz bagInfo, puede determinar 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.

Salida:

{"COUNT_NEXT6HOURS":0}

Ejemplo 5: ¿Cuál es la duración entre el momento en que se abordó el equipaje en una pierna y se alcanzó la siguiente pierna para el pasajero con el número de boleto 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

Explicación: en una aplicación de aerolínea, cada cliente puede tener un número diferente de saltos/patas entre su origen y destino. En esta consulta, se determina el tiempo transcurrido entre 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.

Salida:

{"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 la duración en milisegundos, utilice la función 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

Salida:

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

Ejemplo 6: ¿Cuánto tiempo lleva desde el momento del registro de entrada hasta el momento en que se escanea la bolsa en el punto de embarque del pasajero con el número de ticket 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)

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 el resto de 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 Escaneo de BagTag. En la consulta anterior, se determina la diferencia en el tiempo de acción entre la exploración de etiqueta de bolsa y la hora de entrada. La función contains se utiliza para filtrar la hora de acción solo si el código de acción es Entrada o BagScan. Dado que solo el primer tramo de vuelo tiene detalles de check-in y escaneo de bolsas, 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.

Para determinar la duración en milisegundos, utilice la función 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)

Salida:

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

Ejemplo 7: ¿Cuánto tiempo tardan las bolsas de un cliente con el ticket no 1762320369957 en llegar al primer punto 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

Explicación: en una aplicación de aerolínea, cada cliente puede tener un número diferente de saltos/patas entre su origen y destino. En el ejemplo anterior, se 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 primeros detalles del punto de tránsito. flightDate en el primer registro es la hora a la que la bolsa sale del origen y estimatedArrival en el primer registro de tramo de vuelo indica la hora a la que llega al primer punto de tránsito. La diferencia entre los dos da el tiempo necesario para que la bolsa alcance el 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.

Para determinar la duración en milisegundos, utilice la función 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

Salida:

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

Funciones de Redondeo de Registro de Hora

Puede utilizar las funciones timestamp_ceil, timestamp_floor, timestamp_trunc, timestamp_round y timestamp_bucket para redondear los valores de registro de hora.

Para las funciones timestamp_ceil, timestamp_floor, timestamp_trunc y timestamp_round, debe proporcionar un unit como segundo argumento. unit especifica la precisión que se debe tener en cuenta al redondear el registro de hora de entrada.

Las siguientes unidades están soportadas en singular o plural: YEAR, IYEAR, QUARTER, MONTH, WEEK, IWEEK, DAY, HOUR, MINUTE, SECOND.

Puede utilizar la función timestamp_bucket para redondear el valor de registro de hora especificado al principio del intervalo especificado (bloque). El intervalo comienza en un origen especificado en la línea de tiempo.

timestamp_bucket soporta los siguientes intervalos en formato singular o plural: WEEK, DAY, HOUR, MINUTE, SECOND.

Ejemplo 1: a partir de los datos de seguimiento de equipaje de la aerolínea, imprima la fecha de llegada de la bolsa y la fecha de subasta de la bolsa para un pasajero con el número de boleto 1762344493810, teniendo en cuenta los 90 días como período de retención de equipaje.

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

Explicación: esta consulta muestra cómo anidar las funciones de registro de hora. Para determinar la fecha en que se retiene una bolsa no reclamada, agregue 90 días a bagArrivalDate mediante la función timestamp_add. La función timestamp_ceil redondea el valor al principio del día siguiente.

Salida:

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

Ejemplo 2: imprima el nombre, el número de vuelo y la fecha de viaje de todos los pasajeros que abordaron en el aeropuerto de origen JFK en el mes de marzo 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'

Explicación: utiliza la función timestamp_floor con el valor de unidad MONTH para redondear a la baja las fechas de viaje hasta el inicio del mes. A continuación, compare el valor de registro de hora resultante con la cadena "2019-03-01" para seleccionar los pasajeros deseados. Esta consulta no tiene en cuenta a los pasajeros en tránsito.

En este ejemplo, se proporciona la fecha en una cadena con formato ISO-8601, que convierte implícitamente CAST en un valor TIMESTAMP.

Para evitar la duplicación de resultados debido a varias bolsas comprobadas por parte de un pasajero, sólo tiene en cuenta el primer elemento de la matriz bagInfo en esta consulta.

Salida:

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

Ejemplo 3: a partir de los datos de seguimiento de equipaje de la aerolínea, imprima todas las actividades realizadas en las maletas facturadas en la estación de origen MEL. Alinee las acciones con un intervalo de un 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"

Explicación: en esta consulta, utilice la función timestamp_round con la unidad como MINUTE para redondear actionTime al minuto más cercano.

Para evitar la duplicación de resultados debido a varios equipajes facturados por un pasajero, solo tiene en cuenta el primer elemento de la matriz bagInfo en esta consulta.

Salida:

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

Ejemplo 4: recupere las estadísticas del número de pasajeros que salen del aeropuerto IST cada 12 horas con cubos a partir del 1 de enero de 2019. Tenga en cuenta los datos solo para el mes de febrero 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

Explicación: para considerar a los pasajeros que viajan en febrero de 2019, utilice la función timestamp_floor y redondee flightDate al principio del mes. Compare el resultado con la cadena "2019-02-01T00:00:00Z". En este ejemplo, se proporciona la fecha en una cadena con formato ISO-8601, que convierte implícitamente CAST en un valor TIMESTAMP.

Para incluir los vuelos en tránsito desde el aeropuerto IST, utilice el constructor de matrices [ ] para indicar que flightLegs es una matriz y considere cada elemento de matriz fltRouteSrc en la búsqueda.

Utilice la función timsestamp_bucket en los campos flightDate con un intervalo de 12 horas y el origen del 1 de enero de 2019.

Salida:

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

Funciones de Formato de Registro de Hora

Puede utilizar las funciones format_timestamp y parse_to_timestamp para aplicar formato a los valores de registro de hora. Además, puede utilizar la función to_last_day_of_month para recuperar el último día del mes a partir de un registro de hora determinado.

Ejemplo 1: para un pasajero con un número de billete específico, imprima la hora de llegada estimada en el primer tramo de acuerdo con pattern y timezone introducidos.

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

Explicación: en esta consulta, especifica el campo estimatedArrival, pattern y el nombre completo de timezone como argumentos para la función format_timestamp para convertir la cadena timestamp en el patrón "MMM dd, yyyy HH:mm:ss" especificado.

Nota: La letra 'O' en el argumento pattern representa ZoneOffset, que imprime la cantidad de tiempo que difiere de Greenwich/UTC en la cadena resultante.

Salida:

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

Ejemplo 2: analice el valor string proporcionado con el valor pattern especificado, que incluye un desplazamiento de zona, en un registro de hora.

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

Explicación: en esta consulta, el argumento string tiene un ID de zona horaria, GMT+02:00, por lo que el argumento pattern debe incluir un símbolo de zona o un ZoneOffset. Cuando se ajusta en la función format_timestamp, el registro de hora de salida se mostrará en la zona horaria GMT+02:00.

Salida:

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

Ejemplo 3: para un suscriptor, imprima el último día del mes en el que caduca la suscripción a la cuenta.

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

Salida:

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

Funciones de extracción de registro de hora

Las funciones de extracción de registro de hora recuperan la fecha, la semana o el valor de índice correspondientes de un registro de hora determinado.

Las funciones de extracción de fecha devuelven el año/mes/día/hora/minuto/segundo/milisegundo/microsegundo/nanosegundo correspondiente de un registro de hora.

Ejemplo 1 - Obtener detalles consolidados de viaje de los pasajeros de los datos de seguimiento de equipaje de la aerolínea.

En una aplicación de línea aérea, es beneficioso para los pasajeros tener un resumen rápido de sus próximos detalles de viaje. Puede utilizar otras funciones de tiempo para obtener detalles de viaje consolidados de los pasajeros de la tabla 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

Explicación:

Puede utilizar las funciones de tiempo para recuperar la fecha de viaje, el mes y el año. La función de cadena concat se utiliza para concatenar los registros de viaje recuperados para mostrarlos en el formato deseado en la aplicación. Primero debe utilizar la expresión CAST para convertir flightDates en TIMESTAMP y, a continuación, recuperar los detalles de fecha, mes y año del registro de hora.

Salida:

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

La consulta devuelve los detalles del vuelo que pueden servir como una consulta rápida para los pasajeros.

Las funciones de extracción de semana devuelven la semana/isoweek correspondiente de un registro de hora.

Ejemplo 2: Determinar la semana y el número de semana ISO a partir de la fecha de viaje de un pasajero.

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

Explicación: primero debe utilizar la expresión CAST para convertir flightDate en un TIMESTAMP y, a continuación, recuperar la semana y el isoweek del registro de hora.

Salida:

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

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

Las funciones de extracción de índice de registro de hora devuelven el índice de trimestre/semana/mes/año correspondiente de un registro de hora.

Ejemplo 3: busque el día de la semana para los registros de hora indicados.

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

Explicación: el segundo registro de hora de la consulta está en un formato no admitido '06/19/24' por sí mismo, por lo que debe ajustarlo en la función parse_to_timestamp para que sea válido.

Salida:

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

Funciones de hora actual

Puede utilizar las funciones current_time_millis y current_time para recuperar la hora actual. La función current_time_millis devuelve el tiempo como el número de milisegundos. La función current_time devuelve la hora como un valor de registro de hora.

Ejemplo 1 - Determinar el lapso de tiempo entre la última fecha de viaje de un pasajero y la fecha actual.

En una aplicación de línea aérea, algunos clientes viajan con mucha frecuencia y tienen derecho a recompensas de millas de viajero frecuentes. Puede determinar el lapso de tiempo entre la última fecha de viaje de un pasajero y la fecha actual para evaluar si se pueden considerar para dicho 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

Explicación:

Puede utilizar la función current_time para obtener la hora actual. Para determinar el intervalo de tiempo entre la fecha del último viaje y la fecha actual, puede proporcionar la hora actual a la función get_duration/timestamp_diff junto con la hora del último viaje. Para obtener más información sobre las funciones timestamp_diff y get_duration.

Salida:

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

La función current_time se utiliza para calcular la hora actual. Utilice la función timestamp_diff para calcular la diferencia horaria entre la hora actual y la fecha del último vuelo. Primero debe utilizar la expresión CAST para convertir flightDates en TIMESTAMP y, a continuación, recuperar los detalles de día, mes y año del registro de hora. Dado que la función timestamp_diff devuelve el número de milisegundos entre dos valores de registro de hora, utilice la función get_duration para convertir los milisegundos en una cadena de duración.

La función get_duration convierte los milisegundos en días, horas, minutos, segundos y milisegundos en función del valor de retorno. Las conversiones siguientes se tienen en cuenta a efectos de cálculo:

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

Por ejemplo: si la función timestamp_diff devuelve el valor 129084684821 milisegundos, la función get_duration lo convierte de manera correspondiente a 1494 días 52 minutos 4 segundos 687 milisegundos.

Ejemplos con 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().

Descargue el código completo SQLFunctions.java de los ejemplos aquí.

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

Descargue el código completo SQLFunctions.py de los ejemplos aquí.

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

Descargue el código completo SQLFunctions.go de los ejemplos aquí.

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

JavaScript: descargue el código completo SQLFunctions.js de los ejemplos aquí.

  //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: descargue el código completo SQLFunctions.ts de los ejemplos aquí.

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 enumerable asíncrono resultante.

Descargue el código completo de SQLFunctions.cs en los ejemplos aquí.

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

Temas relacionados