Ok, you said you were using a scripting language, what is it, PHP?
If it is the case, I suggest you drop the last column 'DaysLeft' and make a function in the scripting language of your choice that takes the 'PostStart' and 'LenChoice' as arguments:
in PHP it would write like this: (hope you understand)
function DaysLeft($start, $len){
return time()-($start+$len);
}This function returns the Unix timestamp that is left for the user's usage. It is in milliseconds if I'm right (see php.net for time()).
In my opinion, MySQL is not good for time calculations, you should better make 'PostStart' an INTEGER and put a Unix-Timestamp into it, same thing for 'LenChoice' you should put the time length in milliseconds in there as an INTEGER.
And your scripting language makes all the calculations, MySQL is used to store important variables. Variables that can be calculated will take too much space for a micro-nothing of work.
I work for security programming and it is common thing to take the initial logon timestamp and the time elapsed since the last HTTP command. I prefer to deal with time using my scripting language (PHP) instead of using MySQL functions. Why, first because I've seen inconsitency in MySQL time calculations, second because I can easily use the Unix-Timestamp INTEGER and use date() with it to format the way it should be displayed. Also, it is easy to make a variable OneDay=(1000*60*60*24), OneHour=(1000*60*60), OneMinute(1000*60), make calculations (PostStart/OneDay)=DaysLeftAsFloat.
Hope this helps, but it would help to know what scripting language you are using, as I say scripting languages are good for calculations where MySQL is good for storing.
Simon
Stuart Felenstein wrote:
While I'm figuring this needs to be addressed via my
scripting language wanted to ask here.
Customers will buy a block of time to use my service. Time meaning "number of days". 90, 45, 60, etc.
Here are the fields relevant to this question: PostStart [Date] LenChoice [int] DaysLeft [int]
When they register , the current date is input automagically into PostStart. LenChoice is chosen by the user and is the length of days they want this block. DaysLeft is where the calculation would be done to hold the difference between the current date, date posted and how many days were paid for. This is where I'm not entirely sure what to do. I'm probably inhaling gasoline or something but how would I get the field to the numbers of DaysLeft ?
i.e. DaysLeft[today]= 3, DaysLeft[tomorrow]=2..etc
Thank you , Stuart
p.s. I'm on 4.0.22 , so no stored procedures.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
