Return date fields as it is for Database and Lucid ORM


#1

Hello friends,

I just recognized that all my production apps returns different date time than what it is in my database.
I’m using MSSQL.

for instance,
when I have “2019-01-02 12:41:41.000” in my DB,
but it returns this when using Lucid ORM.

2019-01-02 19:41:41

AND this when Using “Database” Query builder

2019-01-02T12:41:41.000Z

i’ve tried using this solutions, but it doesnt work:

is there any way adonis would return date as it is,
without changing it’s timezone. ?

I’m Using Adonis 4.1


#3

Here’s data from my DB,
Columns:
fields
Records:


all datetime stored in my DB are in my local date time. (Asia/Jakarta)

Here’s my code, trying to retrieve it with Lucid (using Repl):

> const Database = use('Database')
undefined
> const Log = use('App/Models/Log')
undefined
> let get = await Log.query().select('id','user_id','created_at','updated_at').where('id','a9864480-0e67-11e9-a6a5-ddb732b15ab7').first()
> get.toJSON()
{ id: 'a9864480-0e67-11e9-a6a5-ddb732b15ab7',
  user_id: 'rkdFflFJ7',
  created_at: '2019-01-02 22:23:20',
  updated_at: '2019-01-02 22:23:20' }

Here’s when using Database:

> get = await Database.connection('mssql2').query().select('id','user_id','created_at','updated_at').from(Log.table).where('id','a9864480-0e67-11e9-a6a5-ddb732b15ab7').first()
> get
{ id: 'a9864480-0e67-11e9-a6a5-ddb732b15ab7',
  user_id: 'rkdFflFJ7',
  created_at: 2019-01-02T15:23:20.000Z,
  updated_at: 2019-01-02T15:23:20.000Z }
>

you might noticed that by using Database,
the hours is the same with DB, but it’s converted to ISO in UTC Timezone.
so when i tried formatting it, it will return as 22:23:20

All i need is to retrieve the values as it is.


#4

OPTION ONE

(@eldinbiz the second option is more related you´re trying to achieve, now my example is with MySQL you only have to find the equivalent function to MSSQL Server to get the same result)

This is my database info


+---------------------+
| created_at          |
+---------------------+
| 2018-12-31 04:27:59 |
| 2018-12-31 04:27:59 |
| 2018-12-31 04:27:59 |
| 2018-12-31 04:27:59 |
+---------------------+

Now with this query I get this values


const data = await ModelName.query().select('created_at').fetch()

And I get the following on my web browser


[
{
"created_at": "2018-12-31 04:27:59"
},
{
"created_at": "2018-12-31 04:27:59"
},
{
"created_at": "2018-12-31 04:27:59"
},
{
"created_at": "2018-12-31 04:27:59"
}
]

So as you see I get the correct values, please try this but inspite of your terminal please via your web browser

STEPS TO DO

  • build a model for your table
  • build a controller where you invoke your model
  • build a query to fetch all your data using your ModelName
  • return that variable
  • visualizing via your web browser

OPTION TWO

Make use of DATE_FORMAT function in this way


await Database.raw('SELECT nameuser, DATE_FORMAT(created_at, "%Y-%m-%d %H:%i:%s")  FROM users')


This part "%Y-%m-%d %H:%i:%s" formats your data into correct date and time


#5

I got this problem on All my production sites.

the reason I used REPL is to reproduce the problem i found in my web Apps in a more simplified way rather than recreating new models, controllers, and return it as a response.json on controller.

i can assure you, the result in REPL is similar with the problem on my sites. :grin:

maybe some info on my packages.json will help :slightly_smiling_face:

{
  "name": "adonis-fullstack-app",
  "version": "4.1.0",
  "adonis-version": "4.1.0",
  "description": "The fullstack application boilerplate for Adonisjs",
  "main": "index.js",
  "scripts": {
    "start": "node server.js",
    "test": "node ace test"
  },
  "keywords": [
    "adonisjs",
    "adonis-app"
  ],
  "author": "",
  "license": "UNLICENSED",
  "private": true,
  "dependencies": {
    "@adonisjs/ace": "^5.0.8",
    "@adonisjs/auth": "^3.0.7",
    "@adonisjs/bodyparser": "^2.0.9",
    "@adonisjs/cors": "^1.0.7",
    "@adonisjs/drive": "^1.0.4",
    "@adonisjs/fold": "^4.0.9",
    "@adonisjs/framework": "^5.0.12",
    "@adonisjs/ignitor": "^2.0.8",
    "@adonisjs/lucid": "^6.1.3",
    "@adonisjs/mail": "^3.0.9",
    "@adonisjs/session": "^1.0.28",
    "@adonisjs/shield": "^1.0.8",
    "@adonisjs/validator": "^5.0.6",
    "@adonisjs/websocket-client": "^1.0.9",
    "adonis-scheduler": "^3.0.2",
    "angka-menjadi-terbilang": "^0.5.0",
    "axios": "^0.18.0",
    "html-pdf": "^2.2.0",
    "jsonwebtoken": "^8.4.0",
    "locutus": "^2.0.10",
    "moment": "^2.23.0",
    "moment-range": "^4.0.1",
    "moment-timezone": "^0.5.23",
    "mssql": "^4.1.0",
    "promise-async": "^0.2.0",
    "r2": "^2.0.1",
    "request-promise": "^4.2.2",
    "shortid": "^2.2.14",
    "soap": "^0.25.0",
    "superagent": "^4.0.0",
    "uuid": "^3.3.2",
    "xml-js": "^1.6.8"
  },
  "devDependencies": {},
  "autoload": {
    "App": "./app"
  }
}

some updates:
my momentary solution is to use moment-timezone library to convert all my datetimes strings returned from my query into UTC time zone, which strangely show as my Local Date time :sweat:
but this is not an elegant way, since i have to convert all my sources to use moment-timezone library, and it also only affect queries using Lucid ORM, not Database.


#6

i could also use your OPTION TWO, which is formating all dates in the SQL Queries.
but again, that’ll require me to convert All my queries using Raw (or at least raw select on Lucid) in all my source code. which is not an efficient way.


#7

Found the solution !!!

Just parse options inside the connection keys in database.js config. :sweat_smile:

what I did wrong on my previous trial is parsing it outside the connection key:

mssql2: {
    client: 'mssql',
    connection: {
      host: Env.get('DB2_HOST', 'localhost'),
      port: Env.get('DB2_PORT', '1433'),
      user: Env.get('DB2_USER', 'sa') ,
      password: Env.get('DB2_PASSWORD', 'root'),
      database: Env.get('DB2_DATABASE', 'mylogs')
    },
    useUTC: false
  }

here’s the correct one:

mssql2: {
    client: 'mssql',
    connection: {
      host: Env.get('DB2_HOST', 'localhost'),
      port: Env.get('DB2_PORT', '1433'),
      user: Env.get('DB2_USER', 'sa') ,
      password: Env.get('DB2_PASSWORD', 'root'),
      database: Env.get('DB2_DATABASE', 'mylogs'),
      options: {
        useUTC: false
      }
    }
  }

silly me :sweat_smile:
hope anyone that are probably having this kind of problem will find this useful :grin: