[Resolved] Date/Time rollover problem!

baitubai

My website looks like this:
FROM Date<drop down menu> Time<menu>
TO Date<menu> Time <menu>
<other menus>......... [submit]
If I choose "From 08-13-2003 13:00:00 To 08-14-2003 14:00:00", it will give me the data of both day from 13 to 14 o'clock instead of giving from yesterday's 13 to today's 14:00:00. Could anyone give me an idea to solve this problem?
Any help is appreciated.
Thanks.

Sxooter

We need more info to help. What's the final query look like?

baitubai

looks like:


if ($timeId1<=$timeId2) {
$q="SELECT date,time,host,msg FROM logs WHERE date>='$dateId1' AND 
date<='$dateId2' AND time>='$timeId1' AND time<='$timeId2' AND 
host='$hostId' AND ... ORDER BY date ASC"; 
} else {
$q="SELECT date,time,host,msg FROM logs WHERE date>='$dateId1' AND 
date<='$dateId2' AND time<='$timeId1' AND time>='$timeId2' AND 
host='$hostId' AND... ORDER BY date ASC"; 
}

Sxooter

Splitting the date and time out in seperate fields has made this a messy procedure.

If they were normal date / time types (i.e. combined) you could do this:

select * from test where date between '20030404 12:00 -500' and '20030605 12:00 -500';

Maybe a union?

But I'd fix the schema to store date and time together. It's far easier to process these things when they're merged into one field.

ctimmer

(
date > '$dateId1'
OR
(
date = '$dateId1'
AND
time >= '$timeId1'
)
)
AND
(
date < '$dateId2'
OR
(
date = '$dateId2'
AND
time <= '$timeId2'
)
)

Untested but it should work with your data definition.

baitubai

how to fix the schema of my date and time? any function I can use ?
thanks a lot.

Sxooter

You could likely do something like:

create newtable (datetime timestamp, field2 varchar...) select date || time, field2 from oldtable;

Note that if you're using a database that doesn't support the || as a concatenation operatoer as the SQL spec calls for then you'll have to adjust this. I think in mysql it's the concat() function.

baitubai

Thank both of you very much!
I tried ctimmer's way, and it works.
😃
It still would be GREAT if giving me idea to fix the data/time schema.

baitubai

Thanks Sxooter!
I will try that!

Sxooter

I just thought of the answer!

Use concat and between:

Normal SQL: (i.e. postgresql)

select * 
from t2 
where 
cast((date||' '||time) as timestamp) 
between 
'20020304 12:00:00 -500' 
and 
'20020305 09:15:00 -500';

You can probably do something like it with the concat function in MySQL.

Sxooter

I tried making it work with concat in mysql, but it appears to get confused. Sorry, not sure how to make concat and mysql and cast all work together happily. It looks like it can't handle casting from a function. 🙁

baitubai

🙂 thanks.
It's always good to try and learn!