Between two Date/Time
Hi Experts
I have a MySQL table with the following structure:
id auto_inc
date date
time time
I'm wondering, how can I SELECT all records that fall under a specific date/time period?
For example, If i have between Jan 7 at 7:00pm to Jan 9 @ 7pm, I need all records that fall under:
Any help will be greatly appreciated.
I have a MySQL table with the following structure:
id auto_inc
date date
time time
I'm wondering, how can I SELECT all records that fall under a specific date/time period?
For example, If i have between Jan 7 at 7:00pm to Jan 9 @ 7pm, I need all records that fall under:
Jan 7 7:00pm to 11:59pm
Jan 8 12:00am to 11:59pm
Jan 7 12:00am to 7:00pm
Any help will be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dave Baldwin
More info: https://dev.mysql.com/doc/refman/5.0/en/datetime.html
ASKER
Can I do a concat, then use between?
it is often advised to avoid using between unless you take into account the meaning a between 1 and 5 means a can be 1 but can not be 5.
Yes, you can do concat, but I think you will lose any indexing advantages
try and use the execution plan comparison.
What is the issue of using where (date='one' and ( time )) or (date and (time))
Yes, you can do concat, but I think you will lose any indexing advantages
try and use the execution plan comparison.
What is the issue of using where (date='one' and ( time )) or (date and (time))
Can I do a concat, then use between?No, just don't. 'concat' will not properly support the MySQL date/time arithmetic. Or as Arnold points out, indexing won't work properly unless you make it a single datetime field. A proper index on a datetime field will make the comparison very easy because the arithmetic is already done to put them in order.
ASKER
Thank you