0

I'm expanding a booking system.

I have a set of records whom only should show in the results when the START_DATE_VALUE is an X amount of time in the future. Each of these records carries this value (number and unit, so number: 7 and unit 'DAY' for example).

If the INTERVAL would be a fixed value, the following would be the solution.

unix_timestamp('START_DATE_VALUE') > unix_timestamp(NOW() + INTERVAL 7 DAY)

I would like to set have the 7 and DAY value from the items record, like so:

unix_timestamp('FROM_DATE_VALUE') > unix_timestamp(NOW() + INTERVAL items.available_from_number items.available_from_unit)

Simple version of the query to check availability of items:

SELECT * FROM items
WHERE items.id NOT IN (SELECT booking_items.item_id FROM bookings
    INNER JOIN booking_items ON bookings.id = booking_items.booking_id
    WHERE UNIX_TIMESTAMP(bookings.end_date) >= UNIX_TIMESTAMP('START_DATE_VALUE')
    AND UNIX_TIMESTAMP(bookings.start_date) <= UNIX_TIMESTAMP('END_DATE_VALUE'));

I've been playing around for a while and I can't seem to figure it out. Is this at all possible with mysql?

Thank you for your time.

Wijnand
  • 1,192
  • 4
  • 16
  • 28
  • @SebastianBrosch It will create a scary looking query, but it will help me solve the issue, thanks! After that I will need to translate it to Laraval Eloquent ORM. That'll be a great next challenge :) – Wijnand Jul 31 '20 at 12:29
  • 1
    Number may be inserted directly as `table.column`. Unit cannot - CASE is the simplest solution. `unix_timestamp('FROM_DATE_VALUE') > unix_timestamp(NOW() + CASE items.available_from_unit WHEN 'YEAR' THEN INTERVAL items.available_from_number YEAR WHEN 'MONTH' THEN INTERVAL items.available_from_number MONTH WHEN 'WEEK' THEN INTERVAL items.available_from_number WEEK WHEN 'DAY' THEN INTERVAL items.available_from_number DAY ELSE 0 END)` – Akina Jul 31 '20 at 12:37

0 Answers0