Is there a way to store diff of dates of 2 cols as 3rd col?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Is there a way to store diff of dates of 2 cols as 3rd col?

Post 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?
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Why are you using the date functions in php?

http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post 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?
mwasif
Forum Newbie
Posts: 13
Joined: Sun Jul 15, 2007 1:24 pm

Post 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')
User avatar
Gente
Forum Contributor
Posts: 252
Joined: Wed Jun 13, 2007 9:43 am
Location: Ukraine, Kharkov
Contact:

Post 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')
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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).
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post 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?
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Hmm... califdon says I should not store the date differences, but hawleyjr says the opposite 8O I'm a little confused or am I just looking at the answer from the wrong perspective..?
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post 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?
mwasif
Forum Newbie
Posts: 13
Joined: Sun Jul 15, 2007 1:24 pm

Post 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()
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post 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
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post 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...
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post 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"
     }
}

Post Reply