Sqlite store date as iso format

[marceloverdijk]

Hi,

I'm using migrating from plain SQL to JDBI to store records using @BindBean:

@SqlBatch("insert_match")
fun insertMatches(@BindBean matches: List<Match>)

In this example the Match bean has a LocalDate property which is stored as unix timestamp it seems like -619750800000

Sqlite supports multiple formats to store dates as per the docs:

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](https://www.sqlite.org/lang_datefunc.html) 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](https://www.sqlite.org/lang_datefunc.html).

Currently we store dates as ISO8601 string (TEXT) and I need to keep it that way for compatibility.

Is there a way to force JDBI to store the dates as ISO string?

[marceloverdijk]

When creating the jdbi instance, besides passing the jdbc url, it is also possible to pass some specific sqlite-jdbc parameters like below:

val jdbi = Jdbi.create("jdbc:sqlite:${outputFile.absolutePath}", Properties().apply {
    setProperty("date_class", "text")
    setProperty("date_string_format", "yyyy-MM-dd")
})

[stevenschlansker]

Yes, this looks like the right approach. Glad you figured it out.