Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: DATE_ADD with dynamic INTERVAL

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    2
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •