Issue in converting datetime columns #19
Comments
|
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:
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? |
|
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. |
|
This can be changed with properties of sqlite-jdbc driver |
|
this properties need to be added here |
|
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. |
|
This should be easy to do, we need just add command line argument to change format, Anyaway need to find time to do this. Probably can look into it on next week |
|
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 I think I'll kindly wait for your fix |
|
Fixed in v1.1.1, please try like this: java -jar pg2sqlite-1.0.3.jar -d database.dump -o sqlite.db -t text |
|
Works perfectly. Thank you! |
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!
The text was updated successfully, but these errors were encountered: