MYSQL Query Question

[tomtimms]

I've been trying to wrap my brain from this and can't figure it out.  I have data that at the begging of the month the sum of the data goes into a table.  So all data for April get's summed up on may 1st and then stored into a table with the date format 2010-04.  I need a query that will display the results 45 days later, so on May 15th the 2010-04 data will be shown.  I hope this makes some sense, I can't seem to figure it out.

[roopurt18]

What's the datatype for the column that stores the 2010-04  Is it varchar?  Or is it a datetime or timestamp column?

 

Do you want it to display only on the 45th day later?  Or should it display as long as it's at least 45 days old?

[tomtimms]

yes its a VARCHAR (64) as I needed it to be custom time field.  The data stored in APRIL is financial and payments go out 45 days after the month is over.  So on May 1st I need the data to show up that payment is due on may 15th.  I hope that explains a little better.  I will eventually need to have 30 days and 15 days as well, but for now just 45.

[roopurt18]

I don't see why you don't just change the column type to datetime and instead of storing 2010-04 just store 2010-04-01 00:00:00.

 

VARCHAR(64) is 57 more bytes more than you need.  datetime is usually 8 or 9 bytes which is 1 or 2 more than you need, but it allows you to use the datetime functions which make calculating date differences very easy.

 

Alternatively you could CONCAT '-01' to your column value and CAST that to datetime and still use the datetime functions.

 

But VARCHAR(64) is a bad design decision.

[tomtimms]

thanks but I actually had it at varchar(16), had 64 stuck in my head from another column.