MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Speed Up Queries / Question No: 20

How to get all dates between two dates?

I need to create an inline view that will give me all dates between two specified. Here is the question that I need to answer:

This basic query gets me the last 6 hours of history table entries for an item, grouped into minutes (if an item has a delay of 5 seconds, it takes the max out of each 11 entries per minutes).

SET @iid=231;
SET @hid=1;
SELECT from_unixtime(t.clock), 
DATE_FORMAT(from_unixtime(t.clock),'%j %h:%i %p') as mins
,
MAX(t.value) as value_max from history t
WHERE t.itemid = (
SELECT itemid from items
WHERE itemid = @iid and hostid = @hid
)
AND DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR) <=
from_unixtime(t.clock) GROUP BY mins ORDER BY t.clock;

It’ll fetch 360 entries (360 minutes in 6 hours) that looks like this:

from_unixtime(t.clock) mins value_max

2/24/2007 05:56:12 055 05:56 AM 0.29
2/24/2007 05:57:02 055 05:57 AM 0.2
2/24/2007 05:58:02 055 05:58 AM 0.23
2/24/2007 06:01:03 055 06:01 AM 0.02
2/24/2007 06:02:02 055 06:02 AM 0.08

The format you see for mins is DAY_OF_YEAR HOUR:MIN AM/PM. DAY_OF_YEAR is unique, as in 55th day of this year.

So if I can JOIN this table with another table/view/sub query that has EVERY MINUTE of the last 6 hours in a similarly formatted column like ‘mins’, we should be able to spot the missing 2 minutes.

from_unixtime(t.clock) mins value_max
2/24/2007 05:56:12 055 05:56 AM 0.29
2/24/2007 05:57:02 055 05:57 AM 0.2
2/24/2007 05:58:02 055 05:58 AM 0.23
2/24/2007 05:59:02 055 05:59 AM NULL <<- from the join
2/24/2007 06:00:02 055 06:00 AM NULL <<- from the join
2/24/2007 06:01:03 055 06:01 AM 0.02
2/24/2007 06:02:02 055 06:02 AM 0.08

Answer No: 20

A subqueryless (ie join) version of your query will likely be faster:
SELECT from_unixtime(t.clock),
DATE_FORMAT(from_unixtime(t.clock),'%j %h:%i %p') AS mins,
MAX(t.value) AS value_max FROM history t
JOIN items i USING (itemid) WHERE t.itemid = @iid
AND i.hostid = @hid AND
DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6 HOUR) <= from_unixtime(t.clock)
GROUP BY mins ORDER BY t.clock;
For your second question, it might be easier to build a calendar table with one row for each possible & relevant datetime value, and left join that calendar table to your history table.

Related MySQL FAQs to the Above FAQ

What-is-better-query-to-update-a-table What is better query to update a table?

How-to-speed-up--multi-table-based-delete-query How to speed up multi table based delete query?

Why-my-update-query-works-slow-in-MySQL Why my update query works slow in MySQL?

What-is-query-cache-in-MySQL What is query cache in MySQL?

What-is-table-cache-in-MySQL What is table cache in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.