Question on date calculation +
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]
Re: Question on date calculation +
You will get your answers by reading carefully the Date Time Functions in the MySQL Reference Manual. Look at the URL: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Bernard On Wednesday 17 November 2004 16:40, 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]
Re: Question on date calculation +
--- Bernard Clement [EMAIL PROTECTED] wrote: You will get your answers by reading carefully the Date Time Functions in the MySQL Reference Manual. This will tell me how to automatically update the column in question ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on date calculation +
You can't automatically update it, that would require triggers which are not supported in mysql, you would need some sort of script that runs once a day and manually uses the functions described in the linke Bernard sent you to update the field. However I would recommend a different table structure, 2 fields: startDate, endDate Then nothing needs to be updated. When you check in your script as to whether the user has time left just select where endDate NOW(). You can compute the endDate easily when doing your insert as DATE_ADD(startDate, INTERVAL LenChoise DAYS), so you don't need to modify anything as far as how you present the choice to the user. On Wed, 2004-11-17 at 13:58 -0800, Stuart Felenstein wrote: --- Bernard Clement [EMAIL PROTECTED] wrote: You will get your answers by reading carefully the Date Time Functions in the MySQL Reference Manual. This will tell me how to automatically update the column in question ? Stuart -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on date calculation +
No, but it will tell you how to compute the field or, maybe, change your design! You definitely need an update statement, something like: update table name set DaysLeft=wathever your figure out from your reading. Now how to run this automatically? Well I do not know if you are using MS WIndows or Linux. For MS WIndows I cannot really help you! Probably using the at command. Under Linux you can use cron to automatically start mysql with your update statement. You can run it as often as you wish. The command might look like: mysql -uuser -ppassword -e update ... Bernard On Wednesday 17 November 2004 16:58, Stuart Felenstein wrote: --- Bernard Clement [EMAIL PROTECTED] wrote: You will get your answers by reading carefully the Date Time Functions in the MySQL Reference Manual. This will tell me how to automatically update the column in question ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on date calculation +
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]
