How do I change the datetime to a date only and then use the .distinct()

I have a table called Orders

Here is a sample values of that table:

[
  {
     id: 1,
     delivery_date: 2020-06-01 00:00:00,
     quantity: 2
  },
  {
     id: 2,
     delivery_date: 2020-06-01 01:00:00,
     quantity: 2
  },
]

I now queried this

...
await Order
      .query()
      .select('delivery_date')
      .distinct('delivery_date')
      .fetch()

The .distinct() did not work because the time is not the same, so my question is how do I use distinct to work even though the time is not the same and the date is the same? and I also need to return the date only, without the time

2 Likes

I use Database:

await Order
      .query()
      .select(Database.raw('DATE(delivery_datem)'))
      .distinct(Database.raw('DATE(delivery_datem)'))
      .fetch()

This didnt exactly solve my problem but helped me get an i idea to solve my other problem, thanks for this

So what I did is just separate the date and the time. As per the project requirements I think that was the best solution for me, not sure why, but adonis was still adding the time on my date, I think it was moment under the hood, so to solve that;

You have to let Adonis know that your column is a date first by adding this on your model, add your column name inside that array:

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

After that we need to format that column when displaying the data by using castDate

static castDates(field, value) {
    if (field === 'delivery_date') {
      return value.format('YYYY-MM-DD')
    }

    return super.formatDates(field, value)
}

Note also that you have to call .fetch() for that to work

All this are on the documentation:
https://adonisjs.com/docs/4.1/lucid#_dates

1 Like