Issue in converting datetime columns

[cv-or]

Hi,

First, congrats for your work, pretty awesome repo! I have an issue regarding datetime columns.

Within the PostgreSQL dump file, the datetime columns are defined as "timestamp with time zone":

The section of the dump file that fills the table seems correct:

However, the resulting SQLite DB is filled with INT values, corresponding to the timestamp values,

while I was expecting a datetime conversion to give this result instead:

What I'm doing wrong?

Thanks!

[caiiiycuk]

Hi. According to offical sqlite3 documentation:

2.2. Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

It seems that by sqlite3 driver uses Unit Time for DATE/TIME/TIMESTAMP. I do not do any convertation, I just set Date into prepared statement.

Moreover, personally I think storing datetime as integer is more preferabble as ISO8601 string. Anyway you can change format with sql query using builtin functions as said in docs. Is it works for you?

[cv-or]

Hi Alexander,

thanks a lot for the quick answer. Yes, indeed in our applications we use the TEXT ISO8601 approach. Do you think there is a way to enforce this specific approach during the conversion process, instead of using Unix Time INTEGER?

For example, we also do some conversions from MySQL to SQLite using this code and in that case the resulting SQLlite DB uses dates stored as TEXT.

[caiiiycuk]

This can be changed with properties of sqlite-jdbc driver

https://github.com/xerial/sqlite-jdbc/blob/baf12955eed2b144f14d3d38bc163d94969d539b/src/test/java/org/sqlite/QueryTest.java#L133-L135

[caiiiycuk]

this properties need to be added here https://github.com/caiiiycuk/postgresql-to-sqlite/blob/fe52bb387f7996029bfc1062d1d7332e11b4cf5a/src/main/scala/com/github/caiiiycuk/pg2sqlite/Connection.scala#L23

[LeJeko]

Hi Alexander,

Thank you very much for the detailed information you provided.

I now have a better understanding of how the conversion of datetime columns from PostgreSQL to SQLite works. However, in our application, we are accustomed to using the TEXT ISO8601 approach for storing dates, as it aligns better with our requirements.

I was wondering if it would be possible to adjust your application to allow for this option during the conversion process, rather than using the default INTEGER Unix Time format. Would it be feasible to add this functionality to your application? It would be really helpful for us, and I believe for other users who have similar preferences for date storage.

Again, thank you for your work on this application. I appreciate your attention to this matter.

P.S. I must admit, I'm not familiar with sqlite-jdbc, and I lack the Java knowledge to adapt it myself. Moreover, as I primarily work with Django, I'm not well-versed in Java. Your guidance on this matter would be greatly appreciated.

[caiiiycuk]

This should be easy to do, we need just add command line argument to change format,

    Properties properties = new Properties();
    properties.setProperty(SQLiteConfig.Pragma.DATE_CLASS.pragmaName, "text");
    Connection conn = DriverManager.getConnection("jdbc:sqlite:", properties);

Anyaway need to find time to do this. Probably can look into it on next week

[LeJeko]

Thank you for your quick reply!

I've clone your repo and made these changes to Connection.scala:

import java.util.Properties
import org.sqlite.SQLiteConfig
...
def sqlite(dbFile: File): Connection = {
    val connectionHolder = new ConnectionHolder {
      override def makeConnection: java.sql.Connection = {
        val properties = new Properties()
        properties.setProperty(SQLiteConfig.Pragma.DATE_CLASS.pragmaName, "TEXT")
        implicit val connection = DriverManager.getConnection(s"jdbc:sqlite:$dbFile", properties)
        connection.setAutoCommit(true)
        sqlitePragmas()
        connection.setAutoCommit(false)
        connection
      }
      override def db = dbFile.toString
    }
    new Connection(connectionHolder)
  }
...

I'm able to compile with sbt one-jar but I have this error when running the command:

$ java -jar postgresql-to-sqlite_2.11-1.0.3-one-jar.jar -d 2024.05.29-dump-labmgr.sql -o converted_db.sqlite3 -f true
15:56:52.854 [main] WARN  c.github.caiiiycuk.pg2sqlite.Config$ - You should set SQLITE_TMPDIR environment variable to control where sqlite stores temp files
15:56:52.858 [main] INFO  com.github.caiiiycuk.pg2sqlite.Boot$ - '2024.05.29-dump-labmgr.sql' (0 Mb) -> 'converted_db.sqlite3'
15:56:52.858 [main] INFO  c.g.c.pg2sqlite.LoggedIterator - Progress 0.9%, elapsed: 0m 0s 2ms / remaining: 0m 0s 205ms...    
15:56:52.868 [main] ERROR com.github.caiiiycuk.pg2sqlite.Boot$ - null
java.lang.ExceptionInInitializerError: null
    at org.sqlite.SQLiteConfig$Pragma.<clinit>(SQLiteConfig.java:380) ~[sqlite-jdbc-3.42.0.0.jar:na]
    at com.github.caiiiycuk.pg2sqlite.Connection$$anon$1.makeConnection(Connection.scala:26) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at com.github.caiiiycuk.pg2sqlite.Connection.connection$lzycompute(Connection.scala:61) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at com.github.caiiiycuk.pg2sqlite.Connection.connection(Connection.scala:61) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at com.github.caiiiycuk.pg2sqlite.Connection.withStatement(Connection.scala:66) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at com.github.caiiiycuk.pg2sqlite.Connection.execute(Connection.scala:87) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at com.github.caiiiycuk.pg2sqlite.command.CreateTable$.apply(CreateTable.scala:43) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at com.github.caiiiycuk.pg2sqlite.DumpInserter$$anonfun$insert$2.apply(DumpInserter.scala:25) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at com.github.caiiiycuk.pg2sqlite.DumpInserter$$anonfun$insert$2.apply(DumpInserter.scala:24) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at scala.Option.map(Option.scala:146) ~[scala-library-2.11.12.jar:na]
    at com.github.caiiiycuk.pg2sqlite.DumpInserter.insert(DumpInserter.scala:24) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at com.github.caiiiycuk.pg2sqlite.Boot$.liftedTree1$1(Boot.scala:26) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at com.github.caiiiycuk.pg2sqlite.Boot$.delayedEndpoint$com$github$caiiiycuk$pg2sqlite$Boot$1(Boot.scala:25) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at com.github.caiiiycuk.pg2sqlite.Boot$delayedInit$body.apply(Boot.scala:9) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at scala.Function0$class.apply$mcV$sp(Function0.scala:34) ~[scala-library-2.11.12.jar:na]
    at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12) ~[scala-library-2.11.12.jar:na]
    at scala.App$$anonfun$main$1.apply(App.scala:76) ~[scala-library-2.11.12.jar:na]
    at scala.App$$anonfun$main$1.apply(App.scala:76) ~[scala-library-2.11.12.jar:na]
    at scala.collection.immutable.List.foreach(List.scala:392) ~[scala-library-2.11.12.jar:na]
    at scala.collection.generic.TraversableForwarder$class.foreach(TraversableForwarder.scala:35) ~[scala-library-2.11.12.jar:na]
    at scala.App$class.main(App.scala:76) ~[scala-library-2.11.12.jar:na]
    at com.github.caiiiycuk.pg2sqlite.Boot$.main(Boot.scala:9) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at com.github.caiiiycuk.pg2sqlite.Boot.main(Boot.scala) ~[postgresql-to-sqlite_2.11-1.0.3.jar:1.0.3]
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:578) ~[na:na]
    at com.simontuffs.onejar.Boot.run(Boot.java:340) ~[postgresql-to-sqlite_2.11-1.0.3-one-jar.jar:na]
    at com.simontuffs.onejar.Boot.main(Boot.java:166) ~[postgresql-to-sqlite_2.11-1.0.3-one-jar.jar:na]
Caused by: java.lang.NullPointerException: Cannot invoke "[Lorg.sqlite.SQLiteConfig$Pragma;.clone()" because "org.sqlite.SQLiteConfig$Pragma.$VALUES" is null
    at org.sqlite.SQLiteConfig$Pragma.values(SQLiteConfig.java:376) ~[sqlite-jdbc-3.42.0.0.jar:na]
    at org.sqlite.SQLiteConfig.<clinit>(SQLiteConfig.java:357) ~[sqlite-jdbc-3.42.0.0.jar:na]
    ... 27 common frames omitted
Exception in thread "main" java.lang.reflect.InvocationTargetException
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:119)
    at java.base/java.lang.reflect.Method.invoke(Method.java:578)
    at com.simontuffs.onejar.Boot.run(Boot.java:340)
    at com.simontuffs.onejar.Boot.main(Boot.java:166)
Caused by: java.lang.NoClassDefFoundError: Could not initialize class org.sqlite.SQLiteConfig$Pragma
    at com.github.caiiiycuk.pg2sqlite.Connection$$anon$1.makeConnection(Connection.scala:26)
    at com.github.caiiiycuk.pg2sqlite.Connection.connection$lzycompute(Connection.scala:61)
    at com.github.caiiiycuk.pg2sqlite.Connection.connection(Connection.scala:61)
    at com.github.caiiiycuk.pg2sqlite.Connection.close(Connection.scala:82)
    at com.github.caiiiycuk.pg2sqlite.Boot$.delayedEndpoint$com$github$caiiiycuk$pg2sqlite$Boot$1(Boot.scala:41)
    at com.github.caiiiycuk.pg2sqlite.Boot$delayedInit$body.apply(Boot.scala:9)
    at scala.Function0$class.apply$mcV$sp(Function0.scala:34)
    at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
    at scala.App$$anonfun$main$1.apply(App.scala:76)
    at scala.App$$anonfun$main$1.apply(App.scala:76)
    at scala.collection.immutable.List.foreach(List.scala:392)
    at scala.collection.generic.TraversableForwarder$class.foreach(TraversableForwarder.scala:35)
    at scala.App$class.main(App.scala:76)
    at com.github.caiiiycuk.pg2sqlite.Boot$.main(Boot.scala:9)
    at com.github.caiiiycuk.pg2sqlite.Boot.main(Boot.scala)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    ... 3 more
Caused by: java.lang.ExceptionInInitializerError: Exception java.lang.ExceptionInInitializerError [in thread "main"]
    at org.sqlite.SQLiteConfig$Pragma.<clinit>(SQLiteConfig.java:380)
    at com.github.caiiiycuk.pg2sqlite.Connection$$anon$1.makeConnection(Connection.scala:26)
    at com.github.caiiiycuk.pg2sqlite.Connection.connection$lzycompute(Connection.scala:61)
    at com.github.caiiiycuk.pg2sqlite.Connection.connection(Connection.scala:61)
    at com.github.caiiiycuk.pg2sqlite.Connection.withStatement(Connection.scala:66)
    at com.github.caiiiycuk.pg2sqlite.Connection.execute(Connection.scala:87)
    at com.github.caiiiycuk.pg2sqlite.command.CreateTable$.apply(CreateTable.scala:43)
    at com.github.caiiiycuk.pg2sqlite.DumpInserter$$anonfun$insert$2.apply(DumpInserter.scala:25)
    at com.github.caiiiycuk.pg2sqlite.DumpInserter$$anonfun$insert$2.apply(DumpInserter.scala:24)
    at scala.Option.map(Option.scala:146)
    at com.github.caiiiycuk.pg2sqlite.DumpInserter.insert(DumpInserter.scala:24)
    at com.github.caiiiycuk.pg2sqlite.Boot$.liftedTree1$1(Boot.scala:26)
    at com.github.caiiiycuk.pg2sqlite.Boot$.delayedEndpoint$com$github$caiiiycuk$pg2sqlite$Boot$1(Boot.scala:25)
    ... 14 more

I think I'll kindly wait for your fix

[caiiiycuk]

Fixed in v1.1.1, please try like this:

java -jar pg2sqlite-1.0.3.jar -d database.dump -o sqlite.db -t text

[LeJeko]

Works perfectly. Thank you!