Web Analytics Made Easy -
StatCounter Integer Ranges - CodingForum

Announcement

Collapse
No announcement yet.

Integer Ranges

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Integer Ranges

    Usually I would do this in code but I've seen you SQL gurus do things with it that I never even imaged possible (btw where do you learn all that stuff) so I thought I'd ask if this is possible...

    If I have a database full of entries with StartDate and EndDate (values as unix timestamps ie integers) is there any way in SQL to determine whether a given range of values eg user enters a new start date and end date, overlaps or is between any of the values in the database?
    http://www.hazelryan.co.uk

  • #2
    First, I would strongly suggest storing dates as one of mysql's date types (DATE,DATETIME,TIMESTAMP) instead of as a unix timestamp. The database has a number of useful date manipulation/comparison functions that you're losing with timestamps, and you can always use UNIX_TIMESTAMP() to convert it if you need to.

    That aside, you should be able to use any of mysql's comparison operators on your timestamps for your check. I think something like this'll work (for both timestamps or mysql date types):

    Code:
    SELECT
     foo
    FROM
     table
    WHERE
     startdate BETWEEN userstart AND userend
    OR
     enddate BETWEEN userstart ANd userend
    OR
     (startdate <= userstart AND enddate >= userend)

    Comment


    • #3
      That appears to be working Thanks!
      http://www.hazelryan.co.uk

      Comment

      Working...
      X