SQL query help

[Platypus]

Hey guys,

Here's the deal. This query works well for most applications but it fails on a specific instance.

As you can see, trying to compare 22:00 (10pm) and 02:00(2am) fails because of the way the query is written. I am not up to snuff on SQL, I can build basic queries so I was wondering if anyone had a suggestion..

And I thought of concatenating the date and time but there is a wildcard search for time and date and that would break it. I know there is someone out there who's run into this little quirk before, any help would be appreciated.

 

[Platypus]

Sorry forgot to add,

My inputs where the two dates and 22:00 and 02:00

(I inherited this code, trying to make it work) It somehow is dropping the time stamps?

 

[MrChad]

What DBMS?

 

[Platypus]

Originally posted by: MrChad
What DBMS?

Thanks for looking,

I am using MySQL 4.0.21. The complicated bit is that date and time are stored in seperate fields in the DB, not as one.

 

[agnitrate]

Can you just select all times from the 5th first that are later than 22 and join that with all the times from the 6th earlier than 02?

 

[Platypus]

Um, you're kind of jumping ahead of my learning curve here Brian

I can pretty much do whatever I need to, I just dont know how to make that work.

 

[MrChad]

Originally posted by: agnitrate
Can you just select all times from the 5th first that are later than 22 and join that with all the times from the 6th earlier than 02?

That's what I was going to suggest.

 

[agnitrate]

Originally posted by: CorporateRecreation
Um, you're kind of jumping ahead of my learning curve here Brian

I can pretty much do whatever I need to, I just dont know how to make that work.

I'm no SQL genious either and I'm not sure what the HOUR(time) thing will do, but perhaps something similar to this might work?

Maybe that can get you headed in the right direction?

[EDIT] make that 'date = ...' below, I forgot the = signs [/EDIT]

 

[MrChad]

Incidentally, is the "host like '%%'" criteria actually doing anything?

 

[Platypus]

Originally posted by: MrChad

Originally posted by: agnitrate
Can you just select all times from the 5th first that are later than 22 and join that with all the times from the 6th earlier than 02?

That's what I was going to suggest.

Can you explain exactly what you did here?

Also, I don't know about the host command, I assume it's there for a reason though I could be wrong.

 

[MrChad]

From your SQL it looks like date and time are stored in separate columns (a bit odd, but ok).

The original query looked like it wanted records between 10 PM on 6/4 and 2 AM on 6/5. SQL WHERE clauses are just conditional logic, so it's just a bit of translation. Either the item meets the first set of criteria (date = '06-04-2005 and HOUR(time) > 22) or it meets the second set of criteria (date = '06-05-2005' and HOUR(time) < 02). If either condition is met, the row is included in the result set.

 

[Platypus]

Originally posted by: MrChad
From your SQL it looks like date and time are stored in separate columns (a bit odd, but ok).

The original query looked like it wanted records between 10 PM on 6/4 and 2 AM on 6/5. SQL WHERE clauses are just conditional logic, so it's just a bit of translation. Either the item meets the first set of criteria (date = '06-04-2005 and HOUR(time) > 22) or it meets the second set of criteria (date = '06-05-2005' and HOUR(time) < 02). If either condition is met, the row is included in the result set.

I'll audition your solution and get back to you. Thanks a lot for your help!

 

[lansalot]

host like '%%' - doesn't do anything. Except possibly force a full table scan on your table...

 

[statik213]

The best thing IMO is to make a `real` DATETIME value out of your data. What format are the date and time in?
If you nave the date portion in this format: YYYY-MM-DD and the time portion in this format: HH:MM:SS
Do this:

SELECT *
FROM table
WHERE STR_TO_DATE(CONCAT(date_portion_field, ' ', time_portion_field), '%Y-%m-%d %H:%i:%s') BETWEEN fromDate AND toDate;

If you have a more 'exotic' format see below and format the STR to a proper DATE. I would add a new DATETIME field and save the combined date/time value and drop the old date time fields.

G'luck!

from: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

 

[statik213]

Originally posted by: CorporateRecreation
Hey guys,

Here's the deal. This query works well for most applications but it fails on a specific instance.

As you can see, trying to compare 22:00 (10pm) and 02:00(2am) fails because of the way the query is written. I am not up to snuff on SQL, I can build basic queries so I was wondering if anyone had a suggestion..

And I thought of concatenating the date and time but there is a wildcard search for time and date and that would break it. I know there is someone out there who's run into this little quirk before, any help would be appreciated.

... if you did have a unified DATETIME field things would be a lot simpler for you, you can always split the field into a DATE and TIME portion with the DATE() and TIME() functions for your other queries to work.