Page 1 of 2
Is there a way to store diff of dates of 2 cols as 3rd col?
Posted: Mon Jul 16, 2007 3:34 pm
by legend986
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?
Posted: Mon Jul 16, 2007 5:17 pm
by hawleyjr
Posted: Tue Jul 17, 2007 2:33 am
by legend986
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?
Posted: Tue Jul 17, 2007 8:22 am
by mwasif
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?
Yes. e.g.
Code: Select all
UPDATE table SET
thirdcol = DATEDIFF('fisrtvalue','secondvalue')
Posted: Tue Jul 17, 2007 9:39 am
by Gente
mwasif wrote: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?
Yes. e.g.
Code: Select all
UPDATE table SET
thirdcol = DATEDIFF('fisrtvalue','secondvalue')
I'm not sure it's a good idea to update all the table after every single insert.
Code: Select all
INSERT INTO 'your_table'
SET fisrtvalue = '1997-12-31',
secondvalue = '1997-12-30',
thirdcol = DATEDIFF('1997-12-31','1997-12-30')
Posted: Tue Jul 17, 2007 10:26 am
by califdon
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).
Posted: Tue Jul 17, 2007 10:32 am
by legend986
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?
Posted: Tue Jul 17, 2007 10:46 am
by hawleyjr
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).
I'm not sure if I could say it better than califdon.
You can do (And should do) all of your calculations via
mysql (Not PHP) functions.
Posted: Tue Jul 17, 2007 10:52 am
by legend986
Hmm... califdon says I should not store the date differences, but hawleyjr says the opposite

I'm a little confused or am I just looking at the answer from the wrong perspective..?
Posted: Tue Jul 17, 2007 10:56 am
by hawleyjr
No, You should not store the date differences. You should calculate as you go. There is a lot of things you can do with IF and WHERE statements, and date functions.
Posted: Tue Jul 17, 2007 10:59 am
by legend986
Oh... I think I haven't learnt any SQL after thinking about what you say just now... Could you please guide me in the right path? I mean, I need a few examples... What should I search for? Is this Advanced MySQL or something?
Posted: Tue Jul 17, 2007 11:03 am
by mwasif
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()
Posted: Tue Jul 17, 2007 11:10 am
by Zoxive
Why not just make it so it only pulls out the info where the date is <= 30 days from now?
Code: Select all
SELECT * FROM `apps` WHERE DATE_SUB(CURDATE(),INTERVAL 30 Day) <= `time`;
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
Posted: Tue Jul 17, 2007 11:28 am
by legend986
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...
Posted: Tue Jul 17, 2007 11:42 am
by Zoxive
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)
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"
}
}