Is there a way to store diff of dates of 2 cols as 3rd col?
Moderator: General Moderators
Is there a way to store diff of dates of 2 cols as 3rd col?
I have something like this:
Start Validity...................End Validity.......................Both are date type in MySQL
2007-08-10....................2007-09-20.......................
Is there a way to create a third column that is the difference of the other two columns? I mean, the number of days should be stored in the third column.... Is there a direct way of doing or should I keep using date functions in php to calculate that?
Start Validity...................End Validity.......................Both are date type in MySQL
2007-08-10....................2007-09-20.......................
Is there a way to create a third column that is the difference of the other two columns? I mean, the number of days should be stored in the third column.... Is there a direct way of doing or should I keep using date functions in php to calculate that?
Yes. e.g.legend986 wrote:No... I meant do I have to use php to calculate the value of the third value and insert it.... Thanks for the SQL functions... Oh so I would be using something like DATEDIFF('fisrtvalue','secondvalue') as the value to be inserted into the third column. Is that it?
Code: Select all
UPDATE table SET
thirdcol = DATEDIFF('fisrtvalue','secondvalue')- Gente
- Forum Contributor
- Posts: 252
- Joined: Wed Jun 13, 2007 9:43 am
- Location: Ukraine, Kharkov
- Contact:
I'm not sure it's a good idea to update all the table after every single insert.mwasif wrote:Yes. e.g.legend986 wrote:No... I meant do I have to use php to calculate the value of the third value and insert it.... Thanks for the SQL functions... Oh so I would be using something like DATEDIFF('fisrtvalue','secondvalue') as the value to be inserted into the third column. Is that it?
Code: Select all
UPDATE table SET thirdcol = DATEDIFF('fisrtvalue','secondvalue')
Code: Select all
INSERT INTO 'your_table'
SET fisrtvalue = '1997-12-31',
secondvalue = '1997-12-30',
thirdcol = DATEDIFF('1997-12-31','1997-12-30')Although there are always exceptions, the general rule is that you should never store calculated values in a database. Unlike spreadsheets, databases are designed to store raw data from which you can calculate whatever you want in the application program. I would advise you not to store the date difference in the record unless there are convincing reasons why you should (such as an enormous database--many millions of records--and the need to calculate summaries frequently).
Well actually what I'm doing is something like:
I'll ask the user to input a starting date for a package
I'll calculate the ending date using the PHP Date functions and insert both the start date and the end date into two columns.
What I need to do is depending on the package he has chosen, I need to see if his package expired. For example, if he has chosen a one month pack, I'll see if 30 days are over. Then if they are over, I'll remove him from the member's list.
I thought of the third column strategy. What is the best way to achieve this?
PS: I have a silly idea too: Is there a way I can tie up a code to the MySQL column itself? For example, if his package_id is 1, then the SQL should insert 30 in the third column (I'll do this using the solution of the above pacakge) and then if the third column value is above 30 or something else depending on the package, I should insert a 1 or 0 in the fourth column. If its a 1, then the packages is still valid else not... Is there something like this?
I'll ask the user to input a starting date for a package
I'll calculate the ending date using the PHP Date functions and insert both the start date and the end date into two columns.
What I need to do is depending on the package he has chosen, I need to see if his package expired. For example, if he has chosen a one month pack, I'll see if 30 days are over. Then if they are over, I'll remove him from the member's list.
I thought of the third column strategy. What is the best way to achieve this?
PS: I have a silly idea too: Is there a way I can tie up a code to the MySQL column itself? For example, if his package_id is 1, then the SQL should insert 30 in the third column (I'll do this using the solution of the above pacakge) and then if the third column value is above 30 or something else depending on the package, I should insert a 1 or 0 in the fourth column. If its a 1, then the packages is still valid else not... Is there something like this?
I'm not sure if I could say it better than califdon.califdon wrote:Although there are always exceptions, the general rule is that you should never store calculated values in a database. Unlike spreadsheets, databases are designed to store raw data from which you can calculate whatever you want in the application program. I would advise you not to store the date difference in the record unless there are convincing reasons why you should (such as an enormous database--many millions of records--and the need to calculate summaries frequently).
You can do (And should do) all of your calculations via mysql (Not PHP) functions.
You can check the member expiry by comparing expiry date column to the current date
Code: Select all
SELECT username FROM users WEHRE expiry_date<CURDATE()Why not just make it so it only pulls out the info where the date is <= 30 days from now?
Just like php, mysql has a nice (but i still like php's better) documentation on their website.
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
Code: Select all
SELECT * FROM `apps` WHERE DATE_SUB(CURDATE(),INTERVAL 30 Day) <= `time`;
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
Hmm... so many logics
I'm confused now... My purpose is something like this:
There is a member_id, start_date,end_date, package_id for each member_id. The end_date is calculated based on the package_id
If package_id = 1, end_date = start_date + 7 days
package_id = 2, end_date = start_date + 14 days
...
...
package_id = 6, end_date = start_date + 6 months
All this is done while the member is registering himself for a package. Now, there are two ways I could remove the user:
1. Automatically remove him once the end_date has passed - I don't know how to do this.
2. Manual Removal - I do this in the following way.
There is a page where I list all the courses into which the user is enrolled. At the time of listing, I fetch all the data from the database and then see if the end_date has passed. If it has, then I would remove him from the course, so that his course listing page wouldn't contain his name.
Which one should I follow? I'm finding it difficult to proceed without knowing if the solution to 1 exists...
And for 2, how would the SQL statement look like when I'm writing it in PHP?
Or is there a better logic for all this?
PS: Yeah PHP documentation seems much better. I'm still reading through MySQL (documentation thanks for the link). Hope, I'll get some other idea into my mind...
There is a member_id, start_date,end_date, package_id for each member_id. The end_date is calculated based on the package_id
If package_id = 1, end_date = start_date + 7 days
package_id = 2, end_date = start_date + 14 days
...
...
package_id = 6, end_date = start_date + 6 months
All this is done while the member is registering himself for a package. Now, there are two ways I could remove the user:
1. Automatically remove him once the end_date has passed - I don't know how to do this.
2. Manual Removal - I do this in the following way.
There is a page where I list all the courses into which the user is enrolled. At the time of listing, I fetch all the data from the database and then see if the end_date has passed. If it has, then I would remove him from the course, so that his course listing page wouldn't contain his name.
Which one should I follow? I'm finding it difficult to proceed without knowing if the solution to 1 exists...
And for 2, how would the SQL statement look like when I'm writing it in PHP?
Or is there a better logic for all this?
PS: Yeah PHP documentation seems much better. I'm still reading through MySQL (documentation thanks for the link). Hope, I'll get some other idea into my mind...
If you store how long each "package" is in the package table, then you could so something like so..
(Keep it mind its a bit messy since i just wrote this on the fly)
(Keep it mind its a bit messy since i just wrote this on the fly)
Code: Select all
// Get every package info
$sql = "SELECT length from `package`"
$result = mysql_query($sql) or die('Query failed: ' . $sql . '<br>' . mysql_error());
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$sql2 = "SELECT * FROM `people` WHERE DATE_SUB(CURDATE(),INTERVAL " . $line['length'] . " Day) <= `time`"; // Assuming 'length' is the number of days specifiyed in the table package
$result2 = mysql_query($sql2) or die('Query failed: ' . $sql2 . '<br>' . mysql_error());
while ($line2= mysql_fetch_array($result2, MYSQL_ASSOC)) {
//This should be every "people" whos time is within 30 days
// Change the <= to >= it will be everyone who is "expired"
}
}