如何使用mysql数据库中的日期字段获取每周记录

Question

I want to select weekly and monthly records from MySQL database without specifying a start and end date, but by using the first date a record was entered into the database and calculating the sum, 7 days afterwards.

What I want to achieve is every 7 days the cost field in my application must reset to 0 and with each day calculating, until it gets to the 7th day and the cycle starts again.

I have tried the DATE_ADD and DATE_SUB function and both don't work to my expectation.

I have researched and tried many different solutions but they all don't seem to work There is sample of my queries I have tried.

Solution 1

select expense_name, expense_date, cost from expenses 
where week(expense_date)

Solution 2

select expense_name, expense_date, cost from expenses 
where yearweek(expense_date)

Sample Data

Name    Date       Cost 
Food1   2018-02-23  14
Food2   2018-02-23  8
Food3   2018-02-27  10
Food4   2018-02-27  10
Food5   2018-02-27  50
Food6   2018-03-1   2.6
Food7   2018-03-8   18

Thanks

Answer 1

Am traveling atm but I think this will kinda help you.

Not sure what column name you use for saving the creation date but for now I use “created_at”.

WHERE created_at BETWEEN created_ar AND DATE_ADD( created_add, INTERVAL +1 MONTH)