Solicito ayuda para definir funciones sobre estándar de fechas ISO o SQL
 

Entre las funciones de la unidad GHFRTL se encuentra una de nombre ghISODate, esta recibe un valor de tipo TDateTime y devuelve una cadena de caracteres con el formato yyyy-mm-dd (año, mes y día expresando la fecha dada). Existe una función muy parecida, de nombre ghISODateTime, que al igual que la anterior toma un valor de tipo TDateTime, pero la cadena que regresa lleva el formato yyyy-mm-dd hh:nn:ss, es decir, además de la fecha incluye la hora con minutos y segundos.

Existen otras funciones se que apoyan en las anteriores dos para convertir valores de tipo TDateTime a su expresión como cadenas de caracteres para ser añadidas en la formación de sentencias SQL: ghQuotedISODate, ghQuotedISODateTime, ghSQLValue, ghSQLValues, ghCommaSQLValues.

Obviemos el hecho de que podemos usar objetos TParam / TParameter cuando vamos a lanzar una sentencia SQL para que sea ejecutada por un servidor de base de datos. GH Freebrary es una biblioteca de propósito general, y aunque todas sus funciones tuvieron origen en necesidades prácticas de proyectos ordinarios, he intentado dar a cada una de sus partes un diseño estandarizado, consistente y flexible. Aunque existen las colecciones Params / Parameters, nunca se sabe cuándo un programador va a necesitar prescindir de éstas y convertir un valor de tipo TDateTime a una cadena de caracteres bajo un formato estándar.

El asunto es que había dado por hecho que el formato de fecha y hora ISO, incluyendo minutos y segundos, era sencillamente yyyy-mm-dd hh:nn:ss, porque hace algunos años lo leí en algún documento y al ver que Firebird (uno de los motores más respetuosos con los estándares) lo aceptaba a la primera, lo asenté así en la función ghISODateTime:

Código Delphi [-]
  Function ghISODateTime (Const Value :TDateTime) :String;
  Begin
    Result := FormatDateTime ('yyyy-mm-dd hh:nn:ss', Value);
  End;

Pero se presentan dos principales trabas:

• Algunos motores, como MS-SQL no son precisamente muy apegados a los estándares y el formato de fecha y hora anterior sencillamente les es inválido en una expresión simple de fecha y hora entrecomillada.
• Lo había visto en documentos XML y otros lugares, pero hasta hace poco desconocía que el estándar ISO 8601 señala el uso de una letra "T", y no un espacio, como separador entre las partes de fecha y hora: yyyy-mm-ddThh:nn:ss.

El hecho de que ghISODateTime no incluya la letra T, hace que funciones derivadas como ghSQLValue, ghSQLValues y ghCommaSQLValues no puedan ser utilizadas con valores de fecha y hora en algunos motores distintos de Firebird. Por otra parte, si añado la letra T, entonces Firebird marcará error por no reconocer el formato (al menos en Firebird 1.5 el separador debe ser un espacio).

Sospecho que he confundido el formato ISO de fecha y hora del enlace anterior, con el formato que tradicionalmente usan algunos motores SQL para representar el tipo TimeStamp.

Para solucionar estas discrepancias, lo primero que me gustaría saber es cuál es el formato más aceptado en los distintos motores de bases de datos, a la hora de expresar un valor de fecha y hora como cadena de caracteres.

En Firebird, '2013-04-30 14:25:00' es perfectamente válido. Pregunta para los compañeros: ¿cómo podría ser expresado a manera de cadena de caracteres y sin usar funciones ese mismo valor en una sentencia SQL de otros motores? Esto es con el fin de conocer el estándar más respetado. Una vez identificado éste, ¿es un estándar al que podríamos darle el adjetivo de "ISO"? ¿o mejor "SQL"? ¿algún otro? ¿deberían ser renombradas las funciones "ghXXXISOXXX" mencionadas anteriormente? ¿Debería añadir la "T" al formato y crear otra función exclusiva para SQL?

Luego, asumiendo que no existe un estándar que "a la primera" acepten todos lo motores de base de datos, me tienta la idea de definir una variable global como las clásicas ShortDateFormat y ShortTimeFormat de Delphi (algo como GHSQLDateTimeFormat) para no tener que pasar a toda esa serie de funciones el formato que debe emplearse cuando alguno de los valores sea de tipo TDateTime, sino que esa nueva variable lo determine.

Me interesa convertir a formato de fecha y hora con la "T" puesto que eso marca el estándar ISO y en muchos lugares resultarán útiles las funciones gh que así lo hagan, pero también me interesa convertir a una cadena de caracteres aceptable en la mayoría de motores SQL.

Solicito su ayuda para llevar a buen puerto este análisis. Espero haberme dado a entender y de antemano les extiendo mi gratitud.

Al González.

Puedo aportar poca cosa, sólo que en sqlite acepta los distintos formatos.
Aquí el enlace.

Pues basicamente, diria que tu problema describe la solucion: No la hay (universal).

Si tu función supuestamente genera un formato ISO definido, entonces lo logico es que te apegues a eso. Si firebird o lo que sea se ha desviado, es otro tema.

Cada motor, ademas, tiene su forma "especial" de tratar con las fechas (siendo el tipo de datos de los nada-estandar que hay). Esto es lo que hago yo, en base a los multiples lenguajes y motores de BD que me ha tocado manejar.

1- Me estandarice en manejar (de lo posible) solo el formato ISO de fechas. Ademas, todo lo hago, de ser posible, en localizacion USA (decimales son . por ej).

Para ello, hago uso por ejemplo en Sql Server de comandos como SET DATEFORMAT si me conecto de forma ad-hoc o de ser posible, especifico en la cadena de conexion o inicio de sesion contra la BD que en "locale" es EN-US, y que las cadenas son UTF-8

2- Intento, a lo máximo, pasar lo mas pronto posible a un tipo de datos correcto, en vez de estar pasando cadenas libres. Osea, si tengo una cadena "12 abr" en la primera "entrada" la paso a Datetime y solo la convierto en la ultima "salida".

Osea, intento que en el programa, internamente, exista solo una representacion inflexible y definida de los datos, donde el "locale" del equipo es solo algo que se toma en cuenta para conversiones y para visualizar en el mundo "externo" a ese programa, pero todo en su "interior" esta claro que es un locale fijo (en mi caso, Ingles-US).

Eso se desprende de este principio:

http://en.wikipedia.org/wiki/Robustness_principle

Tal principio indica que tus funciones no deben dar sorpresas. Deben ser "inflexibles" y dar para la misma entrada, la misma salida, a menos, que *explicitamente* se pida lo contrario - y eso solo, si tal función tiene como tarea, el de ajustar dinamicamente la salida en base al entorno o configuracion, ej: Una función que formatea cadenas como FormatDateTime-.

Me cabrea del todo si existe una función, que si le pido la fecha en formato cadena, me devuelva algo diferente en base al entorno, idioma, version, presión atmosferica, dirección del viento y fase de la luna. Me la volaria aun mas si tal función parece que sigue un estándar, pero al mirarlo a fondo, resulta que no.

Es mejor entonces, que tal función retorne algo fijo e inflexible y que el programador ajuste las cosas o haga su propia función para adaptarse al entorno o las necesidades especificas que enfrente.


Y por ultimo, es mejor mandar valores a las BD usando la función params, en vez de mandar como texto los valores y esperar que el programador sepa que es una injeccion sql y todo eso.

Muchas gracias por sus respuestas, Antonio y Mario. :)

Has aportado mucho, Antonio. Según esa tabla, SQLite, prevé el uso de ambos estilos: con espacio entre la fecha y la hora o con una letra T.

Firebird 1.5 no acepta una sentencia como:

Código SQL [-]
Select Cast ('2013-04-30T14:25:00' As TimeStamp) From RDB$Database

debido a la forma en que está expresada esa fecha (error "conversion error from string..."). En cambio no tiene problemas si tan sólo cambio la T por un espacio:

Código SQL [-]
Select Cast ('2013-04-30 14:25:00' As TimeStamp) From RDB$Database

Por lo visto SQLite admite las dos formas. Para salir de dudas respecto a Firebird, sería bueno verificar si alguna de sus más recientes versiones admite la letra "T". ;)

Por otro lado, ¿podrían por favor probar unas simples sentencias Select como las anteriores en otros motores? (cambiando TimeStamp o RDB$Database por lo que fuera necesario). Sería muy bueno encontrar cuál es el patrón común, aunque ya de inicio se sepa que no será universal.

En la versión 2.5 de Firebird sigue dando ese error. si cambio la "T" por un espacio, funciona correctamente.

Saludos

Saludos.

En Oracle usando la función to_date no me reconoce el formato con T.

Código SQL [-]
Select to_date('2013-04-30 01:25:00', 'yyyy-mm-dd HH:MI:SS') from dual

Código SQL [-]
Select to_date('2013-04-30T01:25:00', 'yyyy-mm-ddTHH:MI:SS') from dual

Código SQL [-]
Select to_date( replace('2013-04-30T01:25:00','T',' '), 'yyyy-mm-dd HH:MI:SS') from dual

El formato 1 funciona correctamente, el 2 devuelve un error "date format not recognized", el 3 funciona remplazando la letra T por un espacio ' ' quedando igual al formato 1.

Oracle docs date format.

Muchas gracias, ElKurgan y beginner01.

Por lo visto la persistencia de Firebird en usar espacio y no una T, tiene que ver con su apego al estándar SQL más que al estándar ISO. No es que se haya "desviado" o algo así.

Según se colige, si bien Oracle acepta el formato sin la T, es porque uno mismo tiene que indicárselo como segundo argumento de la función to_date. He leído que el parámetro de formato puede omitirse, pero entonces la función to_date de Oracle se basaría en algo llamado NLS_TERRITORY o NLS_DATE_FORMAT:Supongo entonces que en Oracle no hay forma de expresar una fecha y hora de manera literal, sin poner explícitamente el formato, con la completa seguridad de que todas sus versiones y configuraciones tomarán ese valor siempre de la misma manera. A diferencia de Firebird, que parece respetar bastante su formato predeterminado. ¿Qué hay de SQLite? ¿cuál es su formato predeterminado? ¿Y en MS-SQL, MySQL, PostgreSQL, Sybase, Informix...?

Los animo a hacer más pruebas con los servidores de bases de datos que tengan instalados.

Código SQL [-]
Select 'Test' From RDB$Database  -- Cualquier tabla
  Where Current_TimeStamp  -- Cualquier cosa que sea un valor TimeStamp
  > '2012-10-14 21:04:50'  -- Una expresión literal de fecha y hora

¿Podrían ayudarme a determinar si el formato que acepta Firebird (yyyy-mm-dd hh:mm:ss) podría ser un buen default para la mayoría de los más populares motores SQL?

¿Hay o no un acomodo relativamente común o que merezca cierto respeto del año, mes, día, hora, minuto y segundo al expresar una fecha y hora literalmente en los diversos motores de bases de datos?

Por lo que voy viendo, creo que podríamos llegar a tener dos funciones: ghISODateTime (con la T) y ghSQLDateTime (con espacio), pero es muy pronto para decidir. Es necesario recabar más información.

NOTA: En el primer mensaje escribí "hh:nn:ss" y más tarde "hh:mm:ss", lo primero fue por compatibilidad con la función FormatDateTime de Delphi, pero este formato del formato no tiene importancia (sabemos que la parte cambiada se refiere a minutos). El formato de un valor literal de fecha y hora es el que importa. Insisto, ¿cuál es el más aceptado o que debiera respetarse?

Siéntanse partícipes de estas historias de colaboración. No estoy pidiendo ayuda para un proyecto personal que vaya a entregar a un cliente y por el cual me vayan a pagar. Esta es sólo una pequeña traba de diseño / concepto que vale la pena resolver en nuestra biblioteca, solución que cuando menos servirá a otras personas que busquen respuesta a las mismas inquietudes.

Saludos. :)

En MS SQL Server funciona de las dos maneras, con T o espacio.

Código SQL [-]
Select Cast ('2013-04-30T14:25:00' As datetime)

Código SQL [-]
Select Cast ('2013-04-30 14:25:00' As datetime)

Salida

Que bien, ¿y sin usar Cast? Digamos, como en la última sentencia que puse (la del Where).

Funciona perfectamente.

Código SQL [-]
Select 'Test'  
  Where GETDATE()  -- En SQL Server se usa esta función para obtener la hora actual.
  > '2012-10-14 21:04:50'

Gracias beginner01, juraría que no me aceptó algo así hace tiempo cuando tuve oportunidad de manejar MS-SQL.

Me salta la duda de si no estará convirtiendo lo que devuelve GETDATE() a cadena. Es decir, ¿realmente está haciendo una comparación de fechas o más bien es una comparación de cadenas de caracteres?

Probado en firebird 2.5
Resultado correcto

Saludos

Después de algunas horas de búsqueda y lectura, pongo una serie de enlaces Web que me parecen relevantes para todos aquellos que se interesen en el tema de los formatos usados en expresiones literales de fecha y hora dentro del lenguaje SQL. Algunos de estos enlaces ya aparecen en mensajes anteriores, no llevan un orden específico.

http://docs.oracle.com/cd/B19306_01/...nctions183.htm

http://www.sqlite.org/lang_datefunc.html

http://msdn.microsoft.com/es-es/library/ms189491.aspx

http://en.wikipedia.org/wiki/ISO_860...epresentations

http://www.ibphoenix.com/resources/d...design/doc_169

http://publib.boulder.ibm.com/infoce...c/esqlc126.htm

http://msdn.microsoft.com/en-us/library/ms187819.aspx

http://msdn.microsoft.com/en-us/libr...#ISO8601Format

http://www.sql.org/sql-database/post...-datetime.html

http://publib.boulder.ibm.com/infoce...2Fr0008474.htm

http://www.firebirdsql.org/en/firebird-date-literals/

http://wiki.navicat.com/wiki/index.p...TIME_format%3F

Estaría bien que algunos de los compañeros se tomaran un poco de tiempo para leerlos (parcial o totalmente) y contrastar opiniones. De mi parte tengo ya algunas ideas que podrían ser concluyentes, pero ahora mismo debo ausentarme. Espero haya más retroalimentación.

Ah, si alguien tuviera la suerte de encontrar el estándar "oficial" SQL que hable sobre los formatos de cadenas literales de fecha y hora, estaría excelente. :)

Saludos.

Para comprobarlo ya que no aparece o no encontré suficiente documentación sobre esto realicé una prueba para ver el que ocurre
y al hacer algo como esto.

Código SQL [-]
Select 'Test'
  Where GETDATE()  
   > '2012-10-14T25:04:50'

Colocando la hora en un formato incorrecto (14T25:04:50) devuelve este error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." con lo cual me queda claro que SQL Server convierte la fecha literal a datetime para luego hacer la comparación.

Las pruebas fueron realizadas en SQL Server 2000 y 2008.

Muchas gracias, beginner01.

¿Renombramos la función ghISODateTime por ghSQLDateTime? :)

Sip

Un saludo

Bien, pero entonces creo que mejor sería contar con funciones para ambos tipos de formato:

1. ISO, con la famosa "T" entre la fecha y la hora, e invariable.
2. SQL, sin la "T", pero "configurable" mediante variable global, dado el frágil consenso que hay entre los distintos fabricantes.

Y aquellas otras funciones relacionadas con SQL que necesiten convertir una fecha y hora a expresión literal usarían la opción #2. El programador, entonces, dependiendo del motor que use y la configuración de éste, ajustaría la variable global de formato según se requiera. Aunque, de entrada, el valor predeterminado de dicha variable sería 'yyyy-mm-dd hh:nn:ss' ("nn" es para minutos en Delphi), por ser el formato que todos los motores "deberían" aceptar incondicionalmente.

Veo que se respondió muy poco a las preguntas que he hecho a lo largo del hilo. ¿Tan aburrida es esta sección? ;)

Lo que implica que tal vez no este usando el estándar ISO.

Lo que estas pensando no tiene sentido. O mejor dicho, es una aberración (y demuestra porque nombrar cosas es una de las dos cosas mas dificiles).

Dices que quieres tener una funcion que devuelve la fecha en estandar ISO.

Eso solo tiene un sentido: Devuelve una cadena en estándar ISO y eso es todo.

Luego, resulta que por la razon que sea, X sistema tiene algo que se parece al estándar ISO. Entonces deberías mandarle la cadena que SI ES el estándar ISO? NO! Porque no lo es.

Y deberías "configurar" la función para que devuelva algo parecido al ISO? NO. Porque la función dice que se llama "devolver cadena estándar ISO". Es algo muy preciso.

-- Eso lo tienes claro. Solo recalco.

Y tener una función que formatea de forma diferente de acuerdo a como lo diga el programador, se llama "Format Date". Que tiene un carajo que ver con que sea el ISO.

Asi que al punto al que voy es que los nombres deben ser precisos en su intención. "FormatDateAsISO" es un nombre preciso. "FormatDate(FormatString:String)" tambien (denota que es ambiguo y se configura con FormatString). "FormatDateAsFirebird" es algo preciso.

La patada es hacer algo como "FormatDateAsSql" -esta es la aberracion a la que hablo- porque resulta que eso depende de cada motor, pero da la idea que me formatea correctamente como "sql" lo cual, en si, es una suposición *erronea*. Entonces para corregirla tocaria hacer algo como "FormatDateAsSql(Engine:TDBEngine)" y no se si quieres ponerte a hacer la implementacion pa cada motor.

Pero es aun peor. Resulta que muchos programadores (que de por si, al mandar fechas como cadenas ya es un error) mandan la fecha de acuerdo al "locale" de sistema operativo (que puede diferir del de la BD)... son como 3 "locales" diferentes en juego: Lo que dice tu funcion, lo que dice el OS, lo que dice la BD (ah, y lo que cree el programador, que no ha pensado en esto).

Lo correcto, como creador de libreria, es dar solo funciones *precisas* y funciones configurables -pero desligadas, osea, agnosticas-, pero cuidarse de interpretar las intenciones y entornos donde operan. Si el programador X maneja un motor Y, en un "locale" Z y necesita una cadena formateada, es EL el que debe crear "FormatDateForDBX", no la libreria (a menos que, sea la libreria "DBXLibrary", osea, sea para ese motor en especifico - si es que el engine tiene un formato "fijo" independiente del locale).

Y usar una vble global es un error (las vbles globales son en el 99.99% de los casos un error de diseño).

Agradezco la buena intención, Mario. Vamos por partes para tratar de sintonizarnos. :)

"Tal vez" no, ¡con toda seguridad! Ha quedado claro que una cosa es el estándar ISO para fechas y horas y otra el presunto estándar SQL para fechas y horas (sería bueno encontrar la prueba definitiva de su existencia). Los cuales, hace algunos años, asumí de forma errónea que se trataban de la misma cosa, y por eso nombré a esas funciones con la palabra "ISO", empleándolas luego en asuntos de SQL.

Y que lo digas, ese Phil Karlton sabía por lo que hemos pasado algunos "quisquillosos" (horas consultando páginas de código, bibliotecas nativas y de terceros, ayuda de Delphi, diccionarios, gramática del inglés...todo para saber qué nombre darle a una simple clase, método o función).

Claro, eso no tiene vuelta de hoja, es así de simple y no representa ningún problema. Y las nuevas funciones ghISOXXX ya no serían utilizadas para aspectos de SQL, sino las funciones que lleven la palabra "SQL" en el nombre. Éstas son las que particularmente requieren atención ahora, dado el descubrimiento "Fecha ISO <> fecha SQL".

Si el último enunciado fue una pregunta, la respuesta es: nada. Como dije antes, lo de ISO queda apartado.

La función que refieres, la nativa FormatDateTime de Delphi, toma como primer argumento el formato que se quiere utilizar. Pero otras funciones nativas, como DateToStr, TimeToStr y DateTimeToStr (las versiones que reciben un sólo parámetro) no. Todas estas hacen la conversión con base en el formato que señalan un grupo de variables globales. Es algo de lo más normal y a nadie le hace poner el grito en el cielo, en virtud de que dicho formato es relativamente estable bajo su contexto (en nuestro caso sería el contexto del lenguaje SQL), pero siempre está abierto a que la aplicación lo establezca. Tú mismo has dicho que usas el comando Set DateFormat de MS-SQL.

De acuerdo, pero sin caer en la trampa de la precisión papista (DeleteASpecifiedFileFromTheContainerDrive para una función que se entiende sencillamente como DeleteFile).

No estoy de acuerdo. Según las pruebas y los enlaces que están ante ti, parecen ser varios los motores que se adhieren al misterioso estándar, si bien son pocos los casos que hemos comprobado hasta el momento.

Vamos Mario, arrima el hombro más allá de la crítica (ésta no dejes de soltarla porque la considero importante). Casimiro, ElKurgan y beginner01 se tomaron la molestia de hacer pruebas e indagar. He leído que trabajas con muy diversas tecnologías, por lo que es probable que tengas acceso inmediato a SGBDs no evaluados aún. Si lees con detenimiento, comprenderás que hay indicios sobre la existencia de ese estándar SQL para expresión literal de fechas y horas. Y si es un estándar, ¿por qué no habría de ser el valor predeterminado (default) de la variable global de formato? A fin de cuentas, sólo tendrían que ajustar esta variable aquellos que usen gestores SQL "renegados" (y mira que MS-SQL, por lo que se ve en los enlaces y en las pruebas, ha terminado admitiendo el formato sin la T).

Esa manía de lanzar aventuradas generalizaciones...:p Recuerda que hablamos de una biblioteca de propósito general. No voy a suprimir la existencia de una función sólo porque alguien podría darle un mal uso (¿que se dejen de fabricar cuchillos para evitar los apuñalamientos?). Como programadores bibliotecarios, no podemos conocer el total de las situaciones donde una rutina o clase podría ser útil, porque eso está en el infinito. Pero sí podemos, y debemos, abrirnos a la experiencia, al estudio de toda clase de temas y al entendimiento de las necesidades de nuestros usuarios (con más ánimo cuando éstos tienen la paciencia de explicarlas a detalle); y entonces considerar escenarios en los que nuestras pequeñas creaciones significarán la solución a algo.

Nunca sabes quién estará, por ejemplo, creando una herramienta de administración de bases de datos con capacidad para formar sentencias SQL puras, quizá de mantenimiento, a partir de palabras y valores introducidos en cuadros de texto. Ninguna inyección (no "injeccion") SQL por la que tengamos que asustarnos.

Creí que ya se había extinto esa corriente de pensamiento que, más por berrinche que por otra cosa, estigmatiza el uso de variables globales. Me hiciste recordar este viejo hilo (perdonar mis resabios de hace nueve años). Probablemente, para quienes se obstinan en ello, las decenas (quizá cientos) de variables globales nativas de Delphi son el otro 0.01%. :rolleyes:

¿Qué es una variable global? Es un campo de una clase anónima cuyo contexto es la aplicación entera.

Saludos.

No había visto ese hilo, es corto, conciso y claro: las variables globales se usan cuando hacen falta.
En cierto trabajo que estuve hace un tiempo presumían de no usar variables globales, era el gran secreto del programador jefe de allí, "bien, y entonces cómo hacéis?, pregunté, y la respuesta: usamos un record accesible a todos los módulos. ¿Y eso no es lo mismo que una variable global? :confused:
Perdón por desvirtuar un poco.