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

[[Al González]]

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.

[Casimiro Notevi]

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

Time Strings

A time string can be in any of the following formats:

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

Aquí el enlace.

[mamcx]

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

Be conservative in what you send, be liberal in what you accept

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.

[[Al González]]

Muchas gracias por sus respuestas, Antonio y Mario.

Empezado por Casimiro Notevi

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

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.

[[ElKurgan]]

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

Saludos

[beginner01]

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.

[[Al González]]

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:

If you omit fmt, then char must be in the default date format [...] The default date format is determined implicitly by the NLS_TERRITORY initialization parameter or can be set explicitly by the NLS_DATE_FORMAT parameter.

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.