Dates range of 15 minutes

[hokletrain]

Hey guys, the data in my database gets inputted at dates within the hour, I'm trying to round each date to the nearest 15 minute interval. I have tried to use SEC_TO_TIME but wasn't working. I'm trying to calculate data for every 15 minutes. 

 

Example dates: 2017-09-05 09:01 , 2017-09-05 09:05  , 2017-09-05 09:17 , 2017-09-05 09:24

 

First two dates should 'round' to 9:00 and the second set should 'round' to 9:30. 

 

Current Query: 

"SELECT d.$sensor,b.$sensor1,c.$sensor2,d.date 
FROM test d, test b, test c WHERE d.$sensor 
IN (SELECT $sensor FROM test WHERE sn = $rtu) AND b.$sensor1
IN (SELECT $sensor1 FROM test WHERE sn = $rtu1) AND c.$sensor2 
IN (SELECT $sensor2 FROM test WHERE sn = $rtu2) AND d.date = b.date AND d.date = c.date 
order by d.date"

Any help would be great , thanks. 

Edited December 20, 2017 by hokletrain

[requinix]

d.date is a DATETIME, right? Not a string?

[hokletrain]

d.date is a varchar , which has been formatted!

Edited December 20, 2017 by hokletrain

[requinix]

Use a DATETIME. You're mostly there already. No, being "formatted" is not good enough. If you don't want to display seconds then use DATE_FORMAT() to get the formatted version you want (or do it in PHP).

 

Question: would it be easier to fix the times when adding the data? I mean, if "09:01" isn't what the value should be then it shouldn't be that value.

[hokletrain]

I'm using PHP to insert JSON data into the MySQL table, which gives me a date I'm just using the varchar to store it in the PHP formatted datetime. I can still use every MySQL date function with the varchar though aha? I pretty much just want to get records of data that are inside the period of 15 minutes, hence why I'm trying to round it. In my case could i use FLOOR?   e.g SEC_TO_TIME(FLOOR((TIME_TO_SEC(CURTIME())+450)/900)*900).

[hokletrain]

Use a DATETIME. You're mostly there already. No, being "formatted" is not good enough. If you don't want to display seconds then use DATE_FORMAT() to get the formatted version you want (or do it in PHP).

 

Question: would it be easier to fix the times when adding the data? I mean, if "09:01" isn't what the value should be then it shouldn't be that value.

 

I also still want to keep the original date's which is why i haven't rounded in my insert script. I only want to do this rounding part for calculations 

[requinix]

It's not about whether it works or not. It's about whether you're using the right tool for the job. Like, you could use a pair of pliers to drive a nail, but using a hammer is definitely better.

 

Anyway, keeping the original timestamp is fine. Since you care about the minutes and not seconds, using TIME_TO_SEC/SEC_TO_TIME is probably fine. Then pair that with DATE_FORMAT.

DATE_FORMAT(SEC_TO_TIME(ROUND(TIME_TO_SEC(d.date) / 900) * 900), "%Y-%m-%d %H:%i")

Note that MySQL has a ROUND() function.

[hokletrain]

 I'm kind of confused in which part of the query i would apply the date_format part to. Would i select it at the start e.g SELECT d.$sensor,b.$sensor1,c.$sensor2,DATE_FORMAT(SEC_TO_TIME(ROUND(TIME_TO_SEC(d.date) / 900) * 900), "%Y-%m-%d %H:%i") as dd.date?

 

Or could i just use it at the end as a group by DATE_FORMAT(SEC_TO_TIME(ROUND(TIME_TO_SEC(d.date) / 900) * 900), "%Y-%m-%d %H:%i") ?

 

And even then how could i apply this to the other date fields like b.date and c.date? 

 

Sorry I'm quite new to this particular MySQL , thanks for all the help Req.

[requinix]

It's a function call, meaning you can use it in places and ways that work for function calls.

 

You aren't grouping by anything so I don't know why you need that. You don't need it for sorting because the order won't change by adding this in (think about it).

 

You do need it if you want the query to return a different result. Currently

"SELECT d.$sensor,b.$sensor1,c.$sensor2,d.date 
FROM test d, test b, test c WHERE d.$sensor 
IN (SELECT $sensor FROM test WHERE sn = $rtu) AND b.$sensor1
IN (SELECT $sensor1 FROM test WHERE sn = $rtu1) AND c.$sensor2 
IN (SELECT $sensor2 FROM test WHERE sn = $rtu2) AND d.date = b.date AND d.date = c.date 
order by d.date"

you're returning d.date, so obviously you need to DATE_FORMAT that. If b.date and c.date don't match up then you have to deal with that. However reformatting the dates is the wrong way to do that. It puts a lot of strain on MySQL having to calculate so much stuff every time.

 

You're using PHP to insert the data, which means you can process the data any way you want. Here's my suggestion:

 

Set up a new table that tracks when sensor data is being recorded. It has an ID, a date for when the data was received, and a rounded date for when you want the data to correspond to.

When inserting, you create a new record in there first, grab the new ID, and use that as a foreign key for the sensor data. Then, instead of joining test against itself based on the date, you join based on the ID.

 

The date formatting will be irrelevant because your PHP does that when it inserts that first new record, and the date mismatches won't matter because you're joining by an identifier instead of a value.

[hokletrain]

Thanks for all the help again, Req. The issue is that the ID/SerialNumber is dynamic and changes a lot. I looked at your previous answer got kind of confused so just stuck with your first suggestion. Processed the date through PHP rounded to nearest 15 minutes and stored as proper date. Code works almost perfectly with what i have above. Now that the dates are rounded properly , I get a lot of looped results is that because I'm using date = date and so on?

 

Example query: 

SELECT d.RH,b.AT,c.WSAV,d.date FROM test d, test b, test c WHERE d.RH
IN (SELECT RH FROM test WHERE sn = 5165654) AND b.AT 
IN (SELECT AT FROM test WHERE sn = 5165655) AND c.WSAV
IN (SELECT WSAV FROM test WHERE sn = 5165656) AND d.date = b.date AND d.date = c.date ORDER BY d.date;

Output:

 

 

 

So what i have done is LIMIT by 1, which gives me the result i need. Just curious why it returns so many results?

[gizmola]

You are selecting from the same table 3x