Should be easy logic: how do I store a duration in a MSQL table?

[endouken]

Hey guys et gals,

 

Much like the title suggests, i'm having some trouble figuring out the logic here - i'm sure someone will have a quick and definite answer for me:

 

- I have a products table, for which some of the 'membership' type products are xyz long in duration.  So i have added a duration column to the table - but what data type is the best suited for storing the duration / interval?

 

Some membership products are 1 month long, others are 1 year long.  I thought about using seconds (int) but different months have different amounts of seconds...i thought about using DATE but that stores an actual date (e.g. March 2011) which isn't exactly a duration...

 

- I will then use the duration column to cross reference to a purchase history table which includes a date purchased and date expires, in case anyone is wondering why i'm using a duration column on the products table.

 

Thanks to anyone that can help!

 

Tom.

[The Little Guy]

Store a start date, then use the MySQL date_sub() function to check. for what ever it is you want to check

 

select * from my_table where the_date > date_sub(now(), INTERVAL 1 MONTH)

[endouken]

Store a start date, then use the MySQL date_sub() function to check. for what ever it is you want to check

 

select * from my_table where the_date > date_sub(now(), INTERVAL 1 MONTH)

 

Cool thanks. 

 

So if i want to then change the duration for some reason, i would have to change the php code? 

 

Strictly, the logical process should be to change a parameter on the mysql table (i.e. the duration cell for the product) which the php code references when making the mathematical calculation.  Thereby avoiding any further php code manipulation when making duration changes.

 

Is this a mysql fail / does mysql not have the functionality to store a duration?

[endouken]

[optikalefx]

Store the start and end date. If you want to adjust the duration, just adjust the end date. Because in reality that is what changing the duration means, it means its going to end later, or end sooner. Again using date_sub to figure out the actual duration if you want to know that specific information.

[endouken]

Store the start and end date. If you want to adjust the duration, just adjust the end date. Because in reality that is what changing the duration means, it means its going to end later, or end sooner. Again using date_sub to figure out the actual duration if you want to know that specific information.

 

Thanks for replying.

 

But to store the end date I need to know the duration.  If the duration is 1 day, 1 week or 1 year.  So how do I store a duration in a mysql table?  Once i can do that, i can use PHP to get todays date and add the duration to figure out the end date.

 

What i want to avoid is specifically including '+1 year' / '+1 week' / '+1 etc' in my php, as then to change the duration i need to edit code rather than a table - and that is bad logic.

 

Does MYSQL not have a format for storing a duration - cuz i sure as hell cannot figure it out!  :'(

[optikalefx]

Again don't store the duration.  calculate the end date in PHP and store that.  The logic is much more refined that way.

[ignace]

MySQL can't, but PHP can: strtotime() or just store the duration as seconds that way if you calculate the TIMESTAMP of a date (or just stored the TIMESTAMP) you can just add it.

 

$enddate = time() + 86400; // 86400 = 1 day

[endouken]

MySQL can't, but PHP can: strtotime() or just store the duration as seconds that way if you calculate the TIMESTAMP of a date (or just stored the TIMESTAMP) you can just add it.

 

$enddate = time() + 86400; // 86400 = 1 day

 

That's not a great idea if i read it correctly - different months have different seconds, as do years.  May seem punitive, but when a whole companies membership is out by a day it will be a very real problem.

 

What seems to be being looked over in the "store a start and end date" response, is to calculate the end date i need to know the interval between the two, that interval needs be assumed somewhere - and all assumptions should be made in the database, this way a different value can be put in to the database and the php spits out a new answer without any modification; this is database design 101.

 

How can i store "+1 year" in my database - would a varchar work?  Then i can do (e.g.) $interval = GET duration from table products where productid = $productid and use that in the php date + $interval rather than date +1 year...

 

Thanks for everyones replies so far, i did have a convo with Pikachu about this, which shed some light - but i'm always keen to debate and pick more brains!

 

Tom.

[optikalefx]

Seriously - just calculate the end date and store that.  There is an awesome mysql date function called DATE_DIFF that you can use in all your selects to return the duration without ever going to PHP. 

 

If you REALLY wanna store duration, you need to store a concatted string.  1:Y  would be 1 Year. and you can use Y from the php date functions. So in PHP you would explode(":","1:y") to get that as an array. Or just store 2 fields.

 

Either way you should just store the end date.