SQL, CSV, SQLITE3. Problema con consulta de fechas.

SQL, CSV, SQLITE3. Problema con consulta de fechas. - Versión para impresión

+- Comunidad Gambas-es (https://gambas-es.org)
+-- Foro: Gambas (https://gambas-es.org/forum-3.html)
+--- Foro: Bases de Datos (https://gambas-es.org/forum-6.html)
+--- Tema: SQL, CSV, SQLITE3. Problema con consulta de fechas. (/thread-615.html)

SQL, CSV, SQLITE3. Problema con consulta de fechas. - Shell - 18-09-2021

Buenas!.

Tengo un archivo de texto que tiene tropecientas mil líneas con este formato:

Cita:02/29/16|901120|TheZone/files/_2016/Break (1986)(Martin Aberle)[aka Demolition] [not in tosec].adf

Fijaros en la fecha. Es MM/DD/YY ó M/D/Y.

Según tengo entendido Sqlite3 o SQL trabaja con el formato. Y/M/D
En principio usé la aplicación DB Browser for SQLITE e importe el archivo como CSV y le dije que el separador era "|"
Las fechas se encuentran desordenadas.

La estructura por defecto de la base de datos era:
field1 - campo de texto ( Donde va las fechas)
field2 - campo tipo entero ( Donde va el tamaño del archivo)
field3 - campo de texto ( Donde va el nombre del archivo ).

Buscando, encontré que si quiero mostrar solamente la columna de las fechas podía hacer esto:
Intenté hacer la consulta de esta forma para obtener solo las fechas de la base de datos.

Código:
select strftime(fecha,'%Y-%m-%d') from JuegosAmiga;

El resultado fue una lista nula.

Así que intenté primero cambiar del archivo de texto cada línea el formato de fecha.
De M/D/Y a Y/M/D.

Como me defiendo más con Gambas y no quería complicarme con Awk o Sed lo hice de esta forma:
( La codificación del texto es en latin1, así que cambié cada línea a utf-8)

Código:
Public Sub Form_Open()

  Dim contenidotexto As String

  Dim contenidomodificado As String

  Dim acontenido As String[]

  contenidotexto = File.Load(User.Home &/ "Descargas/filelist.txt")

  acontenido = Split(contenidotexto, gb.NewLine, "", True)

  contenidomodificado = CambiarFechaDeLineas(acontenido)

  File.Save(User.Home &/ "Descargas/filelist3.txt", contenidomodificado)

End

Public Function CambiarFechaDeLineas(contenido As String[]) As String

  ' Modifica el comienzo de la línea, sustituyendo el formato de la fecha

  Dim linea As String

  Dim otralinea As String

  Dim cambiofecha As String

  Dim salida As String

  Dim datos As String[]

  Dim unafecha As Date

  For Each linea In contenido

    datos = Split(Conv(linea, "latin1", "utf-8"), "|")

    unafecha = CDate(datos[0])

    otralinea = Format(unafecha, "yy/mm/dd")

    cambiofecha = Replace(linea, datos[0], otralinea)

    salida &= cambiofecha & gb.NewLine

    datos.Clear

  Next

  Return salida

End

Y obtengo una salida, ejemplo de línea de esta forma.

Cita:02/29/16|901120|TheZone/files/_2016/Break (1986)(Martin Aberle)[aka Demolition] [not in tosec].adf
a
16/02/29|901120|TheZone/files/_2016/Break (1986)(Martin Aberle)[aka Demolition] [not in tosec].adf

Esta vez utilicé la consola de sqlite3. Y usé estas instrucciones. ( La cosa es aprender sql ).

Cita:sqlite> CREATE TABLE JuegosAmiga(
   ...> fecha INTEGER NOT NULL,
   ...> size INTEGER NOT NULL,
   ...> nombre TEXT NOT NULL
   ...> );
sqlite> .mode csv
sqlite> .separator |
sqlite> .import filelist3.txt JuegosAmiga
sqlite> .save mibase.sqlite3
sqlite> .exit

E intente volver a ejecutar la sentencia sql que os comenté al principio. La salida volvió a ser la misma. Nula en todos los registros.

[Imagen: ZP8o2BZ.png]

¿ Cuál es el problema de las fechas ?. He invertido la instrucción pero tampoco es la solución.
Otra es que salga como titulo de la cabecera de la columna, la función, que cosa más rara.

Os muestro el enlace que estoy siguiendo. Primero una en español que no me solucionó el problema. Que parece muy completa.

Función strftime para dar formatos a fechas y horas

La taba se crea las fechas como texto.

Y ahora la de inglés.

SQLite Strftime() Function

Espero que el problema no sea la conversión del archivo.

Saludos

RE: SQL, CSV, SQLITE3. Problema con consulta de fechas. - Shordi - 18-09-2021

El problema es que eso que le metes en la primera columna no es una fecha. Luego intentas usar una función que convierte fechas a cadenas... lo cual no funciona porque no le proporcionas fecha alguna.

Cita:¿ Cuál es el problema de las fechas ?. He invertido la instrucción pero tampoco es la solución.
Otra es que salga como titulo de la cabecera de la columna, la función, que cosa más rara.

Sqlite no tiene campos tipo fecha. Para él eso no existe. Lo que tiene es una batería de funciones que devuelven cadenas reconvertidas al formato deseado. Para que un campo se considere fecha por strftime (y el resto de funciones de sqlite) debe ser (dejemos de lado el calendario Gregoriano y el tipo real):

o un campo de texto con éste formato: YYYY-MM-DD HH:MM:SS.SSS (formato ISO 8601)

o un número entero que serán los segundos transcurridos desde 1970-01-01 00:00:00 llamado "Tiempo Unix"

Si tu campo de texto no cumple el formato ISO no es considerado una fecha por Sqlite.

Si la base de datos vas a tratarla desde gambas, mejor procesa los datos en gambas creando un campo Date y rellenándolo con la primera columna del csv debidamente tratado.

A la segunda cuestión:

Sqlite no garantiza el nombre del campo (es decir puede ser el literal de la sentencia sql o la dependencia tabla.campo o lo que se le ocurra) a menos que añadas as 'literal' como nombre de campo. Es lo que hay.

Saludos

RE: SQL, CSV, SQLITE3. Problema con consulta de fechas. - Shell - 19-09-2021

(18-09-2021, 18:01)Shordi escribió: Si la base de datos vas a tratarla desde gambas, mejor procesa los datos en gambas creando un campo Date y rellenándolo con la primera columna del csv debidamente tratado.

No es realmente que me interese crear una base de datos del archivo ya que mi interés del archivo es algo casual.
Prácticamente del archivo si quiero buscar algo al igual que un nombre, puedo usar la consola de Linux y usar los comandos
adecuados.

La cosa es que como lo vi en parte parecido a un archivo csv pensé que iba a funcionar de una manera más fácil
la conversión a una base sqlite3. Por ejemplo para ordenar las fechas de los registros y presentarlo en una consulta.
Pero ya veo que no funciona tan fácilmente.

(18-09-2021, 18:01)Shordi escribió: o un campo de texto con éste formato: YYYY-MM-DD HH:MM:SS.SSS (formato ISO 8601)

Vi que a la hora de introducir fechas usaban hasta la hora. No pensé que la hora fuera algo obligatorio.
Una cosa es la fecha completa como debe ser ingresada y otra es como la queramos presentar (sin hora) con funciones de
cadena del propio sqlite,que fue mi error.

Desde la aplicación "Calc" de LibreOffice permite la conversión de un archivo csv.
Ese campo que parece una fecha puede ser interpretado como fecha y decirle el formato que tiene.

[Imagen: nG30lkq.png]

En este caso como el archivo es muy grande supera el máximo de líneas que permite Calc. Que permite un máximo de 1048576 filas,
que ya son filas.

En el código de gambas puedo modificar el formato de salida de fecha y hacerlo más parecido al formato que comentas.
La hora es indiferente.

Código:
...

otralinea = Format(unafecha, "yyyy-mm-dd") & " 00:00:00"

...

Gracias por tu explicación maestro de las bases de datos. Wink

Saludos