0

I want to get records on weekly basis from a table in which i have stored a datetime field. A mysql query which can identify the week number and week start and end date.

Like shown here in the link.

https://www.epochconverter.com/weeks/2017

How can i get that record through mysql query with week number, start and end date of that week and the records between start and end date of the week. Not for current or single week. I need them for all past weeks upto records datetime values available in database table.. I.e

Week (number to know?) - Start date - end date.

Week 01 -   Start date - January 2, 2017 ---- End date --   January 8, 2017
|-> All records in it.
Week 02 -   Start date - January 9, 2017 ---- End date --   January 15, 2017
|-> All records in it.
Paolo Forgia
  • 6,572
  • 8
  • 46
  • 58
Googler
  • 211
  • 1
  • 3
  • 12
  • 5
    First you need to decide if you are using mysql or sql server. They are not the same thing. Then you need to post enough information so others aren't guessing what you want. Table definition, sample data and desired output based on the sample data. – Sean Lange Jun 02 '17 at 13:55
  • also be aware that weeknumbers are different for US then for other regions, like europe for example – GuidoG Jun 02 '17 at 14:07
  • Please, give an example of input parameter values and expected result. – Alexander Jun 03 '17 at 04:08
  • Do you want to get dates of the first and last days of a week, that specified by year and week number? – Alexander Jun 03 '17 at 04:16
  • Or do you mean what there is a table with time stamped records in your database and you want to get week number, first and last dates of week for each record? – Alexander Jun 03 '17 at 04:30
  • @Alexander I need UK based weekly records. Let say it is January and there are four weeks in it. So need mysql query which can separate all 4 weeks records. Which will display start and end date of each week – Googler Jun 03 '17 at 10:14
  • @Googler, oh I had not wait your comment and post the answer. Please, see below. – Alexander Jun 03 '17 at 10:17

2 Answers2

0

Let, there is the main_table in your database with some data. For example:

CREATE TABLE `main_table` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Date` date NOT NULL,
  `Description` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT main_table 
    (`Date`, Description) 
VALUES 
    ('2017-01-02','The first record'),
    ('2017-01-05','The second record'),
    ('2017-01-11','The third record'),
    ('2017-01-17','The fourth record');

Create a user defined function, that returns date of specific day of same week as specified date. Use CREATE FUNCTION syntax:

DELIMITER $$
CREATE FUNCTION `GET_DAY_OF_WEEK` (`date`  DATE, `day` TINYINT UNSIGNED) 
RETURNS DATE
BEGIN
    RETURN `date`- INTERVAL WEEKDAY(`date`) + 1 DAY + INTERVAL `day` DAY;
END$$
DELIMITER ;

If your week is started by Monday, then use 1 for Monday and 7 for Sunday. If your week is started by Sunday, then use 0 for Sunday and 6 for Saturday. For example, my week is started by Monday, and I want to get last Wednesday date, then

SELECT GET_DAY_OF_WEEK('2017-06-03', 3);

returns '2017-05-31'.
Thus to get records of main_table on weekly, you could use the following MySQL query:

SELECT 
    ID, 
    WEEKOFYEAR(`Date`) AS WeekNum,
    GET_DAY_OF_WEEK(`Date`, 1) AS WeekStarted,
    GET_DAY_OF_WEEK(`Date`, 7) AS WeekFinished,
    Description
FROM main_table;

The query result will be

ID  WeekNum WeekStarted WeekFinished    Description
1   1       2017-01-02  2017-01-08      'The first record'
2   1       2017-01-02  2017-01-08      'The second record'
3   2       2017-01-09  2017-01-15      'The third record'
4   3       2017-01-16  2017-01-22      'The fourth record'
Alexander
  • 4,420
  • 7
  • 27
  • 42
  • Looks great @Alexander. Let me give it a try Will update you back. – Googler Jun 03 '17 at 10:18
  • @Googler, did you try the solution? Are there more questions? – Alexander Jun 05 '17 at 09:04
  • Buddy not yet. Had one more question on it. I will update you shortly by tomorrow once i start on it. Little busy right now. Will surely catch up with u. Need more assistance from you. Thanks – Googler Jun 05 '17 at 12:26
  • How can i number the day names on base of the above records. Like i want all records belonging to monday as 1, tuesday as 2, Wed as 3 and so on.. I have a field datetime to be used as base of the records for filtering. – Googler Jun 08 '17 at 13:32
  • @Googler, use [DAYOFWEEK()](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek) method or [DAYNAME()](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayname). – Alexander Jun 08 '17 at 14:38
  • THanks @Alexander. Why i am not able to get distinct records on base of WEEKOFYEAR. like select distinct DAYOFWEEK(DATE(datetime)); – Googler Jun 08 '17 at 20:12
  • Used group by instead. Thanks for the help – Googler Jun 08 '17 at 20:21
  • Can you please help me with this issue? https://stackoverflow.com/questions/44563916/current-day-total-login-time-mysql – Googler Jun 15 '17 at 10:05
  • can you give ur 5 mins today please. I need to work on it. :( Would really appreciate it – Googler Jun 15 '17 at 10:22
-2

Learn how to get the weeks here, then to get mysql data between the dates, use this statement: 

$sql = "SELECT * FROM stock WHERE dateColumnName BETWEEN '{$startDate}' AND '{$endDate}'";

Ensure the dates are in format 'Y-m-d'.

UPDATE:

Here is the full code that works for me:

<?php 
function getStartAndEndDate($week, $year) {
  $dto = new DateTime();
  $dto->setISODate($year, $week);
  $ret['week_start'] = $dto->format('Y-m-d');
  $dto->modify('+6 days');
  $ret['week_end'] = $dto->format('Y-m-d');
  return $ret;
} ?>
    <table>
        <tr>
            <th>Week Number</th>
            <th>From Date</th>
            <th>To Date</th>
        </tr>
<?php 
for($i=1; $i<=52; $i++){
    $week_array = getStartAndEndDate($i,2017);
    $sql = "SELECT * FROM stock WHERE on_date BETWEEN '{$week_array['week_start']}' AND '{$week_array['week_end']}'";
    $result = mysqli_query($con,$sql);
    $item = mysqli_fetch_assoc($result);
    echo "<tr>
            <td>Week $i </td>
            <td>".$week_array['week_start']."</td>
            <td>".$week_array['week_end']."</td>
            <td>".$item['id']."</td>
        </tr>"; 
} ?>

</table>
Josiah
  • 118
  • 10