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:

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.
APD TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Usually, in MySQL it is best to store dates using unix_timestamp, but that is me.

Select * from table where (date='01/07/2015' and time>='19:00' and time <='00:00') or

Your issue is that the way the data is stored is unclear.
You could use date/time functions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
It would be much more efficient to use a single DATETIME field so MySQL could use it's date/time arithmetic to do the comparisons.  Then I would use a query like...
SELECT * FROM mytable WHERE thedate >= '2015-01-07 19:00:00' AND thedate <= '2015-01-07 23:59:59';

Open in new window

Date and Time are reserved words and I would never use them for column names.
Note that the Date/Time format I used is properly 'sortable' even in a text field.
Dave BaldwinFixer of ProblemsCommented:
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

APD TorontoSoftware DeveloperAuthor Commented:
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))
Dave BaldwinFixer of ProblemsCommented:
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.
APD TorontoSoftware DeveloperAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.