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]
Date calculation and displaying the right date
Hi All I've not been able to find a good answer to this or figure out what would be the best approach - bear in mind I am a beginner. I have tried the following USE databas SELECT Item_A AS 'Something 1', Item_B AS 'Something 2', Item_C AS 'Something 3', INTERVAL 6 MONTH + Item_B AS 'Something 4', BEGIN IF CURDATE() = INTERVAL 6 MONTH + Item_C THEN SELECT INTERVAL 12 MONTH + Item_C [ELSEIF CURDATE() = INTERVAL 12 MONTH + Item_C THEN SELECT INTERVAL 18 MONTH + Item_C] ELSE INTERVAL 6 MONTH + Item_C) END IF FROM Table ; What I want to do is; I have a post Item_C which is a date. Six months after Item_C an event occurs (lets call it The Event) , and continues to occur every six months untill the post Item_C recives Status NULL. I need to be able to extract from the database the following info and I will explain it in an example; Say on May 1 I would like to find out what if any instances of The Event are occuring during the month of may and the next month. If the original Item_C has the date 2003-01-01, the The Event has/will occurr on 2003-07-01 and on 2004-01-01 and on 2004-07-01 etc. In order for me to discover on June 1 2004 that the The Event will occur 2004-07-01 I need to do a calculation on the original date 2003-01-01 and present the relevant date 2004-07-01 at the relevant time and at the same not including any Item_C which has status NULL. I tried to do this with the above example - which I am sure all you knowledgeable people can see is kludge at best...I am prepared to figure this out on my own but I am having difficulty grasping the appropriate methodology here. Hence any and all help will be much appreciated. Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date calculation, design advice needed
hi all, i've a question concerning the calculation of the number of days between 2 dates, and after searching the mailing list i'm still stuck, any help will be appreceiated :} actually i've a table with a lot of rows (varying between 5 to 15 millions), each row has a unique id and 2 date: start_date and end_date (which are in fact datetime fields). i need to calculate the (integer) number of days between start_date and end_date for each row BUT without counting some dates (mostly sundays and some holidays) if they are present between start_date and end_date. i dont know if it's very clear (sorry, english is not my native language) so here's an example of what i'm trying actually : - i've created a table to store the dates i need to exclude : CREATE TABLE `TBL_EXCLUDE_DATE` ( `excl_date` date NOT NULL default '-00-00', `label` char(32) NOT NULL default '', PRIMARY KEY (`excl_date`) ) and populated it with all the sundays of year 2003 and some others dates like christmas, easter monday, etc etc i've also my main table with my millions of rows: CREATE TABLE `TBL_MAIN_DATE` ( `id` mediumint(9) unsigned NOT NULL auto_increment, `start_date` datetime NOT NULL, `end_date` datetime NOT NULL, PRIMARY KEY (`id`), KEY `start_date` (`start_date`), KEY `end_date` (`end_date`) ) i created also a third table for storing the number of days to exclude for each row: CREATE TABLE `TBL_COUNT_EXCLUDE` ( `id` mediumint(9) unsigned NOT NULL, `excl_count` tinyint unsigned NOT NULL, PRIMARY KEY (`id`) ) and i populate it with this query: INSERT INTO `TBL_COUNT_EXCLUDE` SELECT TEST.id, SUM(IF(EX.excl_date BETWEEN TEST.start_date AND TEST.end_date, 1, 0)) AS nb_jour FROM TBL_TEST AS TEST, SILOE_REF_EXCLUSION_DATE AS EX GROUP BY id ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date calculation, design advice needed (continued)
arrgg, sorry i made a mistake and my precedent mail
was sent before i finished wrting it :-{,
the query is:
INSERT INTO `TBL_COUNT_EXCLUDE`
SELECT MAIN.id, SUM(IF(EX.excl_date BETWEEN
MAIN.start_date AND MAIN.end_date, 1, 0)) AS
excl_count
FROM TBL_MAIN_DATE AS MAIN, TBL_EXCLUDE_DATE AS EX
GROUP BY id
and after that i substract the number of days between
start_date and end_date for each row, minus
excl_count.
i dont think this is very efficient, and it takes a
long time to compute on millions of rows, so any
advice will be greatly appreciated.
here's some info on the data i manipulate:
- there's can't be more than 3 months between
start_date and end_date
- start_date and end_date can span on two consecutives
year, i will have some cases with start_date :
2003-12-10 and end_date : 2004-02-10)
___
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Calculation
On 8 Aug 2003 at 10:16, Oswaldo Castro wrote: I have two datetime fields on my database. I nedd to return the difference in minutes between them. I tried date_sub, extract(hour_minute from ...) and it does't work SELECT ( UNIX_TIMESTAMP(datetime2) - UNIX_TIMESTAMP(datetime1) ) / 60; (assuming the dates are within the 1970-2037 range). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date Calculation
Hi List I have two datetime fields on my database. I nedd to return the difference in minutes between them. I tried date_sub, extract(hour_minute from ...) and it does't work Any help will be very apreciated. Thanks Oswaldo Castro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date calculation
Dear Sirs: Please I am facing the follow problem: I need to calculate the difference in seconds for two registers type datetime, My question is if exists some function or trick on mysql for perform it? I need this for calculate some information about time for a billing system so I need precision of seconds. Thank you for your replies Ernesto Freyre Get your free email with GroupWeb Worldmailer at http://www.worldmailer.com. Send and receive e-mail from any computer with a web browser. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: date calculation
On 29 Nov 2001, [EMAIL PROTECTED] wrote: I need to calculate the difference in seconds for two registers type datetime, My question is if exists some function or trick on mysql for perform it? I need this for calculate some information about time for a billing system so I need precision of seconds. If you are developing on a *nix system and are able to do this at all, I find that the easiest way is to store a unixtime integer and to do the conversion to/from real dates in the application. my 2 cents, Sincerely, Jan -- Mr. Jan-Aage Bruvoll IT Project Manager 20 Min Holding, Thurgauerstrasse 40, CH-8050 Zurich Zurich office: +41 1 307 4293, fax: +41 1 307 4281 Office/fax: +44 2072408283 Mobile: +44 7740291600 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
