[v5] DateTime format and QueryBuilder

[edesideri]

Hi,
i have query build on QueryBuilder (Database.query()...etc)
The DateTime field is serialized as UTC format (2020-11-02T12:43:06.000Z) while in mySql table is 2020-11-17 13:06:42 like i would to show.
How can i define the DateTime format?
Thanks

[AhmadPirzargar]

Does anyone have a solution?

[tomgobich]

There's a method on DateTime called toSql(), this should format to a SQL compatible format.

const posts = await Post.query().where('publishAt', '<=', DateTime.now().toSql())

https://moment.github.io/luxon/api-docs/index.html#datetimetosql

[AhmadPirzargar]

Yes, I had to use this method

[AhmadPirzargar]

But if the DateTimeFormat defined in DialectMysql is used for serializing on executing a query on the database, the problem is solved.

[andre-ab]

In the database we have the dateTime like this: event: 2024-04-09 10:30:00

when using the Database it brings a dateTime with change
Look:

await Database
  .from('appointment as ap')
  .select('event')

return 2024-04-09T11:00:00.000Z
three hour difference, how to fix this?

[andre-ab]

in file .env Add

TZ=UTC