Query capturing timestamp 0000-00-00 00:00:00

Question

How do I get this query to not pick up timestamps with the default DatePercentUpdate = 0000-00-00 00:00:00? I thought I had it by using 0, but it captures the 0000-00-00 00:00:00's.

$stmtSummary = $mysqliSummary
    ->prepare("SELECT n.nodeID, n.percentRedYellow, n.count, c.Loopback_ip, c.city 
               FROM CATV.CableData  c 
               INNER JOIN CATV.node n ON n.nodeID = c.Node_ID   
               INNER JOIN CATV.CM cm ON cm.LoopbackIP=c.Loopback_ip
               WHERE c.LocationID IS NOT NULL 
                 AND c.Status_Update_Time <= NOW() 
                 AND c.Status_Update_Time >=(NOW() - INTERVAL 2 DAY) 
                 AND c.Status_Update_Time>0
                 AND n.DatePercentUpdate <= NOW() 
                 AND n.DatePercentUpdate >=(NOW() - INTERVAL 2 DAY) 
                 AND n.DatePercentUpdate>0
                 AND length(c.Node_ID)>0
               Group BY c.city, n.nodeID");

Added: I tried searching online to use 0000-00-00 00:00:00 in the query, but didn't find anything with that in a filter.

`WHERE date_field > "0000-00-00 00:00:00"` ? OR `WHERE date_field != "0000-00-00 00:00:00"`? — treyBake, Oct 22 '18 at 14:01
I haven't digged into your query but [there must be something else going on](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=649fb81c4e5f4e54c0b7b35112498fc4). — Álvaro González, Oct 22 '18 at 14:12
Comparing Status_Update_Time with both >= and <= should be sufficient. You're probably looking at the wrong place. — Salman A, Oct 22 '18 at 14:15
You're (ab)using partial `GROUP BY` and probably getting values inside n.count, c.Loopback_ip, c.city and getting confused. — Salman A, Oct 22 '18 at 14:21
@ThisGuyHasTwoThumbs your suggestion worked, except I needed single quotes. Thanks a lot!!! I wasn't sure how to format using 0000-00-00 00:00:00, and it wasn't coming up in my internet searches! — Michele, Oct 22 '18 at 14:26
Sure, go ahead and post an answer so I can accept it! Thanks @ThisGuyHasTwoThumbs! — Michele, Oct 23 '18 at 16:41
@ThisGuyHasTwoThumbs - yes! I commented above Oct 22. I just needed to change the quotes! Thanks! — Michele, Nov 05 '18 at 18:13
@Michele fully aware, more if my answer was the right solution, Mark as accepted :) if not let me know and I'll remove :) — treyBake, Nov 05 '18 at 19:20

score 0 · Accepted Answer · answered Oct 24 '18 at 07:16

0

You can simply use the > (greater than) comparison operator or the != (doesn't equal) comparison operator like so

WHERE `date_field` != '0000-00-00 00:00:00'

The example will get all rows where your date doesn't equal that value.

answered Oct 24 '18 at 07:16

treyBake

6,440
6
26
57

score 0 · Answer 2 · answered Oct 20 '20 at 08:55

Capture Query where timestamp is 0000-00-00 00:00:00

To select rows select * from table_name where your_column = 0;

To update rows UPDATE table_name SET column_name = new_value where your_column = 0;

This will fix

mysql incorrect datetime value: '0000-00-00 00:00:00' mysql error

Query capturing timestamp 0000-00-00 00:00:00

2 Answers2

Linked