Today I spent much time to figure out an issue related to ‘date’ on my new project with NodeJs / Adonis using a Mysql backend.

The issue which I was facing was that, whenever I query the LUCID model with the date ( a DATE-TIME field) to query information, the results returned were just of the previous date.

I tried setting MySQL default time zone in my.ini and also try changing a few settings on the application side but nothing seems to be working.

Finally, I got it working by setting the time zones same on Adonis application as well as MYSQL server as follows

my.cnf

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
secure-file-priv=/var/lib/mysql
lower_case_table_names=1
default-authentication-plugin=mysql_native_password
default-time-zone=ASIA/Bahrain

database.ts

mysql: {
      client: 'mysql',
      connection: {
        host: Env.get('DB_HOST', '127.0.0.1') as string,
        port: Number(Env.get('DB_PORT', 3306)),
        user: Env.get('DB_USER', 'lucid') as string,
        password: Env.get('DB_PASSWORD', 'lucid') as string,
        database: Env.get('DB_NAME', 'lucid') as string,
        timezone: 'Asia/Bahrain',
      },
      healthCheck: false,
NodeJs and Mysql Date problem

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.