$10 or $15 to tell me the sql query i need

Here's some info about the question I have:

Problem
I am trying to figure out how to determine the amount of time between two dates and then post that number to a field.

Details
I have a table named "user" that contains fields called initDate, wedMonth, wedDay, wedYear, and timetowed. All fields are of the type varchar.

initDate contains a date that the user registered in the form January 20, 2008.

wedMonth has month names spelled out like February, wedDay has days like 5, and wedYear is a four digit year like 2008.

I want timetowed to be filled with the number of months (or days) between the two dates. For example, if the initDate was January 1, 2008 and the wedMonth, wedDay, and wedYear were May 1, 2008, then the timetowed field would be 4 (or 120 if we did it in days).

Can I write a mysql query to do this? Or does it need to be PHP on the website where the user registers? If I can do it with PHP, great, but explain how I can then retoactively assign this field for all of my previous users that are already in the db. In either case, I'll give $10 to the first person who posts the code below that works without alteration. $5 bonus if you get it done today (by midnight CST on Sunday Jan 20).

Let me know if I need to post the PHP on the user registration webpage.

Thanks!

 

You have two date/time:
eg.
2001-06-29 21:00:00 and now

First of all, you have to convert the two formated date/time to a UNIX timestamp, through mktime() function

To obtain UNIX time stamp from 2001-06-29 21:00:00


$date1_formated = "2001-06-29 21:00:00";
$date1 = strtotime($date1_formated); //converts to a UNIX timestamp
$date2 = time(); //returns current UNIX timestamp

Then you must do the difference

$seconds_between = $date2 - $date1;

Now you got in seconds the difference between this two date/time variables.
You just have to divide and round in every case:

To get years ---> divide $seconds_between by 60x60x24x365 (this is aproximated, because there are years with 366 days)

$num_years = floor($seconds_between/(60*60*24*365));

$not_rounded = $seconds_between/(60*60*24*365);

$difference = $not_rouded - $num_years;

if ($difference > 0) {
$num_months = floor(($not_rouded - $num_years)*12);
$not_rounded = ($not_rouded - $num_years)*12;
}

And you only have to repeat the steps with days (*30), hours, minutes and seconds.
It's a hard work.

I hope it will help you.

 

wow, LGwebs, you are REALLY fast!

So is that function something that I put in the query box in PHPmyadmin? Or something I need to run on a web page?

P.S. I clicked on the link in your sig to sign up to thank you and it just took me to a blank page.

 

That would be something you need to run on the webpage.

 

You should be able to do it with pure SQL.... if you wanted a table with the output then in MS SQL it would be:

SELECT     Userid, initDate, wedDay, wedMonth, wedYear, DATEDIFF(day, wedDay + wedMonth + wedYear, initDate) AS DaysToGo
FROM         User

Code (SQL):

Looking at the MySQL reference however it appears that DateDiff only does days in MySQL so the code should be:

SELECT     Userid, initDate, wedDay, wedMonth, wedYear, DATEDIFF(wedDay + wedMonth + wedYear, initDate) AS DaysToGo
FROM         User

Code (MySQL):

But i dont have MySQL running so cannot test it

 

SELECT floor(((date1-date2)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600 -
(floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
FROM dates;

 

Thanks! I feel like you are definintely the closest so far. You are at least trying to make it work with mySQL rather than PHP and you gave me a query to try.

I had to tweak what you had a bit (I guess because it is case sensitve on the table name.)

SELECT recID, initDate, wedDay, wedMonth, wedYear, DATEDIFF(wedDay + wedMonth + wedYear, initDate) AS DaysToGo FROM user

This didn't throw any errors, but I got NULL in the DaysToGo column. I tried several variations including the one below, but to no avail.

SELECT recID, initDate, wedDay, wedMonth, wedYear, DATEDIFF(wedMonth + wedDay + wedYear, initDate) AS DaysToGo FROM user

You have any ideas? Do you think it is because there is a comma in the initDate text field??

Thanks!

 

What link was it

 

Thanks for the effort, but this didn't work. I think for this to work I would need to get my date fields to be in date format rather then the varchar they are now.

 

Hmm... I can't remember. None of them are ringing a bell right now. Have you changed them recently?

 

Have your date fields in date or timestamp format will save many headaches Because you will be able to use mySQL's date arithmetic functions on the fields

 

I completely agree. Unfortunately, it was not set up this way and I seriously doubt it's something easy to change over.