Get this week daily data from mysql
Known:
- Table name is table_stock_in
- This current date is 21 September 2019
Data displayed in below table:
| id | quantity | time_create |
| 1 | 10 | 2019-09-12 20:38:46 |
| 2 | 10 | 2019-09-13 20:39:03 |
| 3 | 10 | 2019-09-14 20:39:03 |
| 4 | 10 | 2019-09-15 20:28:10 |
| 5 | 10 | 2019-09-15 20:39:11 |
| 6 | 20 | 2019-09-16 20:28:10 |
| 7 | 30 | 2019-09-17 20:28:41 |
| 8 | 40 | 2019-09-18 20:28:41 |
| 9 | 50 | 2019-09-19 20:29:10 |
Wanted:
I want to get daily data for this week
Question:
How to get it … ?
Answer:
We can use this query below:
SELECT time_create,DAYOFWEEK(time_create) as angka_hari, DAYNAME(time_create) as nama_hari, sum(quantity) as jumlah FROM `table_stock_in` WHERE time_create > DATE_SUB(DATE(NOW()),INTERVAL(DAYOFWEEK(NOW())-1) DAY) group by angka_hariExplain:
- DAYOFWEEK() returns the weekday index for a given date (a number from 1 to 7), 1=Sunday to 7=Saturday.
- DAYNAME() returns the day name
- DATE_SUB(DATE(NOW()),INTERVAL(DAYOFWEEK(NOW())-1) DAY) is for date subtraction “current date – ( the number day of week -1)”
Result:
| time_create | angka_hari | nama_hari | jumlah |
| 2019-09-15 20:28:10 | 1 | Sunday | 20 |
| 2019-09-16 20:28:10 | 2 | Monday | 20 |
| 2019-09-17 20:28:41 | 3 | Tuesday | 30 |
| 2019-09-18 20:28:41 | 4 | Wednesday | 40 |
| 2019-09-19 20:29:10 | 5 | Thursday | 50 |
Thanks for reading