Exposed: Confusing behaviour of DateColumnType with SQLite

[x3ro]

I ran into a confusing behaviour of the DateColumnType, which is essentially composed of two things:

1. The type's notNullValueToDB method converts everything to milliseconds
2. The transaction logger output shows the date values as if they were strings

For example (the runnable version can be found here):

val someDate = DateTime.parse("2017-12-01")

object Transactions : IntIdTable() {
    val name = varchar("name", 32)
    val date = date("date")
}

[...]

Transactions.insert {
            it[name] = "test"
            it[date] = someDate
}

Generates the following logs:

13:58:38.448 [main] DEBUG Exposed - CREATE TABLE IF NOT EXISTS Transactions (id INTEGER PRIMARY KEY, name VARCHAR(32) NOT NULL, "date" DATE NOT NULL)
SQL: CREATE TABLE IF NOT EXISTS Transactions (id INTEGER PRIMARY KEY, name VARCHAR(32) NOT NULL, "date" DATE NOT NULL)

Inserting example data
13:58:38.495 [main] DEBUG Exposed - INSERT INTO Transactions ("date", name) VALUES ('2017-12-01', 'test')
SQL: INSERT INTO Transactions ("date", name) VALUES ('2017-12-01', 'test')
13:58:38.500 [main] DEBUG Exposed - SELECT Transactions.id, Transactions.name, Transactions."date" FROM Transactions WHERE Transactions."date" = '2017-12-01'
SQL: SELECT Transactions.id, Transactions.name, Transactions."date" FROM Transactions WHERE Transactions."date" = '2017-12-01'
[Transactions.id=1, Transactions.name=test, Transactions."date"=1512086400000]

Retrieving without Exposed
name = test
date = 1512086400000

If I interpret the logs correctly, Exposed claims to be inserting 2017-12-01, but in reality inserts 1512086400000. I also find it confusing that the time is stored as milliseconds and would like to learn the rationale behind this decision. So in summary:

1. There appears to be a bug in Exposed where queries in the debug log differ from the actual queries executed
2. I'd like to learn why milliseconds are used as the only way to store dates w/ SQLite

---

PS: Thanks for writing Exposed. It's quite fun use!

[x3ro]

By the way, I've currently tackled this issue by implementing my own DateColumnType with the following notNullValueToDB, which appears to exhibit they behaviour I require. Not sure if something like this makes sense for Exposed though, or if there is a better way to achieve this effect.

override fun notNullValueToDB(value: Any): Any {
        if (value is DateTime) {
            return if (time) {
                SQLITE_DATE_TIME_STRING_FORMATTER.print(value)
            } else {
                SQLITE_DATE_STRING_FORMATTER.print(value)
            }
        }
        return value
    }
}

[Tapac]

Hi @x3ro , thank you for reporting that issue.

Right now Exposed treats SQLite datetime columns as NUMERIC (https://www.sqlite.org/datatype3.html#date_and_time_datatype), but jdbc driver still thinks that this is TEXT column, moreover there is a bug that only datetime is mapped to NUMERIC, but for date we still use DATE (which possibly replaced with TEXT on backend too).

We going to make huge refactoring of dates support in Exposed on next release, so your issue also be fixed in it.

[Tapac]

@x3ro , @jvtrigueros , hello!
There are quite a lot changes in place of date time in different places, can you please check that the issue exists if you use the latest Exposed version?
Also, do you use javatime or jodatime modules?

[jvtrigueros]

@Tapac I haven't updated to the latest version, I'm still using the 0.17.x branch, additionally, I've since switched to PostgresQL. That being said, if it helps, I can rollback my repo and try the same code with the latest version of Exposed.

I am still using jodatime but would like to drop that in favour of Vanilla java.time.

[Tapac]

@jvtrigueros, thank you, but I think that I found a test that covered such case, so I'll just close an issue for now.