DATE_ADD with dynamic INTERVAL

shawshankit · #1 (**permalink**) 11-11-11, 03:34

hi everybody!
i would like to know how to make this query work.
INTERVAL needs to be variable (1 MONTH, 1 DAY, etc..)

Code:

SELECT (DATE_ADD(FROM_UNIXTIME(field_date), INTERVAL field_interval)) AS next_date FROM table WHERE id=1

also tried using CONCAT(INTERVAL,' ',table.field_interval), but unsuccessfully.

thanks in advance!

r937 · #2 (**permalink**) 11-11-11, 08:28

you may have to find a way around this, because you cannot have an sql keyword in a variable

try something like this --

Code:

SELECT FROM_UNIXTIME(field_date) + INTERVAL 
         CASE WHEN field_interval = '1 DAY'
              THEN 1
              WHEN field_interval = '1 WEEK'
              THEN 7
              WHEN field_interval = '1 MONTH'
              THEN 31
           END DAY AS next_date 
  FROM daTable 
 WHERE id = 1

here your variables are translated into a number of days to add

another approach is like this --

Code:

SELECT CASE WHEN field_interval = '1 DAY'
            THEN FROM_UNIXTIME(field_date) + INTERVAL 1 DAY
            WHEN field_interval = '1 WEEK'
            THEN FROM_UNIXTIME(field_date) + INTERVAL 1 WEEK
            WHEN field_interval = '1 MONTH'
            THEN FROM_UNIXTIME(field_date) + INTERVAL 1 MONTH
         END AS next_date 
  FROM daTable 
 WHERE id = 1

shawshankit · #3 (**permalink**) 11-11-11, 09:31

Thanks a lot buddy! I've also found this workaround. Not so handy, but ok.

SELECT
CASE SUBSTRING_INDEX(ripetizione, ' ', -1)
WHEN 'DAY' THEN DATE_ADD(FROM_UNIXTIME(data_start), INTERVAL SUBSTRING_INDEX(ripetizione, ' ', 1) DAY)
WHEN 'WEEK' THEN DATE_ADD(FROM_UNIXTIME(data_start), INTERVAL SUBSTRING_INDEX(ripetizione, ' ', 1) WEEK)
WHEN 'MONTH' THEN DATE_ADD(FROM_UNIXTIME(data_start), INTERVAL SUBSTRING_INDEX(ripetizione, ' ', 1) MONTH)
WHEN 'YEAR' THEN DATE_ADD(FROM_UNIXTIME(data_start), INTERVAL SUBSTRING_INDEX(ripetizione, ' ', 1) YEAR)
ELSE NULL
END
AS next_date FROM `table` WHERE id=1

*notes*
data_start = field with date, stored as unix_timestamp
ripetizione = field with interval (may be stored as 1 DAY, 3 MONTH, 5 YEAR, etc)

hope this help!

Internet Services

Web Development

Digital Marketing

Consumer Tech