Sectah Sectah - 7 days ago 6
SQL Question

LIKE and BETWEEN query SQL with Haxe

I'm trying to display scores from a leaderboard within the week.

Currently I query certain daily scores with the following:

public static function displayDay(day:String)
{
queryLeaderboard("SELECT * FROM gamedata WHERE ts LIKE '%-" + day + "%' ORDER by scoreDifference DESC LIMIT 10");
}


Which will display the scores from my timestamp which is formatted as 2016-11-25 20:04:47 hence finding stuff after a "-" with anything afterwards. I have this working nicely for yearly, monthly and daily scores.

However, I'm trying to find weekly scores.

I currently have the following:

public static function displayWeek()
{
var dateNow:Date = Date.now();

Lib.print(dateNow);
Lib.print("<br>");

Lib.print(dateNow.getDate());
Lib.print("<br>");

var weekAgo:Int = dateNow.getDate() - 7;
Lib.print(weekAgo);
Lib.print("<br>");

var query:String = "SELECT * FROM gamedata WHERE ts BETWEEN LIKE '%-" + weekAgo + "%' AND LIKE '%-" + dateNow.getDate() + "%' ORDER by scoreDifference DESC LIMIT 10";
Lib.print(query);
queryLeaderboard(query);
}


The query is as follows:

SELECT * FROM gamedata WHERE ts BETWEEN LIKE '%-19%' AND LIKE '%-26%' ORDER by scoreDifference DESC LIMIT 10


And from what I understand (limited) of SQL queries, it doesn't like trying to find values between each other that are strings? But I believe the format is SDateTime from Haxe.

How do I go about doing this?

class GameData extends Object
{
public var id:SId;
public var username:SString<32>;
public var countryA2:SString<32>;
public var scoreFor:SInt;
public var scoreAgainst:SInt;
public var scoreDifference:SInt;
public var ts:SDateTime;
}

Answer

Try to use such SQL:

...
var query:String = "SELECT * FROM gamedata WHERE ts 
BETWEEN '" + Date.fromTime(dateNow.getTime()-7*24*3600*1000).toString() + "' 
AND '" + dateNow.toString() + "' 
ORDER by scoreDifference DESC LIMIT 10";
Lib.print(query);
queryLeaderboard(query);
...

In MySQL if you use BETWEEN function you have to pass at least string representation of data objects. If you do so the LIKE is no longer needed. Also if you take a look at getDate() - it returns Int so if you substract 7 from monday (1) you get -6. Instead I suggest to substract 7*24*3600 seconds from current time converted to timestamp representation.

Take a look at Haxe documentation API.HAXE.ORG/DATE

I hope this can help.

Comments