Hw to get results by weekday?

Hello,
I have a table of trading results
Here is the date format : 2017-08-22 20:47:14

I want to be able to get results in various options for example, results where trades were executed on monday or froday or mondays. and tuesdays or mondays wednesdays and thursdays
In other words I want to choose a specific weekday or specific weekdays
I tried "SELECT item, SUM(profit) AS profit FROM data WHERE DATEPART(WEEKDAY, Date) IN (2, 3, 4) GROUP BY item "

It doesn’t work.
How do I code the query right?

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

computer froze? error message? wrong results? server blew up?

Here I the query code:

"SELECT ticket, o_time, type, size, item, o_price, s_l, t_p, c_time, c_price, profit
			FROM data  WHERE DATE_FORMAT(c_time, '%H') >= 10:00 AND  DATE_FORMAT(c_time, '%H') <= 22:00  ORDER BY c_time"

Here is the message:
Warning: Invalid argument supplied for foreach() in C:\wamp\www\forex_analyzer - test\forex_all.php on line 417

The foreach() uses the query data.

I can’t find a source with explanations
I want to get results where:L

  1. c_time is a specific year
  2. c_time is a month (e.g. Results where the month is january or July and december
  3. specific day q days (e.g. where the day is all Tuesdays or all Mondays and Wednesdays
  4. Time of dat : AM or PM
  5. Range of hours
  6. Combination : e.g. when the month is February day is tuesday, Time is PM

gets the marshmellows

This will almost certainly mean your query failed. Use your database object’s error reporting methods to get the message that the database engine sent to PHP.

That’s a PHP error. What does the query return when you run it outside of PHP?

SQL query:

SELECT ticket, o_time, type, size, item, o_price, s_l, t_p, c_time, c_price, profit
			FROM data  WHERE DATE_FORMAT(c_time, '%H') >= 10:00 AND  DATE_FORMAT(c_time, '%H') <= 22:00  ORDER BY c_time
            
 LIMIT 0, 25 

MySQL said:

`#1064 - Erreur de syntaxe près de ':00 AND DATE_FORMAT(c_time, ‘%H’) <= 22:00 ORDER BY c_time

LIMIT’ Ã la ligne 2`

DATE_FORMAT(c_time, '%H') >= 10:00

Result of this DATE_FORMAT has type integer. So, correct is…

DATE_FORMAT(c_time, '%H') >= 10
1 Like

“syntax error” means kind of like “it wasn’t said the way it needs to be said”

The “near” isn’t as good as a “this is it” but it does point in the right direction. i.e.

WHERE DATE_FORMAT(c_time, '%H') >= 10:00 
AND  DATE_FORMAT(c_time, '%H') <= 22:00 

MySQL wants string values inside quote marks and numeric values not inside quote marks. Date values have digits, but are they numeric? I think not. Looking at a few examples in the documentation suggests they should be treated as strings not numerics.

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_adddate

What is DATE_FORMAT with “%H” ?

EDIT: ninja’d :wink:

Well, actually it does point to a “this is it”, it’s just pointing at “this is the point where it went to ****, look here and immediately before it.”

In his case, it says “near” ':00 ....' and sure enough, the error occurs at/begins with the :.

1 Like