Acerca de mi base de datos fecha tipo de cruzada: ¿Válido? ¿Vale la pena? ¿Alguien más lo siente?

13

Pasé mucho tiempo respondiendo preguntas sobre SQL en SO. Con frecuencia me encuentro con consultas de este tipo:

SELECT * FROM person WHERE birthdate BETWEEN '01/01/2017' AND '01/03/2017'

SELECT * FROM person WHERE birthdate BETWEEN '2017-01-01' AND '2017-03-01'

SELECT * FROM person WHERE birthdate BETWEEN 'some string' AND 'other string'

es decir, ya sea confiando en una conversión implícita de cadena a fecha (mala), de los parámetros dados o confiando en la base de datos convirtiendo x millones de valores de fila de la base de datos en cadena y haciendo una cadena de comparación (peor)

Ocasionalmente, hago un comentario, especialmente si es un usuario de alta reputación el que escribe una respuesta inteligente, pero creo que a los usuarios que realmente los deberían escribir con menos tipos de datos de forma descuidada / estricta

El comentario usualmente toma la forma de que probablemente sería mejor si convirtieran explícitamente sus cadenas en fechas, usando to_date (Oracle), str_to_date (MySQL), convert (SQLSERVER) o algún mecanismo similar:

    --oracle
    SELECT * FROM person WHERE birthdate BETWEEN TO_DATE('20170101', 'YYYYMMDD') AND TO_DATE('20170301', 'YYYYMMDD')

    --mysql
    SELECT * FROM person WHERE birthdate BETWEEN STR_TO_DATE('20170101', '%Y%m%d') AND STR_TO_DATE('20170301', '%Y%m%d')

    --SQLS, ugh; magic numbers
    SELECT * FROM person WHERE birthdate BETWEEN CONVERT(datetime, '20170101', 112) AND CONVERT(datetime, '20170301', 112)

Mi justificación técnica para hacerlo es que es explícita en cuanto al formato de la fecha, y garantiza que los pocos parámetros de origen se conviertan definitivamente en el tipo de datos de la columna de destino. Esto evita cualquier posibilidad de que la base de datos obtenga un error de conversión implícita (el argumento del 3 de enero / 1 de enero del primer ejemplo) y evita que la db decida convertir un millón de valores de fecha en la tabla a cadenas (usando alguna fecha específica del servidor formato que puede que ni siquiera coincida con el formato de la fecha en los parámetros de cadena dentro del sql) para hacer la comparación - abundan los horrores

Mi justificación social / académica para hacerlo es que SO es un sitio de aprendizaje; Las personas en él adquieren conocimiento ya sea implícita o explícitamente. Para golpear a un novato con esta consulta como respuesta:

SELECT * FROM person WHERE birthdate BETWEEN '2017-01-01' AND '2017-03-01'

Puede llevarlos a pensar que esto es sensato, ajustando la fecha según el formato que prefieran:

SELECT * FROM person WHERE birthdate BETWEEN '01/01/2017' AND '01/03/2017'

Si al menos vieron algún intento explícito de convertir la fecha, podrían comenzar a hacerlo para su formato de fecha extraña, y matar algunos errores de siempre antes de que surjan. Después de todo, intentamos disuadir a las personas para que no se adhieran al hábito de inyección SQL (¿y alguien recomendaría parametrizar una consulta y luego declarar al controlador que @pBirthdate es una cadena, cuando la interfaz tiene un tipo de fecha y hora?)

Volviendo a lo que sucede después de hacer mi recomendación: normalmente recibo un rechazo a la recomendación "sé explícito, usa x", como "todos los demás lo hacen", "siempre me funciona", "muéstrame un poco de manual o documento de referencia que diga que debería ser explícito "o incluso" ¿qué? "

En respuesta a algunos de estos, he preguntado si buscarían en una columna int haciendo que WHERE age = '99' pasara la edad como una cadena. "No seas tonto, no necesitamos poner 'al buscar int" viene la respuesta, por lo que en algún lugar hay cierta apreciación de los diferentes tipos de datos en su mente, pero tal vez no haya conexión con el salto lógico que busca un int. Columna pasando una cadena (aparentemente tonta) y buscando una columna de fecha pasando una cadena (aparentemente sensible) es hipocresía

Entonces, en nuestros SQL tenemos una forma de escribir cosas como números (usar números, sin delimitadores), como cadenas de caracteres (usar cualquier cosa entre delimitadores de apóstrofes). ¿Por qué no hay delimitadores para las fechas? ¿Es un tipo de datos tan fundamental en la mayoría de DB? Podría resolverse todo esto simplemente teniendo una forma de escribir una fecha de la misma manera que javascript nos permite especificar una expresión regular al poner / en cualquiera de los lados de algunos caracteres. %código%. ¿Por qué no tener algo para citas?

En realidad, a mi entender, (solo) Microsoft Access en realidad tiene símbolos que indican "se ha escrito una fecha entre estos delimitadores", por lo que podemos obtener un buen atajo como /Hello\s+world/ pero la presentación de la fecha todavía puede dar problemas por ejemplo, mm / di vs dd / mm, ya que MS siempre ha jugado rápido y con las cosas que la gente de VB pensó que era una buena idea

Volviendo al punto principal: estoy argumentando que es sabio ser explícito con este medio que nos obliga a pasar una multitud de tipos de datos diferentes como cadenas ..

¿Es una afirmación válida?

¿Debo continuar esta cruzada? ¿Es un punto válido que la escritura estricta es un moderno no-no? ¿O cada RDBMS (incluidas las versiones antiguas), cuando se realice una consulta WHERE datecolumn = #somedate# sin duda convertirá correctamente la cadena en una fecha y hará la búsqueda sin convertir los datos de la tabla / perdiendo el uso de los índices? Sospecho que no, al menos a partir de la experiencia personal de Oracle 9. Sospecho también que puede haber algunos escenarios de escaparse si las cadenas siempre se escriben en algún formato estándar ISO, y la columna es un tipo de fecha, entonces parámetro de cadena siempre se convertirá correctamente implícitamente. ¿Esto lo hace correcto?

¿Es una tarea que vale la pena?

Mucha gente no parece entenderlo, o no le importa, o muestra algo de hipocresía porque sus intenciones son intencionadas, pero sus fechas son cadenas. Sin embargo, lo más común a la mayoría es que pocas personas se han dado vuelta y han dicho: "Sabes qué, estoy de acuerdo con tu punto. Seré explícito sobre mis fechas de ahora en adelante".

    
pregunta Caius Jard 06.09.2017 - 07:58

4 respuestas

7

Usted escribió:

  

son esos parámetros del 1 de enero al 3 de enero o del 1 de marzo ..

De hecho, es una fuente potencial de errores. Señalar esto a un autor de la pregunta puede ser útil para otros lectores, así que sí, esta es una preocupación válida. Sin embargo, para ser constructivo, lo haría

  • refiérase a ANSI SQL y use los literales DATE o DATETIME de ese estándar

  • use el formato de fecha y hora no ambiguo habitual de un DBMS específico (y mencione qué dialecto de SQL se usa)

Desafortunadamente, no todos los DBMS admiten los literales de fecha ANSI SQL exactamente de la misma manera (si es que lo admiten), por lo que esto generalmente conducirá a una variante del segundo enfoque. El hecho de que "el estándar" no esté implementado rígidamente por diferentes proveedores de bases de datos es probablemente parte del problema aquí.

Además, para muchos sistemas del mundo real, las personas pueden confiar en una ubicación específica y fija en el servidor de la base de datos, incluso si las aplicaciones cliente están localizadas, porque hay un solo tipo de servidor, siempre configurado de la misma manera . Por lo tanto, a menudo se puede suponer que '01 / 03/2017 'tiene el formato fijo' dd / mm / aaaa ', o' mm / dd / aaaa 'para cualquier SQL utilizado en el sistema específico con el que están trabajando. Entonces, si alguien te dice "siempre me funciona", esta podría ser una respuesta sensata para su entorno . Si este es el caso, hace que valga la pena discutir este tema.

Hablando de "razones de rendimiento": siempre que no haya problemas de rendimiento medibles, es bastante supersticioso discutir con "problemas de rendimiento potenciales". Si una base de datos realiza un millón de conversiones de cadena a fecha o no, probablemente no importa cuándo la diferencia de tiempo es solo 1/1000 de segundo, y el verdadero cuello de botella es la red que hace que la consulta dure 10 segundos. Por lo tanto, es mejor dejar de lado estas preocupaciones siempre que alguien pregunte explícitamente por consideraciones de desempeño.

  

¿Debo continuar esta cruzada?

Te digo un secreto: odio las guerras religiosas. No llevan a nada útil. Por lo tanto, si las especificaciones de fecha / hora ambiguas en SQL pueden dar lugar a problemas, mencionarlos, pero no intente forzar a las personas a ser más rígidas si realmente no les brinda ningún beneficio en su contexto actual.

    
respondido por el Doc Brown 06.09.2017 - 08:51
5

Tu cruzada no resuelve el problema.

Hay dos problemas separados:

  • conversión de tipo implícita en SQL

  • formatos de fecha ambiguos como 05/06/07

Veo de dónde viene con su cruzada, pero no creo que la conversión explícita realmente resuelva el problema en cuestión:

  • La conversión implícita todavía ocurre en caso de una falta de coincidencia entre los tipos en una comparación. Si una cadena se compara con una fecha, SQL intentará convertir la cadena en una fecha primero. Por lo tanto, comparar una columna de tipo fecha con un valor de fecha convertido explícitamente es exactamente lo mismo que comparar una fecha en formato de cadena. La única diferencia que veo es si comparas un valor de fecha con una columna que no contiene realmente fechas sino cadenas, pero esto sería un error en cualquier caso.

  • El uso de la conversión explícita no resuelve la ambigüedad en formatos de fecha que no son ISO.

La única solución que veo:

  • no compare columnas de tipo cadena con valores que no sean de cadena.
  • solo utiliza los formatos de fecha tipo ISO.

Y, por supuesto, nunca almacene fechas en una columna de tipo cadena. Pero una vez más, la conversión explícita de literales de fecha no evitará esto.

Podría decirse que las conversiones implícitas fueron un error en SQL, pero dado cómo está diseñado el lenguaje, no veo el beneficio de la conversión explícita. De todos modos, no evitará la conversión implícita, y solo hace que el código sea más difícil de leer y escribir.

    
respondido por el JacquesB 06.09.2017 - 11:40
3

En primer lugar, tienes un punto. Las fechas no se deben poner en cadenas. Los motores de base de datos son bestias complejas en las que nunca estás 100% seguro de qué sucederá exactamente bajo el capó si se realiza una consulta arbitraria. La conversión a fechas hace que las cosas sean inequívocas y puede aumentar el rendimiento.

PERO

No es un problema que valga la pena el esfuerzo adicional de reflexión para resolver para la mayoría de las personas. Si fuera fácil usar literales de fecha en una consulta, sería fácil defender su posición. Pero no lo es. La mayoría de las veces uso SQL Server, así que tratar de recordar ese desastre para convertir una fecha simplemente no está sucediendo.

Para la mayoría de las personas, el aumento de rendimiento es insignificante. "¿Por qué, sí, señor jefe? Pasé 10 minutos adicionales corrigiendo este simple error (tuve que buscar en Google cómo convertir las fechas porque esa sintaxis es ... especial ...). Pero ahorré 0.00001 segundos más. una consulta raramente ejecutada ". Eso no va a volar en la mayoría de los lugares en los que he trabajado.

Pero elimina la ambigüedad en los formatos de fecha que dices. Nuevamente, para muchas aplicaciones (aplicaciones internas de la compañía, asuntos del gobierno local, etc., etc.) no es realmente una preocupación. Y para aquellas aplicaciones en las que es una preocupación (aplicaciones grandes, internacionales o empresariales), o bien se convierte en una preocupación de UI / capa de negocios o aquellas compañías ya tienen un equipo de DBA bien versados que ya lo saben. TL / DR: si la internacionalización es una preocupación, alguien ya lo está pensando y ya ha hecho lo que usted sugiere (o ha mitigado el problema).

¿Y ahora qué?

Si te sientes tan inclinado, sigue luchando la buena batalla. Pero no se sorprenda si la mayoría de las personas no siente que esto es lo suficientemente importante como para preocuparse. El hecho de que haya situaciones en las que sea importante, no significa que esa sea la situación de todos (y probablemente no lo sea). Así que no te sorprendas cuando recibas un empujón por algo que es técnicamente correcto y mejor, pero no realmente relevante.

    
respondido por el Becuzz 06.09.2017 - 15:00
2
  

Estoy argumentando que es sabio ser explícito con este medio que nos obliga a pasar una multitud de tipos de datos diferentes como cadenas.

Suponiendo que las "fechas" se están transmitiendo "en" Cadenas , entonces sí; Estoy absolutamente de acuerdo en que usted tiene razón al hacer esto.

Cuando es "01/04/07"?
* 4 de enero?
* 1 de abril?
* 7 de abril [2001]?

Cualquiera o todos estos podrían ser correctos, dependiendo de cómo "la computadora" elija interpretarlos.

Si tienes para construir SQL dinámico con literales en ellos, entonces el formato de la fecha debe estar bien definido y, preferiblemente, independiente de la máquina (tuve uno extraño en un servidor Windows donde el procesamiento basado en la fecha dentro de un Servicio de Windows salió mal porque un operador inició sesión en la consola con diferentes preferencias de formato de fecha!). Personalmente, uso exclusivamente [d] el formato "aaaa-mm-dd".

Sin embargo ...

La solución mejor es utilizar consultas parametrizadas que obligan a convertir el tipo de datos antes . SQL se involucra, obteniendo un valor de "fecha" en una fecha El parámetro fuerza la conversión de tipo desde el principio (por lo que se trata de un problema de codificación, no de SQL).

    
respondido por el Phill W. 06.09.2017 - 11:37

Lea otras preguntas en las etiquetas