Solved

DateTime Between on MySQL Not Working

Posted on 2014-09-03
10
475 Views
Last Modified: 2014-09-09
resultsI have a MySQL Query and the date time does not seem to be working does anyone  know why it seems to disreguard the datetime between statements (attached is the image of the results)
 SELECT j.JobID,jde.ToolFailureTime,jde.RigFailureTime ,jue.FailureTime, j.Company, j.Rig, j.RigNumber, j.WellName, jde.RigFailure, jde.ToolFailure, jue.Failure, jue.Notes AS 'UH Notes', jde.Notes AS 'DH Notes'  
FROM `Job` AS j  
JOIN JobDownholeEquipment AS jde ON j.JobID = jde.JobID  
JOIN JobUpholeEquipment AS jue ON j.JobID = jue.JobID  
WHERE 
 jde.RigFailure = TRUE 
OR jde.ToolFailure = TRUE
OR jde.RigFailure = TRUE


AND jde.ToolFailureTime BETWEEN '2014-09-03 12:00:00' AND  '2014-09-03 11:59:59'
OR jde.RigFailureTime BETWEEN '2014-09-03 12:00:00' AND  '2014-09-03 11:59:59'
OR jue.FailureTime BETWEEN '2014-09-03 12:00:00' AND  '2014-09-03 11:59:59'

ORDER BY j.Company
<!----- I also tried-------->
OR jde.ToolFailureTime BETWEEN str_to_date('2014-09-03','%Y-%m-%d') AND str_to_date ( '2014-09-03','%Y-%m-%d')
OR jde.RigFailureTime BETWEEN  str_to_date('2014-09-03','%Y-%m-%d') AND  str_to_date( '2014-09-03','%Y-%m-%d')
OR jue.FailureTime BETWEEN str_to_date('2014-09-03','%Y-%m-%d') AND  str_to_date('2014-09-03','%Y-%m-%d')
ORDER BY j.Company

Open in new window

0
Comment
Question by:r3nder
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I don't have time to create a test case now, but I can give you some theory that might explain it.  First of all, are the columns defined as DATETIME?  That's job #1.

Consider the values in FailureTime.  0001-01-01 00:00:00 does not make sense to me.

Consider the values in ToolFailureTime.  2014-06-09 13:09:48 makes perfect sense.  But a query that looks for data on June 1st-9th with something  like this will not find that row because the time in that column makes the value greater than 2014-06-09, and therefore outside the BETWEEN constraint

... WHERE ToolFailureTime BETWEEN 2014-06-01 AND 2014-06-09

You can find that row by adding one day to get 2014-06-10.  You can also find that row with this:

... WHERE ToolFailureTime BETWEEN 2014-06-01 AND 2014-06-09 23:59:59

You can also find that row with this:

... WHERE ToolFailureTime >= 2014-06-01 AND ToolFailureTime < 2014-06-10

Does that help?
0
 
LVL 6

Author Comment

by:r3nder
Comment Utility
Yes the 3 fields (jde.ToolFailureTime ,jde.RigFailureTime  jue.FailureTime) are datetime
The data 00-00-00 00:00:00 and 0001-01-01 00:00:00 are values the debveloper before me inserted in the database if an Item in the program he wrote was not selected.
The image above was the result after the query was run
in my code it is giving me an error Unable to convert MySQL date/time value to System.DateTime
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Can you please show us the code that generates the error and the exact text of the error message?  Thanks.
0
 
LVL 6

Accepted Solution

by:
r3nder earned 0 total points
Comment Utility
Fixed I did a 3 queries and unioned then all
 query += " SELECT j.JobID, jde.RigFailureTime As 'FailureTime', ";
                query += " CASE jde.RigFailure ";
                query += " WHEN 1 THEN 'RIG' End as 'Tool' ";
                query += " ,j.Company, j.Rig, j.RigNumber, j.WellName,  jde.Notes AS 'Notes' ";
                query += " FROM Job as j ";
                query += " JOIN JobDownholeEquipment AS jde ON j.jobid = jde.jobid ";
                query += " WHERE j.Company <> 'TestCompany' ";
                query += " AND jde.RigFailure = 1 AND jde.RigFailure IS NOT NULL ";
                query += " AND jde.toolFailureTime Between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'  ";
                query += " UNION ";
                query += " SELECT j.JobID, jde.ToolFailureTime As 'FailureTime',  ";
                query += " CASE jde.ToolFailure ";
                query += " WHEN 1 THEN 'TOOL' End as 'Tool' ";
                query += " ,j.Company, j.Rig, j.RigNumber, j.WellName,  jde.Notes AS 'Notes' ";
                query += " FROM Job as j ";
                query += " JOIN JobDownholeEquipment AS jde ON j.jobid = jde.jobid  ";
                query += " WHERE j.Company <> 'TestCompany' ";
                query += " AND jde.ToolFailure = 1 AND jde.ToolFailure IS NOT NULL ";
                query += " AND jde.toolFailureTime Between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'  ";
                query += " UNION ";
                query += " SELECT j.JobID, jue.FailureTime As 'FailureTime',  ";
                query += " CASE jue.Failure ";
                query += " WHEN 1 THEN 'UP' End as 'Tool' ";
                query += " ,j.Company, j.Rig, j.RigNumber, j.WellName,  jue.Notes AS 'Notes' ";
                query += " FROM Job as j ";
                query += " JOIN JobUpholeEquipment AS jue ON j.jobid = jue.jobid  ";
                query += " WHERE j.Company <> 'TestCompany' ";
                query += " AND jue.Failure = 1 AND jue.Failure IS NOT NULL ";
                query += " AND jue.FailureTime Between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'  ";

Open in new window

0
 
LVL 21

Expert Comment

by:Kim Walker
Comment Utility
Did you intend to select between 12 noon and 1 second before 12 noon? 12:00:00 is 12 noon. 11:59:59 is one second before 12 noon. If you're looking for midnight, that'd be 23:59:59.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 6

Author Comment

by:r3nder
Comment Utility
if is converted to 00:00:00 and 23:59:59
in the lines like this one jue.FailureTime Between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "
0
 
LVL 21

Expert Comment

by:Kim Walker
Comment Utility
Your last comment looks incomplete. You never answered my question if you intended to search for a time span of negative one second. Do you still get the error even when you're searching for a significant span of time?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
There are several problems in your where clause.

The first is that you have an unconstrained ORs and what you think is being requested, isn't what is happening.

This is what is happening right now:
SELECT
      *
FROM x
WHERE jde.RigFailure = TRUE
      OR jde.ToolFailure = TRUE
      OR (
         jde.RigFailure = TRUE
         AND jde.ToolFailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
         )
      OR jde.RigFailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
      OR jue.FailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
;

Open in new window

But I believe what you are expecting is:
SELECT
      *
FROM x
WHERE (
         jde.RigFailure = TRUE
      OR jde.ToolFailure = TRUE
      OR jde.RigFailure = TRUE
      )
      AND (
             jde.ToolFailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
          OR jde.RigFailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
          OR jue.FailureTime BETWEEN '2014-09-03 12:00:00' AND '2014-09-03 11:59:59'
          )
;

Open in new window

BUT ALSO you have a major problem with the use of between. It has already been identified that you are using midday (12:00:00) and one second before midday (11:59:59) but you you are currently using these backward you MUST have the earlier date FIRST

see a demonstration of this here: http://sqlfiddle.com/#!9/8464e/2

If you use BETWEEN and get the dates backward you will get NO matches.

e.g. this will NOT work:
select
*
from jobs
where jobstart BETWEEN '2014-09-03 12:00:00' AND  '2014-09-03 11:59:59' -- wrong way around
;
but this WILL WORK:
select
*
from jobs
where jobstart BETWEEN '2014-09-03 11:59:59' AND  '2014-09-03 12:00:00'
;

Then to cap it off: you should NOT USE BETWEEN for date ranges anyway for more on this topic please see: "Beware of Between"
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I'm guessing as to your real intention, but I think this may be the where clause you want:
WHERE (
      jde.RigFailure = TRUE
      AND jue.FailureTime >= '2014-09-03 12:00:00' AND jue.FailureTime < '2014-09-04 00:00:00'
      )
  OR (jde.ToolFailure = TRUE
      AND jde.ToolFailureTime >= '2014-09-03 12:00:00' AND jde.ToolFailureTime < '2014-09-04 00:00:00'
     )
  OR (jde.RigFailure = TRUE
      AND jde.RigFailureTime >= '2014-09-03 12:00:00' AND jde.RigFailureTime < '2014-09-04 00:00:00'
     )
;

Open in new window

It couples each failure type to the related failure date/time, and for the date/time it allows only the half-day from midday up to midnight on 2014-09-03. (Midnight itself is considered to be the start of the next day.)
0
 
LVL 6

Author Closing Comment

by:r3nder
Comment Utility
No one offered a solution
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now