Working with Dates in Database

Hi there,

I have to return an array based on the month from when they were created. In my model, I have the created_at property, in timestamp format, however, I don’t know, how to compare only the months.

Is that possible get this information using adonis database query?

Best regards,

You can use Postgres EXTRACT.

Postgres has a lot of useful functions, you should take a look in Date/Time Functions and Operators.

Finally, you can use this statement in your code:

await Model
  .query()
  .whereRaw('(EXTRACT(MONTH FROM your_models.created_at)) = ?)', [monthInteger])
  .fetch()
3 Likes

Hi,

Thanks for answer me, but it isn’t works.

My query is:


    const invoices = Invoice.query()
      .innerJoin('carts', 'carts.id', 'invoices.id')
      .innerJoin('shops', 'shops.id', 'carts.shop_id')
      .where('shops.id', shop_id)
      .where('status', 'paid')
      .whereRaw('(EXTRACT(MONTH FROM invoices.created_at)) = ?)',[month])
      .fetch()

    return invoices

Can I mix where and whereRaw in the same block?

When I tested the method above, the console returns:

select * from "invoices" inner join "carts" on "carts"."id" = "invoices"."id" inner join "shops" on "shops"."id" = "carts"."shop_id" where "shops"."id" = $1 and "status" = $2 and (EXTRACT(MONTH FROM invoices.created_at)) = $3) - syntax error at or near ")"

Can u help me?

Thanks

Hi there,

I’ve modified the query to:

const invoices = Invoice.query()
      .innerJoin('carts', 'carts.id', 'invoices.id')
      .innerJoin('shops', 'shops.id', 'carts.shop_id')
      .where('shops.id', shop_id)
      .where('status', 'paid')
      .whereRaw("EXTRACT(MONTH FROM TIMESTAMP 'invoices.created_at') = ?", month)
      .fetch()

But, there is another error:

select * from "invoices" inner join "carts" on "carts"."id" = "invoices"."id" inner join "shops" on "shops"."id" = "carts"."shop_id" where "shops"."id" = $1 and "status" = $2 and EXTRACT(MONTH FROM TIMESTAMP 'invoices.created_at') = $3 - invalid input syntax for type timestamp: "invoices.created_at

Even running on PostgresSQL, it doesn`t works:

SELECT EXTRACT(MONTH FROM TIMESTAMP 'invoices.created_at')

The same error above.

I think the adonis record the created_at in another format and it isn’t timestamp format, because, Postgres receives a timestamp like this 2001-02-16 20:38:40, and in the database we have something like this:
image

Anyone can help me?

Thanks