Adonis 5 with Mysql not accept Datetime ISO value without timezone

[franklin-vidal]

When using Adonis 5 with Mysql i get an error while tryng to save string Datetime from ISO format with timezone:

{
...
"birth_date": "1998-01-25T16:51:42.000-03:00",
}

Without the timezone the error not occurr:

{
...
"birth_date": "1998-01-25T16:51:42.000",
}

Showin the error:

UnhandledPromiseRejectionWarning: Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '1998-01-25T16:51:42.000-02:00' for column 'birth_date' at row 1

Thanks for help.

[thetutlage]

What happens when you perform the insert statement by running the SQL query directly (without using Lucid)?

[AhmadPirzargar]

I have the same error when using firstOrCreate
how do we fix it?

dateAdded column type Date MySQL 5.7

await MODEL.firstOrCreate({
      baseId: baseId,
      dateAdded: this.todayDate
    }, {
      baseId: baseId,
      dateAdded: this.todayDate
  })

this.todayDate=DateTime.fromISO(DateTime.now().startOf('day').toISO(), { zone: 'utc' })

Error message:

select * from TABLEwherebaseId= 1 anddateAdded= 2022-10-17 00:00:00.000 Z limit 1 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_zone= '[object Object]',loc= '[object Object]',invalid= NULL,week' at line 1`

I set prepare and serialize methods on the model from Datetime

@column.date({
    columnName: 'dateAdded',
    prepare: (v: DateTime) => v.toISO({ suppressMilliseconds: false, includeOffset: false }),
    serialize: (v: DateTime) => v.toISO({ suppressMilliseconds: false, includeOffset: false })
  })
  public dateAdded: DateTime

[ka9737]

I think prepare arrow function not applied when first (find section) executed.

[wallysonn]

This problem is a real pain, because whoever wrote LUCID ORDM with AXION must have forgotten how to correctly transcribe the date to the MySQL/MariDB standard. This problem doesn't happen with PostgreSQL. I deeply regret not having my API in NestJS and using another ORM.