Question 42262 in PHP Question posted in
Official manual can be found here.

Group same Days by name of day from Datetime Format in MySql – PHP

I have column named start im my database where i store thousands of dates (not the ones you can eat) in the following format:

2019-05-04 07:30:00

To get some stats im using google.charts and trying to group the values by the name of days (Monday, Thuesday).

So far I have the following. Any idea how to extract the day name from the datetime and group them by the same names to get something like:

Monday 120

Thuesday 236

Wednesday 987

and so on

enter image description here

Database Structure:

-- phpMyAdmin SQL Dump
-- version 4.9.7deb1
-- https://www.phpmyadmin.net/
CREATE TABLE `test` (
  `id` int(10) UNSIGNED NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime DEFAULT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4  COLLATE=utf8mb4_unicode_ci;

Select Statement so far:

$sql_8 = "SELECT `start` count(*) as number FROM test GROUP BY `start` ";

PHP Query and Script part:

<?php   
    $result_8 = mysqli_query($conn, $sql_8); 
?> 

<script type="text/javascript">
    google.charts.load('current', {'packages':['table']});
    google.charts.setOnLoadCallback(drawTable);
function drawTable() {
    
    
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Day');
        data.addColumn('number', 'Count');
        data.addRows([ 
        
            <?php  
                while($row = mysqli_fetch_array($result_8)) { 
                    
                    $date = $row["start"];
                    echo "[ ' ".$date." ', ".$row["number"]." ],";   
                }
            ?>          
                  
        ]); 
                     
        var table = new google.visualization.Table(document.getElementById('table_div_8'));
        table.draw(data, {showRowNumber: true, width: '100%', height: '100%',});
    }  
</script>
Tags:

2

Answers


  1. 0

    The MySql / MariaDB function DAYOFWEEK(start) gives you a number 1-7 (1 == Sunday) for any DATE, DATETIME, or TIMESTAMP value. So if you GROUP BY DAYOFWEEK(start) ORDER BY DAYOFWEEK(start) you will get a useful result set in a useful order.

    Something like this query will work.

    SELECT DAYNAME(start) weekday,
       count(*) as number
  FROM test 
 GROUP BY DAYOFWEEK(start)
 ORDER BY DAYOFWEEK(start);

    Pro tip: Every minute you spend studying your RDBMS’s date and time arithmetic operations will save you hours in the future.

    Login or Signup to reply.
  2. 0

    If you’re looking to group by day name regardless of in what year or month the date is, then you can use MySQL DAYNAME() function:

    SELECT DAYNAME(`Start`) AS Daynm,
       COUNT(*) AS number
  FROM test
GROUP BY Daynm;

    And maybe if you want to order by day ascending, then add another function WEEKDAY() – Monday as it’s first day; or DAYOFWEEK() – Sunday as it’s first day.

    SELECT WEEKDAY(`Start`) AS Wkday,
       DAYNAME(`Start`) AS Daynm,
       COUNT(*) AS number
  FROM test
GROUP BY Wkday, Daynm
  ORDER BY Wkday;

    Demo fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search