Jump to content

total date calculation


sofia403

Recommended Posts

ok, i had some great help from this forum recently so i hope for the same outcome this time :)

 

probably a rather easy question for most of you,

 

i have a form that visitors use to input date using php (submission_date) in to db. date is then entered in db as a VARCHAR (i.e. Mar-15-2011), what i would like is to have another field which would calculate time elapsed. Since calculation can be only done on date not varchar ( i assume), i would need to have submission date also as a DATE and then passed on to a new field for calculating total time. does that make sense? lol

 

I would really appreciate any pointers or help with this, not sure if im going in the right direction, but thats a start :)

 

thank you!

 

Link to comment
Share on other sites

So, why are you using a varchar to store a date?

 

But, I'm not understanding your request. "what i would like is to have another field which would calculate time elapsed."

 

Time elapsed from when to when? Do you want the time elapsed from 'submission_date' to today, vice versa, or what? You talk about a "new field" but, not sure if you are talking about a new field to store the time elapsed (which makes no sense) or if it is for something else.

 

Once you get the records you can find the time elapsed in PHP with the varchar value using strtotime() and subtracting that from time() to get the time elapsed.

Link to comment
Share on other sites

Hi mjdamato,

 

im rather new to php and mysql and i really great full for all the help, also english is my 2nd language so i usually make things sound more complicated then they are lol. but i do appreciate everyones patience.

 

now for my question again, thats exactly what i want to do! i want to calculate the total time (in days) from when date was submitted till today, so it would update itself automatically each day. Right now i just have one column which has all the dates for when they were entered by the users. What i would like is to have another row which would show elapsed time for each corresponding record. does that make sense?

 

i chose VARCHAR so i could display my date in a more user friendly format, its entered as Mar-15-2011 opposed to 2011-03-15 as a DATE.

 

Are you saying that i can calculate total time with varchar?

 

 

 

 

Link to comment
Share on other sites

one option you could try would be to have 3 input fields in the form.......one for the day of the month, one for the month, and one for the year.  now you cant use the option for month name, but most people understand mm/dd/yyyy format. so you could have 3 rows in database month, day, year.  each one submits to the correct field and then you can use the following to calculate the number of days to present day.......

<?php 
$date1 = "$year-$month-$day";   

  //calculate years of age (input string: YYYY-MM-DD)
  function birthday ($birthday){
    list($year,$month,$day) = explode("-",$date1);
    $year_diff  = date("Y") - $year;
    $month_diff = date("m") - $month;
    $day_diff   = date("d") - $day;
    if ($day_diff < 0 || $month_diff < 0)
      $year_diff--;
    return $year_diff;
  }
echo birthday($date1). "years ago";

?>

 

my function was named birthday.

Now you can edit this so that you could calculate days instead of years just return $day_diff.

 

then to show the day they entered do this:

<?php
echo $month; ?>/<?php echo $day; ?>/<?php echo $year; ?>

or something similar.  make sense?

Link to comment
Share on other sites

You do NOT want to create a new column in your database to store the "elapsed time". That should be calculated in real-time.

 

i chose VARCHAR so i could display my date in a more user friendly format, its entered as Mar-15-2011 opposed to 2011-03-15 as a DATE.

 

Are you saying that i can calculate total time with varchar?

 

1. You should store dates as dates, number as an appropriate number type (in, float, etc) and text as text. There is a date() functions to transform a date value into whatever format you want for displaying the value. Do not store data in the wrong format because of how you want to display it. You modify the value in PHP code after getting it from the database.

 

2. Yes you could do the calculation in PHP with the varchar value, but you shouldn't be using the varchar for a date to begin with.

 

 

Even though you should store the date as a date there is a slight hurdle in converting MySQL dates to PHP dates and vice versa. Take a look at this article: http://www.richardlord.net/blog/dates-in-php-and-mysql

 

So first, convert your submission_date field to a datetime field.

 

Then, when saving a date, create a MySQL valid datetime value using

$mysqlDate = date( 'Y-m-d H:i:s', $phpdate );

and use that value in your insert query

 

When running a query to get your records you will need to process the value in PHP to convert it to a PHP date using strtotime(). At the same time you can calculate the elapsed time

while($row=mysql_fetch_assoc($result))
{
    $submission_date_ts = strtotime($row['submission_date']); //Timestamp
    $submission_date_str = date("M-d-Y", $submission_date_ts); //String in format MMM-DD-YYYY
    $time_elapsed = (time() - $submission_date_ts); //Elapsed time in seconds

    //
}

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.