Slick 3.3.3 not parsing mysql datetime

Question

Im using slick 3.3.3, and i have a table with columns setup with LocalDateTime (ive also tried OffsetDateTime)

I was told slick 3.3.3 fixes the issue but no matter what I do i still get Text '2023-09-20 21:20:21' could not be parsed at index 10 when trying to parse this column

    def startDate = column[Option[LocalDateTime]]("start_date")

I can see in the code they are assuming in the mysql profile iso8601String, and just letting LocalDateTime or OffsetDateTime parse it on its own, but none of these natively support the above string, which is how mysql naturally stores the string. I dont understand.

Ive also tried using their custom mappers myself but I equally dont understand how to set that up

    MappedColumnType.base[Option[OffsetDateTime], String](
      localDateTime => localDateTime.map(_.format(formatter)).orNull,
      timeStr => if (timeStr == null) None else Some(OffsetDateTime.parse(timeStr, formatter))
    )

Even if i feed the implicit directly into the column like so:

def startDate = column[Option[LocalDateTime]]("start_date", O.SqlType("datetime"))(dateTimeMappingOption)

It just doesn't trigger it. Im also not quite sure how it handles optionals, if im expected to make parsers for each version and their optional or if theres some way to do that automatically.

Help is appreciated.

Answer 1

Here you have a migration guide for slick 3.3.

In the case of MySql says:

slick.jdbc.MySQLProfile

Java Type	SQL Type	Example SQL Literal
java.time.LocalDateTime	TEXT	'2019-02-03T18:20:28.661'
java.time.OffsetDateTime	TEXT	'2019-02-03T18:20:28.661Z'

Which means, the data type of the columns in the table must be of type TEXT. If the columns can be null, you could define the column like this

def startDate = column[Option[LocalDateTime]]("start_date")

If this example is not enough, edit your post adding how you are creating the table in the database and also show the error message you got when you execute the code