Jump to content
Sign in to follow this  
hokletrain

Dates range of 15 minutes

Recommended Posts

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 by hokletrain

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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  :happy-04:

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

 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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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:

 

938f711c7c.png

 

 

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.