How to see if a date is between two others

[abenstex]

Hello everyone,

i have mysql 4.0.24 database and one table with two datetime rows (from_time and to_time). Now i want to make sure that no new entry is "between" an already existing entry in from_time and to_time. How can i do that?

 

[r937]

use

WHERE [i]from_value[/i] BETWEEN from_time AND to_time
   OR [i]to_value[/i] BETWEEN from_time AND to_time

and if this returns any rows, don't insert

r937.com | rudy.ca

 

[Kozusnik]

I've had to use this in the past...

WHERE field BETWEEN UNIX_TIMESTAMP('2005-09-01 00:00:00') AND UNIX_TIMESTAMP('2005-09-10 23:59:59')

Mark

There are 10 types of people in this world, those who understand binary and those who don't.

 

[r937]

mark, that's only if field is integer

i believe abenstex's column is datetime



r937.com | rudy.ca

 

[audiopro]

Are there functions available for datetime fields?
Is it better to use seperate date and time fields, if manipulation of these fields is required?

Keith

http://www.studiosoft.co.uk

 

[r937]

yes, there are numerous functions available for datetime fields

it is almost always better to use a single datetime field rather than separate date and fields if you need a time as well as a date

r937.com | rudy.ca

 

[audiopro]

I have been googling for info on thos functions with no luck, can you point me in the right direction please?

Keith

http://www.studiosoft.co.uk

 

[TonyGroves]

The MySQL manual tells everything there is to know about MySQL functions. You can browse it in HTML form at the MySQL site, or download the PDF version.

 

[audiopro]

The MySQL manual tells everything there is to know about MySQL functions.

I am sure it does and I understand the format and have populated various fields with the correct time and date using low level formatting but I have yet to discover any specific methods which deal with date / time manipulation.


Keith

http://www.studiosoft.co.uk

 

[r937]

have yet to discover any specific methods which deal with date / time manipulation

well, there are the DAYOFMONTH, DATE_ADD, FROM_DAYS, UNIX_TIMESTAMP, SEC_TO_TIME functions, and many more


what kinda manipulation you lookin' for?

r937.com | rudy.ca

 

[audiopro]

I am just investigating what is available. In the past I have hard coded most of these functions but feel reluctant to re-invent the wheel.
I require functions such as Date_Last_Edited, Bid_Hours_Remaining and other such self made functions. I have these functions already written in PERL but was checking out what was available in MySQL.

Keith

http://www.studiosoft.co.uk

 

[r937]

okay, well, have fun in perl, 'cause there's no equivalent in mysql

date_last_edited can be done with a simple timestamp column, but it is updated automatically only if it's the first timestamp column in the table

bid_hours_remaining is application dependent

r937.com | rudy.ca

 

[audiopro]

OK Thanks for that.
Writing the functions is not a problem but I didn't want to do it if there were built in functions.
I have the date last edited available already but will have to do the time remaining one.

Keith

http://www.studiosoft.co.uk