[Resolved] calculating a future date and expressing it in time()

[ajna]

I am working on some a math issue that is driving me nuts.

I have a record in a database. At some point from the time my account begins (noted as the time() of my database record being created) I decide to be removed from this database -- I wish to note this on the record (ie: set a REMOVE field to YES), and calculate from the initial account creation when to remove the record from my table at the end of the current 7 day period this request was made.

So I create my record today and insert the time(). 16 days later I decide to remove the record at the end of the current weeks activity -- the account should get removed after the 21st day since my record was created (the account staying active til the end of the current week during which removal was requested). How do I determine the proper time() for the 21st day (or, rather, end of the THIRD WEEK) since the account was opened?

Any ideas?

Thanks!

Kev

[phpmaven]

If your are using MySQL, take a look at MySQL's to_days() and from_days() functions.

[ajna]

OK. I've been doing the research and testing thing, and I think I'm on the right track. But it just isn't working....

I am using the following code to try to find the USERID of users whos EXPIRE_BASED_ON was 31 days ago, with a DURATION of 30, and a CANCELLED of NO. These records are then updates to set EXPIRE_BASED_ON to TODAYS DATE.

Let's say I have a table with the following data:

USERID DURATION CANCELLED EXPIRE_BASED_ON
test30old 30 NO 2003-07-21
test90old 90 NO 2003-05-23

CODE FOLLOWS:

$today = date("Y-m-d", mktime());

$sql = "SELECT * from ecxsubinfo WHERE DURATION = '30' AND FROM_DAYS(TO_DAYS('EXPIRE_BASED_ON') + 31) = '$today' AND CANCELLED = 'NO'";

$result = @($sql,$connection) or die ("Couldn't execute query 1.");
if (mysql_num_rows($result) > 0) {
while ($row= mysql_fetch_array($result, 1)) {

$userid = $row["USERID"];

$sql = "UPDATE ecxsubinfo SET EXPIRE_BASED_ON = '$today' WHERE USERID = '$userid'";

@($sql,$connection) or die ("Couldn't select database loc.");
}
}

The 30 day account in the db should be updated to TODAYS DATE (from 2003-07-21 to 2003-08-22), but it isn't. I've changed the following code bit:

FROM_DAYS(TO_DAYS('EXPIRE_BASED_ON') + 31)

to "+32" "+30 and "+33" in case my math was off about the days since 2003-07-21, but the record never matches and stays un-updated.

Am I missing something basic?

Is FROM_DAYS(TO_DAYS('EXPIRE_BASED_ON') + 31) the wrong way to get the comparison date I desire?

Thanks for any help.

Kev

[DoD]

you got it, mktime() is what you are looking for 🙂

[ajna]

Pardon the crude reply, but I am not very familiar with the mktime() command. After I send this I will research it, but I want to make sure I understand your comment.

You seem to be saying I would do something like this as my comparison:

mktime() statement based on EXPIRE_BASED_ON = '$today'

or even

mktime(EXPIRE_BASED_ON) = '$today'

Kev

[DoD]

okay, im taking a proper look now 😛

The best ways to store times and dates in your database is using the time() format - the time since the 1970 Unix creation.

This puts your time in seconds, which means it can be manipulated easily.

For example, to get a date out of it, what you do is date(format mktime(seconds))

Im a little puzzled over this:

$sql = "SELECT * from ecxsubinfo WHERE DURATION = '30' 
AND FROM_DAYS(TO_DAYS('EXPIRE_BASED_ON') + 31) = '$today' 
AND CANCELLED = 'NO'"; 

its this FROM_DAYS(TO_DAYS('EXPIRE_BASED_ON') + 31).

Ok.

If i were you, i would look at mktime() with this one...

where 'EXPIRE_BASED_ON' < time (mktime(0,0,$today,0,-30)

where $today is in the time() seconds. (or you can retrieve today's month, day year etc and -30 from the day)

So thats saying, select where 'EXPIRE_BASED_ON' is less than 30 days ago's second count.

My code wont be perfect, and it requires alot of change to your database. What i would say, is to change it, run a query of getting the date set, and running each individual day month and year value through mktime() and time() - you will need explode() to get the numbers out of the date format you set.

your mktime(EXPIRE_BASED_ON) = '$today' would not work, its in the wrong date format. The function cannot tell which is the month day and year and where to break it. For example, if you game it 01,02,03 it wouldnt have a clue what to do.

See http://uk.php.net/manual/en/function.mktime.php for mktime()

and time ()

http://uk.php.net/manual/en/function.time.php

[DoD]

Ok, im not 100% sure of my time() working with the mktime()

i think that you may have to use date("s", mktime(0,0,...));

What i would recommend you do!

Take it in steps. First, get today's date outputted (using echo) on a page.

For example:

echo time();

then, from this, calculate 30 days ago, and output that...

date ("s" mktime (0,0...)); IM NOT SURE IF TO USE DATE OR TIME!!!

then, set up your query, and get it to simply output what it found.

Also, something to see if your query is right is to simply obtain the time in seconds of 30 days ago...

and run the query through something like phpMyAdmin - it will give you errors, and you can manually set the query.

Once the query is sorted, the rest will be fine - you may need to mod your date.

[ajna]

OK. I will look into this path. I was originally planning on using an EXPIRE_BASED_ON which is based on the timecode from time(), but phpmaven recommended looking at MySQL's to_days() and from_days() functions -- so I did.

However, I am still kind of confused -- after everything I read about TO_DAYS, FROM_DAYS, etc. if I have a field EXPIRE_BASED_ON, should the following code in a query string equal a date 31 days after the day stored in EXPIRE_BASED_ON?

FROM_DAYS(TO_DAYS('EXPIRE_BASED_ON') + 31)

Kev

[DoD]

Well, all i can say, is you need to change that date format. Into time() preferably or into the mktime() format.

Forget the TO_DAYS, it doesnt look as though you are using it correctly. And i dont know how to.

Get your db dates in time()

compare the db dates in time() with the time() format for the day 30 days ago - where the numer of time() is less than that for 30 days ago.

[ajna]

I think I can use something like this:

Again, EXPIRE_BASED_ON is now a time() entry from when the user first registered. The code below should check if it is over 30 days since that time() and replace it with the time() it is now.

$gap30 = 30 24 60 * 60;

$sql= "SELECT USERID FROM ecxsubinfo WHERE (EXPIRE_BASED_ON < ($time - $gap30)) AND DURATION = '30' AND CANCELLED = 'NO'";

$result = @($sql,$connection) or die ("Couldn't select database.");
$RowCount = mysql_num_rows($result);
while($row= mysql_fetch_array($result, 1)) { // set variables from first search
$userid = $row[USERID];

// Updating record
$sql = "UPDATE ecxsubinfo SET EXPIRE_BASED_ON = '$time' WHERE USERID = '$userid'";

@($sql,$connection) or die ("Couldn't select database.");

Does that look right? I believe that:

(EXPIRE_BASED_ON < ($time - $gap30))

will check if the EXPIRE_BASED_ON time() entry is over 30 days old and return true if it is.

Thanks for all your input!

Kev

[DoD]

Should do. If it doesnt, use phpMyAdmin to set it, and refine all querys - i use it as it tells me my problem areas.

Glad it helped 🙂