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:

idquantitytime_create
110 2019-09-12 20:38:46
210 2019-09-13 20:39:03
310 2019-09-14 20:39:03
410 2019-09-15 20:28:10
510 2019-09-15 20:39:11
620 2019-09-16 20:28:10
730 2019-09-17 20:28:41
840 2019-09-18 20:28:41
950 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_hari

Explain:

  • 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_createangka_harinama_harijumlah
2019-09-15 20:28:10 1Sunday20
2019-09-16 20:28:10 2Monday20
2019-09-17 20:28:41 3Tuesday30
2019-09-18 20:28:41 4Wednesday40
2019-09-19 20:29:10 5Thursday50

Thanks for reading

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *