sql - rango - trunc date oracle example

Conversión de número de SQL en una hora, para realizar comparaciones de fechas

Question

Estoy luchando con una comparación de tiempo SQL en dos campos que se han configurado como enteros, en lugar de marcas de tiempo.

Tengo algunas métricas de rendimiento en una base de datos que el desarrollador originalmente configuró como int (8). La base de datos contiene los tiempos de inicio y fin de una transacción. Por ejemplo

Algunos datos de muestra pueden ser

id | start_time | end_time
---------------------------
1  |      85958 | 90001 

Si simplemente restara los dos valores, obtendría 4043 segundos, cuando el tiempo de transacción es solo 3. Sin embargo, estoy luchando por convertir los valores en un formato de tiempo que me permita realizar comparaciones de fechas.

No puedo hacer este cálculo dentro de la aplicación, porque hay 100 filas en la base de datos por día, y estoy tratando de calcular los tiempos promedio y máximo para las transacciones.

EDITAR:

Para aclarar

Los tiempos están en segundos 85958 representan 8:59:58 90001 representa 9:00:01

Para empeorar las cosas, 1 minuto después de la medianoche 0:01:00 se representaría como 100.

Answer 1

¿Estás seguro de que necesitas convertir? Tal vez el número represente milisegundos, por lo que la diferencia sería ~ 4 segundos.

Answer 2

Probé esto en MySQL, pero estoy seguro de que la técnica se puede adaptar para trabajar en DB2:

SELECT
    (end_time DIV 10000) * 3600 +
    ((end_time DIV 100) % 100) * 60 +
    end_time % 100 -
    (start_time DIV 10000) * 3600 -
    ((start_time DIV 100) % 100) * 60 -
    start_time % 100
FROM table1

Resultado:

3

La forma en que funciona es usar la división de enteros y la operación de módulo para extraer las partes HH MM y SS de cada marca de tiempo y convertir cada parte en segundos. Los segundos se suman para formar un número total de segundos desde la medianoche para cada marca de tiempo. La diferencia entre estos dos da el tiempo de la transacción.

Tenga en cuenta que esto no funcionará si la transacción comienza antes de la medianoche y termina después de la medianoche. Es posible que deba considerar si el día ha cambiado y corregirlo. Si no tiene el día almacenado en su base de datos, puede buscar tiempos de transacción negativos y agregar 24 horas para que sean positivos y esto debería dar el resultado correcto (siempre que las transacciones no excedan un día de duración, pero Supongo que esto es poco probable en la práctica).

Mi intento de escribir esto para DB2 (no probado):

SELECT
    (end_time / 10000) * 3600 +
    MOD(end_time / 100, 100) * 60 +
    MOD(end_time, 100) -
    (start_time / 10000) * 3600 -
    MOD(start_time / 100, 100) * 60 -
    MOD(start_time, 100)
FROM table1

Answer 3

Suponiendo que está utilizando DB2 para LUW, puede hacerlo usando algunas funciones:

• DÍGITOS (): le da una cadena de caracteres sin relleno de su número entero
• TRANSLATE () - reformatea la cadena de caracteres
• MIDNIGHT_SECONDS - devuelve el número de segundos desde la medianoche por un tiempo.

Esto funcionará en casos donde el valor sea 100 = ''00: 01: 00 ''.

Ejemplo:

select
   id,
   MIDNIGHT_SECONDS(TRANSLATE(''EF:GH:IJ'',DIGITS(end_time),''ABCDEFGHIJ'')) -
      MIDNIGHT_SECONDS(TRANSLATE(''EF:GH:IJ'',DIGITS(start_time),''ABCDEFGHIJ'')) as runtime
from
   your_table;

La expresión anterior no funcionará si start_time> end_time (es decir, start_time es antes de la medianoche, pero end_time es después de la medianoche).

Por supuesto, el verdadero problema aquí es usar una INT para almacenar un TIEMPO. Sería mejor simplemente arreglar su modelo de datos para que use TIME (o mejor, un TIMESTAMP).

Answer 4

Yo sugeriría que los valores están realmente destinados a ser 08:59:58 y 09:00:01 en horas: minutos: segundos suponiendo que la diferencia final es 3

pero todas estas son suposiciones de que debes preguntarle a otros en tu compañía qué son, ya que alguien más debe haberlas usado incluso si el codificador original se hubiera ido

Answer 5

La mayoría de las respuestas ya descritas son todas válidas, pero desafortunadamente el iSeries parece cuestionar diferentes funciones, así que tuve que adaptar las respuestas dadas para que funcione.

La solución final que obtuve fue

select TIME(SUBSTR(DIGITS(END_TIME),1,2) CONCAT '':'' CONCAT SUBSTR(DIGITS(END_TIME),3,2) CONCAT '':'' CONCAT SUBSTR(DIGITS(END_TIME),5,2)) - TIME(SUBSTR(DIGITS(START_TIME),1,2) CONCAT '':'' CONCAT SUBSTR(DIGITS(START_TIME),3,2) CONCAT '':'' CONCAT SUBSTR(DIGITS(START_TIME),5,2)) from table1;

Gracias por todas las respuestas rápidas y detalladas