Hello,
Here is my query:
$sql = "SELECT profit, c_time FROM data ORDER BY c_time ASC";
c_time is a date
the date is in the standart format e.g. 2018-04-22 16:44:53
I want the output tp be in MM-YY format. How can it be done in the query ?
SELECT DATE_FORMAT("2019-02-02", "%m-%y");
Replace the date with your date column name, c_time.
You might want to check out the manual to get familiar with the format specifier options.
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
Thanks for the quick response.
It seems that I don’t know how to use it. I changed my query.
$sql = 'SELECT DATE_FORMAT("c_time", "%m-%y"), profit FROM data ORDER BY c_time ASC';
Here is a sample of the JSON output:
{DATE_FORMAT(“c_time”, “%m-%y”): null, profit: -30.82}
I believe that this is not the way as I get null along the x-axes
I can’t find an explanation abut changing the date format in the function. Here it is:
function get_all_profit()
{
global $db;
try
{
$sql = 'SELECT DATE_FORMAT("c_time", "%m-%y"), profit FROM data ORDER BY c_time ASC';
$stmt = $db->prepare($sql);
$stmt->execute();
if($stmt->rowCount() == 0)
return 0;
else
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
catch(Exception $e)
{
return false;
}
}
try removing the quotes from c_time
mysql thinks it’s a string, not a column
I removed them I still get null on the x-axis though the json data gives the desires date format
Here is a screenshot. What is it now ? any suggestions ?
I think the first column needs an alias/name which is expected by the application code.
Thanks, It works.
Now I wanted to make the xaxix less densed with less values on it. Here is the options code:
var options = {
chart: {
title: 'Box Office Earnings in First Two Weeks of Opening',
subtitle: 'in millions of dollars (USD)',
curveType: 'function'},
hAxis: { showTextEvery: 10, minTextSpacing: 30},
};
Why doesn’t it get more spaced?
Here is a part of the line graph
I think you intend on calculating the total profit per day.
SELECT DATE_FORMAT(“c_time”, “%m-%y”) month_year, SUM(profit) total_profit FROM data GROUP BY month_year ORDER BY c_time ASC
1 Like
that should be GROUP BY month_year ORDER BY month_year ASC
I don’t believe sorting by a string value is going to result in the expected outcome.
“believe” being the operative word… perhaps you should test it 
I’m fairly certain a standard sorting algorithm on those string won’t output the m-y combinations in sequential order. Even though c_time is indeterminate the results will be sorted sequentially since all rows in the same group will share the same month and year. I feel fairly confident making that determination without installing MySQL on my work machine and testing it.
from “believe” to “fairly certain”.,.
how many “standard sorting alrgorithms” do you know that will ~not~ sort these into the correct sequence, please?
'01-18'
'02-18'
'03-18'
'04-18'
'05-18'
'06-18'
'07-18'
'08-18'
'09-18'
'10-18'
'11-18'
'12-18'
'01-19'
'02-19'
'03-19'
'04-19'
'05-19'
'06-19'
'07-19'
'08-19'
'09-19'
'10-19'
'11-19'
'12-19'however, you may have a point if years from the last part of the 20th century are included, in which case OP should switch from '%m-%y' to '%m-%Y'
This for one.
<?php
$dates = [
'01-18',
'02-18',
'03-18',
'04-18',
'05-18',
'06-18',
'07-18',
'08-18',
'09-18',
'10-18',
'11-18',
'12-18',
'01-19',
'02-19',
'03-19',
'04-19',
'05-19',
'06-19',
'07-19',
'08-19',
'09-19',
'10-19',
'11-19',
'12-19',
];
sort($dates);
print_r($dates);
Result
Array
(
[0] => 01-18
[1] => 01-19
[2] => 02-18
[3] => 02-19
[4] => 03-18
[5] => 03-19
[6] => 04-18
[7] => 04-19
[8] => 05-18
[9] => 05-19
[10] => 06-18
[11] => 06-19
[12] => 07-18
[13] => 07-19
[14] => 08-18
[15] => 08-19
[16] => 09-18
[17] => 09-19
[18] => 10-18
[19] => 10-19
[20] => 11-18
[21] => 11-19
[22] => 12-18
[23] => 12-19
)
I tested it
SELECT DATE_FORMAT(c_time, “%m-%y”) AS time, profit FROM data GROUP BY time ORDER BY c_time ASC
I did change the x axis but ic changed the line graph too grouping the data too.
(Thank you very much for your help!!!)
Do tou have more ideas ?
Why this code in the options doesn’t work?
hAxis: { showTextEvery: 10, minTextSpacing: 30}
That is JavaScript code that looks like it is being used with a specific library as such is not related to databases. The community doesn’t respond well to mixing a bunch of concerns/questions into one gigantic thread. If you have JavaScript specific questions begin a new thread with a specific, targeted question in that area of expertise. Alternatively you can focus on getting the data correct before worrying about displaying it. I say that because that graph for profit over time looks odd on the y axis.
I think you missed the aggregate function.
SELECT DATE_FORMAT(c_time, “%m-%y”) AS time, SUM(profit) total_profit FROM data GROUP BY time ORDER BY c_time
The graph on previous posts is the right one. the last one shows that monthly profits are summed and this is not what I wanted. My problem seems indeed a javaScript one.as I want to “dilute” the appearance of the data on the x Axis. Thanks P.S. where did you get your database nkoelegde? a book? a course?
oh my $deity, i can’t believe how stupid i am
my sincere apologies, dude
A few books and a little more than a decade of experience with relational databases. Sitepoint offers some good books for learning relational databases.