Group same Days by name of day from Datetime Format in MySql-CodePudding

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

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>

CodePudding user response：

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.

CodePudding user response：

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