How to get year and date

here of may sample code i want to get the year(2020) , and month in ’ created_at ’

const salary = await salaryRepository.query
.select(‘created_at’)
.sum(‘amount’)
.where(‘created_at’, ‘like’, ‘2020’ + ‘%’) // i want to get the year
.groupBy(‘created_at’) // and i want to group by using month only
.orderBy(‘created_at’)

    return apiResponse.responseData(salary)

This line should be:

.where('created_at', 'like', '%2020%')

// or 

.whereRaw(`created_at like '%${year}%'`)

To group by month, you need to extract the month from the date string. Use the SUBSTRING() function of MySQL.

If your date is format like YYYY-MM-DD, you can try:

SUBSTRING(date, 6, 2) // MM

Combine that with .groupByRaw()

See:
http://knexjs.org/#Builder-groupByRaw

1 Like

To get the actual year, you should use .whereRaw("year(created_at) = ?", year)

For month, use .whereRaw("month(created_at) = ?", month)

month(), year() are mysql functions to get out month and year from a datetime, date or timestamp column.

3 Likes

Hi ndianabasi

thanks for the reply , this is my sample query ,and this is what I want to do for my adonis js

SELECT Month(created_at), SUM(amount)
FROM salary
GROUP BY YEAR(created_at), MONTH(created_at)

I want to sum my amount based on the month

const year = new Date().getFullYear()
const salary = await SalaryRepository.query()
.select(‘created_at’)
.sum(‘amount’)
.where(‘created_at’, ‘like’, ‘%’ + year + ‘%’)

        .groupBy('created_at') // 
        .orderBy('created_at')
  
        return apiResponse.responseData(expense)

thanks olaoluwa-98

how about if groupBy (‘month(created_at’) like on my query, i want to group using month

SELECT Month(created_at), SUM(amount)
FROM salary
GROUP BY YEAR(created_at), MONTH(created_at)

1 Like

thanks it’s working now hehe

1 Like

thanks i know how to use whereraw, and it’s working now hehe

I think what you want to do is group by month and year. So the grouping goes like this:

Jan 2020, Feb 2020, Mar 2020…

Am I right?

If so, then you should use groupByRaw.

group by DATE_FORMAT(created_at,'%b %Y'): this would group by month + year

You should have this after using it.

const salary = await salaryRepository.query
.select("created_at")
.sum("amount")
.whereRaw("year(created_at) = ?", year)
.groupByRaw("DATE_FORMAT(created_at,'%b %Y')")
.orderBy("created_at")

Hi olaoluwa-98

i try .groupByRaw(“DATE_FORMAT(created_at,’%b %Y’)”) but its error ,

and this is my code now

const salary = await SalaryRepository.query()
//select(‘created_at’)
.sum(‘amount’)
.whereRaw(“year(created_at) = ?”, year)
.groupByRaw(“month(created_at)”)
.orderByRaw(“month(created_at)”)
return apiResponse.responseData(expense)

And this is my question again, when i put the
select(“month(created_at)”)
because i want to show the month only and sum amount for my response , then when i try this select(“month(created_at)”) doesn’t work ,
what can be the best way for me to show you the month

this is my query response on mysql , and this is what I want to happen in my response.
image

Thank you and advance

Oww i’m sorry this code is working , when i remove the .orderByRaw

.groupByRaw(“DATE_FORMAT(created_at,’%b %Y’)”)

thank you bro

1 Like

You’re trying to use raw SQL in the select statement. To do that, import Database.

const Database = use("Database");

Then use

.select(Database.raw('month(created_at)'))
1 Like

Okay great!

Hi olaoluwa-98

Thank you so much for helping me, and it’s working now, and I’ve learned so much from you , keep safe bro

2 Likes

You’re welcome!

1 Like