Saving duplicate values ​​in a MySQL database

Question

I have been boggling my head on the table over the past few days, and therefore I am addressing you, the stack overflow.

The software I'm working on has time sensitive data. The usual solution for this is effective and expiration date.

EFF_DT      XPIR_DT     VALUE
2000-05-01  2000-10-31  100
2000-11-01  (null)      90

That would be easy. Unfortunately, we need data that is repeated on an annual basis arbitrarily far into the future. In other words, every May 1 (starting in 2000) we may need an effective value of 100, and every November 1 we can change it to 90.

This may be true for a long time (> 50 years), and therefore I do not want to just create a hundred records. I do not want to do this:

EFF_DT      XPIR_DT     VALUE
2000-05-01  2000-10-31  100
2000-11-01  2001-04-30  90
2001-05-01  2001-10-31  100
2001-11-01  2002-04-30  90
2002-05-01  2002-10-31  100
2002-11-01  2003-04-30  90
...
2049-05-01  2049-10-31  100
2049-11-01  2050-04-30  90
2050-05-01  2050-10-31  100
2050-11-01  2051-04-30  90

. 2000 ( -), :

EFF_DT      XPIR_DT     REPEATABLE  VALUE
1995-01-01  2000-04-30  false       85
2000-05-01  2010-04-30  true        100
2000-11-01  2010-10-31  true        90
2010-05-01  (null)      true        120
2010-11-01  (null)      true        115

( ), , , , .

: , 2010-03-09?

, - ( ), , .

EFF_DT      MOST_RECENT XPIR_DT     VALUE
2000-05-01  2009-05-01  2010-04-30  100
2000-11-01  2009-11-01  2010-10-31  90

90, 2009-11-01 2009-05-01.

., , 2007-06-20:

EFF_DT      MOST_RECENT XPIR_DT     VALUE
2000-05-01  2007-05-01  2010-04-30  100
2000-11-01  2006-11-01  2010-10-31  90

100, 2007-05-01 2006-11-01.

MySQL, MOST_RECENT?

- ?

- Java, . !

Answer 1

, "" - "2007-06-20".

, - ( , , - , ):

select * from (
  select * from mytable 
  where 
    repeatable = false
    and 
    EFF_DT <= '2007-06-20' < XPIR_DT
  union all
  select * from mytable
  where
    repeatable = true
    and EFF_DT <= str_to_date(concat("2007", "-", month(EFF_DT), "-", day(EFF_DT)), "%Y-%m-%d") < XPIR_DT
)
order by EFF_DT desc limit 1

Answer 2

, , MySQL , , - , .

, , / , ( ) 1: (.. "event_id" , ). , , , ( ).

start_date/end_date/value , . , WHERE @somedate BETWEEN start_date AND end_date, .

, INSERT UPDATE , " " ​​ , MySQL. 2 , int int, . , , - ( 50 5? cron? ..), MySQL .

: ? ?

Answer 3

, EFF_DT . , , , .

select EFF_DT  form  date_table where 1 order by EFF_DT desc limit 1

- 90 100 , , mysql. , 100% , . , XPIR_DT (5- ), (11- ). , sql 90, false, 100.

select if((month(XPIR_DT)>=5) and (month(XPIR_DT)<11),90,100) from date_table where id=1