Date Format query in controller


#1

All my dates in this format when execute query

format: date_send: 2018-11-29T02:00:00.000Z,
query: await ModelAccount.query().where({code: value, region_id: regionId}).first()

How change date format to: yyyy-mm-dd

i try add in model, but not success
static boot () {
super.boot()
this.addHook(‘afterFetch’, async(instances) => {
instances = instances.map(row => {
row.date_send = dateFormat(row.date_send, ‘YYYY-MM-DD’)
})
})
}


#2

Hi dude, you can achieve it in this way; check this example

.select(Database.raw('DATE_FORMAT(created_at, "%Y-%m-%d") as date'))

As you can see We make use of:

  • select() method

  • Now we invoque the Database helper

  • After we use raw() method to write pure SQL

  • Now we can use DATE_FORMAT() which requires 2 arguments

  1. column name that contains date

  2. The format you will output the date
    Finally we rename this query with an alias

Here the full example working

``
‘use strict’
const Database = use(‘Database’);

class UserController {

async insertOne () {
	return Database.table('users')
				   .select('username')
				   .select(Database.raw('DATE_FORMAT(created_at, "%Y-%m-%d") as date'))
} 

}

module.exports = UserController


#3

great,

thanks for helping me


#4

please remember mark my answer as your solution

:smiley:


#5

First, you’ll want to define it as a date field in your model:

  static get dates () {
    return super.dates.concat(['date_send'])
  }

This will take care of creating a moment instance for the field when the data is populated from the DB.

As for formatting, you could use the formatDates method, thusly:

  static formatDates (field, value) {
    if (field === 'date_send') {
      return value.format('YYYY-MM-DD')
    }
    return super.formatDates(field, value)
  }

Or, you could use a getter mutator:

  getDateSend(date_send) {
    return date_send.format('YYYY-MM-DD')
  }

Formatting in the DB as @ShadowPaz mentioned works, but then you have to explicitly do that in the controller any time you need that date formatted that way, setting it up in the model is a cleaner solution though, really… and – date formatting in the DB differs depending on the database being used. MySQL, Postgres, and sqlite all have different date formatting syntax. So that would be problematic if running mysql or postgres in production, but testing with sqlite, for example.