How to limit MySQL Select Query Results By Today's Date

Question

I have the following MySQL Select Statement in some PHP that works great:

Here is the full dataset: https://www.dropbox.com/s/65dwada48xh45f1/MySQL_HomeSeerDB.sql?dl=1

SELECT CONCAT(LEFT(DATE(EntryDateTime),10),' ',HOUR(EntryDateTime),':00:00') as DateHour, SUM(HSConnectDown) as HSDownCount FROM State GROUP BY DATE(EntryDateTime),HOUR(EntryDateTime)

I want to limit the results to only those where "DateHour" is the same as today's date. I have the code for that using the following:

WHERE DATE('DateHour') = DATE(CONVERT_TZ(CURRENT_TIMESTAMP,'+00:00','-6:00'))

I just do not know how to put together a MySQL Select statement that will filter the results.

Here is an example output with no where field:

DateHour            HSDownCount
------------------- ------------
2018-03-02 13:00:00 1   
2018-03-02 14:00:00 0   
2018-03-02 15:00:00 0   
2018-03-02 16:00:00 0   
2018-03-02 17:00:00 1   
2018-03-02 18:00:00 0   
2018-03-02 19:00:00 2   
2018-03-02 20:00:00 0   
2018-03-02 21:00:00 1   
2018-03-02 22:00:00 0   
2018-03-02 23:00:00 0   
2018-03-03 0:00:00  0   
2018-03-03 1:00:00  1   
2018-03-03 2:00:00  0   
2018-03-03 3:00:00  0   
2018-03-03 4:00:00  0   
2018-03-03 5:00:00  0   
2018-03-03 6:00:00  0   
2018-03-03 7:00:00  0   
2018-03-03 8:00:00  0   
2018-03-03 9:00:00  0   
2018-03-03 10:00:00 1   
2018-03-03 11:00:00 1   
2018-03-03 12:00:00 0   
2018-03-03 13:00:00 0   
2018-03-03 14:00:00 0   
2018-03-03 15:00:00 1   
2018-03-03 16:00:00 0

When I use any kind of WHERE clause for DateHour I get an error... Example:

SELECT CONCAT(LEFT(DATE(EntryDateTime),10),' ',HOUR(EntryDateTime),':00:00') as DateHour, SUM(HSConnectDown) as HSDownCount
  FROM State
WHERE DateHour = '2018-03-03 11:00:00'
 GROUP 
    BY DATE(EntryDateTime),HOUR(EntryDateTime)


MySQL said:
#1054 - Unknown column 'DateHour' in 'where clause'

Can anyone help me with this?

https://stackoverflow.com/questions/14769026/mysql-select-where-timestamp-today/14769048 — nullqube, Mar 03 '18 at 19:40
which type is `DateHour`? From the looks of it is it `varchar()`? — rollstuhlfahrer, Mar 03 '18 at 19:54
Do you have an error when concatening the two parts? Also, we need to know the type of `DateHour`. — Anthony, Mar 03 '18 at 20:28
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Mar 03 '18 at 21:59
To answer some questions: DateHour is a DateTime field formatted 'YYYY-MM-DD HH:MM:SS' — Jean-Marie, Mar 03 '18 at 22:24
When I put the two parts together I get no results. Even when I simplify the WHERE to just be one of the records DateHour fields, I still get no results. — Jean-Marie, Mar 03 '18 at 22:25
I posted the data set that I am working with. Any ideas how to limit by date given the Select Query? — Jean-Marie, Mar 05 '18 at 17:00

score 0 · Answer 1 · edited Mar 03 '18 at 21:59

0

Add it as where clause in the query.

SELECT CONCAT(LEFT(DATE(EntryDateTime),10),' ',HOUR(EntryDateTime),':00:00') as DateHour, SUM(HSConnectDown) as HSDownCount
  FROM State 
WHERE DATE('DateHour') = DATE(CONVERT_TZ(CURRENT_TIMESTAMP,'+00:00','-6:00'))
 GROUP 
    BY DATE(EntryDateTime),HOUR(EntryDateTime)

edited Mar 03 '18 at 21:59

Strawberry

33,750
13
40
57

answered Mar 03 '18 at 20:57

Mohit Satish Pawar

51
2
6

Adding there WHERE clause does not return results, I had tried that with no success. I edited the original question to give more details. – Jean-Marie Mar 03 '18 at 22:33
Can I have your database dump? If it's large provide the only subset of it. – Mohit Satish Pawar Mar 04 '18 at 07:05
How can I post a dump of the DB? – Jean-Marie Mar 04 '18 at 07:36
Upload dump on cloud and share view only link. – Mohit Satish Pawar Mar 04 '18 at 07:44
Mohit, I will do that. I need to learn how to export the data from MySQL first. It is a VERY simply table with just basically two columns that matter. 'EntryDateTime' and 'HSConnectDown'. The EntryDateTime is recorded every minute and has a DateTime in the format of 'YYYY-MM-DD HH;mm:SS' and HSConnectDown has the format of an int with a value of 0 or 1. So my query just counts the sum of HSConnectDown every hour. All I want to be able to do is limit the output of the 1 hour groupings to 1 day and not ALL records. – Jean-Marie Mar 04 '18 at 23:46
Here is the link to the exported DB: https://www.dropbox.com/s/65dwada48xh45f1/MySQL_HomeSeerDB.sql?dl=1 – Jean-Marie Mar 05 '18 at 04:31

score 0 · Accepted Answer · answered Mar 05 '18 at 18:03

CONCAT(LEFT(DATE(EntryDateTime),10),' ',HOUR(EntryDateTime),':00:00')

Wow, there's a lot of wrongness in that single line of SQL. Giving us some clues about your data structure (particularly the types) might be advisable in future.

DateHour is a DateTime field formatted 'YYYY-MM-DD HH:MM:SS'

Not according to what you posted in the question where its an alias for an expression returning a string:

SELECT CONCAT(LEFT(DATE(EntryDateTime),10),' ',HOUR(EntryDateTime),':00:00') as DateHour

I think @rollstuhlfahrer really wanted to ask the datatype of EntryDateTime but was too shocked to read the question more carefully.

Let's pretend for the moment that the attribute type is a DATETIME or TIMESTAMP then

  SELECT CONCAT(DATE_FORMAT(EntryDateTime, '%Y-%m-%d %H'), ':00') AS dayhour
  SUM(HSConnectDown) AS HSDownCount 
  FROM State
  WHERE EntryDateTime BETWEEN
     CONVERT_TZ(
       CONCAT(CURDATE(), ' 00:00:00')
       ,'+00:00','-6:00')
     AND
     CONVERT_TZ(
       CONCAT(CURDATE(), ' 23:59:59')
       ,'+00:00','-6:00')
  GROUP BY CONCAT(DATE_FORMAT(EntryDateTime, '%Y-%m-%d %H'), ':00')

NB if you want to use a DATETIME or TIMESTAMP in place of CURDATE() then use DATE_FORMAT(value, '%Y-%m-%d')

Note the right hand expressions of the predicates in the WHERE clause all evaluate to constant DATETIME values meaning that this query will use an index on EntryDateTime if one is available. Otherwise it will be no quicker than simply:

  SELECT CONCAT(DATE_FORMAT(EntryDateTime, '%Y-%m-%d %H'), ':00') AS dayhour
  SUM(HSConnectDown) AS HSDownCount 
  FROM State
  WHERE DATE(EntryDateTime)=CURDATE()
  GROUP BY CONCAT(DATE_FORMAT(EntryDateTime, '%Y-%m-%d %H'), ':00')

To the questions you asked, here is the data structure of the table: CREATE TABLE `State` ( `EntryDateTime` datetime NOT NULL, `HSConnectDown` int(11) NOT NULL, `BlueIrisDown` int(11) NOT NULL, `InternetDown` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Here is a sample of the data: INSERT INTO `State` (`EntryDateTime`, `HSConnectDown`, `BlueIrisDown`, `InternetDown`) VALUES ('2018-03-02 13:43:14', 0, 0, 0), ('2018-03-02 13:44:16', 0, 0, 0), ('2018-03-02 13:45:16', 0, 0, 0); Does that help? — Jean-Marie, Mar 05 '18 at 18:54
That did it! SELECT CONCAT(DATE_FORMAT(EntryDateTime, '%Y-%m-%d %H'), ':00') AS DateHour, SUM(HSConnectDown) AS HSDownCount FROM State WHERE DATE(EntryDateTime)=CURDATE() GROUP BY CONCAT(DATE_FORMAT(EntryDateTime, '%Y-%m-%d %H'), ':00') — Jean-Marie, Mar 05 '18 at 19:04

How to limit MySQL Select Query Results By Today's Date

2 Answers2