Date and calculations on Dates?

[dmccabe]

I have a field in a form that a user willl enter a date such as:

 

dd/mm/yy

 

I will at a later stage want to do calculations on the date entered to find things like the number of days since that date.  However I also need to show the user they date they entered in the dd/mm/yy format.

 

So my question is what is the best way to store this date and then perform the calculations on it?

[PC Nerd]

I find personally that its best to store it in a normal timestamp - and I always store it in GMT time.  Then I have a field that either states statically or through a database-per account - what timezone.  That way I can easily move between hosting servers if need be withought having to worry about the time consistency across timezones.

[dmccabe]

Thanks for the reply,  I am not very good with the different field types in mySQL, could you explain a little more how you mean?

 

 

So I would choose field type of "TIMESTAMP" and this seems to store it as YYYY-MM-DD

 

I want the user to be typing in dd/mm/yy, so how can I get the 2 to match up?

 

at the moment if I input 20/12/08  (20th Dec 2008) in the database it is stored as 2020-12-08 (8th Dec 2020) ?

 

Edit: it also has the hours mins and secs in the db too, I dont want this outputted to user or dont want it stored in the db at all if possible.

[PC Nerd]

Well I've always had issues with the timestamp field in mysql, so i prefer using int - and then converting it in php.

 

if your looking for the php conversions into timestamps and converting it to GMT - look into the date() and time() functions.  theres also a number of them that refer to converting to GMT, howeverI forget the specific functions and arguments ( though strtotime() and gmtdate() are usefulll from memory).

 

My personal preference is to do the work in php so that I have complete control over the way its formatted etc.

 

GDLK

[dmccabe]

lol ok now I am more confused.

 

Let me start again and see where we go from here:

 

What I am wanting to do is this!

 

let the user input a date in the dd/mm/yy format.

 

I am wanting to store this info in the database in a way that can:

 

a) be displayed back to the user in the same format dd/mm/yy

b) be used to perform calculations on to find things like the number of days since this date passed.

 

If it is better to store it as an integer then get php to calculate it back in to right format for display, can you please explain how?

[Mark Baker]

So I would choose field type of "TIMESTAMP" and this seems to store it as YYYY-MM-DD

No, it stores it as a timestamp, which is a numeric value (typically a number of seconds since a base date).

When you display it, it converts it to a human readable form (formatted as YYYY-MM-DD) for display purposes only.

[dmccabe]

Ok so what I have done now is this:

 

I am storing the values as DATE n the database, I then found a function to swap the date around in to the right format when displaying it

 

// Function to convert the date format from yyyy-mm-dd to dd-mm-yyy and back again.

function con2mysql($date) {
$date = explode("-",$date);
if ($date[0]<=9) { 
	$date[0]="0".$date[0]; 
}
if ($date[1]<=9) { 
	$date[1]="0".$date[1];
}
$date = array($date[2], $date[1], $date[0]);
return $n_date=implode("-", $date);

}

 

This all seems work fine unless the mm part is less than 10 in which case it adds an extra 0 when displaying the value

 

eg:

 

<td>Off Fleet Date (DD-MM-YY)</td>
<?php $offfleetdate = con2mysql($row['OFF_FLEET_DATE']); ?>
<td><input type="text" name="OFF_FLEET_DATE" value="<?php echo $offfleetdate; ?>"></td>

 

So I put in the date of 20-01-08 (20th Jan 08) and in the db it is stored as 08-01-20, which is correct, but then when the db value is ran through the converted and it is displayed in my form it shows as: 20-001-08?

 

Any ideas why?