Mysql – Getting the first day of week

date formatMySQL

In continuation of my previous question:
Get week/month data

I have a MySQL table with day column of type DATE and position column of type INT.

I need to fetch average position for every week in consideration. (A week is in consideration if there is a row with day value belonging to this week.)

The fetched data should be an array of two columns:

date
position

Here "date" is the first day (Sunday) of the week and "position" is the average position for this week.

The trouble is how to get the first day for a week.

Best Answer

I found a solution in comments of http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

SELECT DATE_ADD(day, INTERVAL (7 - DAYOFWEEK(day)) DAY) AS day, AVG(position) AS position FROM table

Related Solutions

Mysql – How to improve the SQL statement with weekly results with week starting on Thursday or any other day of the week

Here is a query I wrote up to give you the most recent thursday and the ending wednesday

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

Here is an example for today, 2011-09-21

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2011-09-15 00:00:00 | 2011-09-21 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Just replace the NOW() function calls with whatever datetime you like and you will have the week starting Thursday all the time for the give datetime you choose.

Here is another example using the specific date '2011-01-01'

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE('2011-01-01') - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE('2011-01-01') dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2010-12-30 00:00:00 | 2011-01-05 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Your query of table referencing today would resemble something like this:

SELECT * from `table`,
(SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A) M
WHERE `date` >= thu_beg
AND `date` <= wed_end;

Give it a Try !!!

UPDATE 2011-09-22 16:27 EDT

This was my proposed query for marking Thu-Wed.

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

How about other weeks ???

(SELECT SUBSTR('6012345',wkndx,1) does the week starting Mon ending Sun
(SELECT SUBSTR('5601234',wkndx,1) does the week starting Tue ending Mon
(SELECT SUBSTR('4560123',wkndx,1) does the week starting Wed ending Tue
(SELECT SUBSTR('3456012',wkndx,1) does the week starting Thu ending Wed
(SELECT SUBSTR('2345601',wkndx,1) does the week starting Fri ending Thu
(SELECT SUBSTR('1234560',wkndx,1) does the week starting Sat ending Fri
(SELECT SUBSTR('0123456',wkndx,1) does the week starting Sun ending Sat

Mysql – Loyalty reward – record for every day of a week

Maybe.

SELECT count(distinct date(time_they_logged_in)) 
FROM `users` 
WHERE `users`.`time_they_logged_in` between now()-interval 7 day and now();

If the count is 7 they logged in at least once per day in the past week.

Best Answer

Related Solutions

Mysql – How to improve the SQL statement with weekly results with week starting on Thursday or any other day of the week

Mysql – Loyalty reward – record for every day of a week

Related Question