The safest way for saving unexpected time formats in MySQL with PHP

I have to collect dates from different formats and save them in MySQL Filed Date (YYYY-MM-DD I reckon), so how can i change formats like 12/24/2010, 24/12/2010, 12-10-2010 and stuff like this with PHP ?

I know there is mktime, strtotime but how can i do it safely ?

UPDATE开发者_开发知识库: safe from accuracy point of view! :)

You can't: consider mm/dd/yy v yy/mm/dd v dd/mm/yy for the date November 9th 2010.

You would need to know that format that they did it in before hand. There is no one-off solution.

...formats like 12/24/2010, 24/12/2010...

Your basic problem here is that dd/mm/yyyy is often indistinguishable from mm/dd/yyyy.

If you get 11/12/2010, how will you know which format it is supposed to be? strtotime() won't help you there.

If you do plan to use strtotime(), it's worth pointing out that if you give it 11/12/2010 (ie with slash separators), it will treat it as mm/dd/yyyy, but if you give it 11-12-2010 (dash separators), it will treat it as dd-mm-yyyy. But because both formats are commonly written both ways, that distinction is not only pointless but actually makes the strtotime() function too unpredictable for real-life use.

If you do already know the format of any specific incoming date string, then your best best is to split it into its component parts using preg_match() or similar, and re-build it using mktime().

If you don't know the format that was intended by the user then you can guess, but if its ambiguous then there's always a chance you'll get it wrong.

If this is coming from a web site input form, you would be better advised to provide a front-end control in Javascript that always sends the date in a known format (preferably yyyy-mm-dd), and thus removing any possible ambiguity.

If the data is coming from some other source that you can't control, and you're getting ambiguous input then you need to either just accept that you're going to get it wrong some of the time, or ask the end user / data provider to clarify what they meant.

you could try the folowing if you realy have to (try to avoid it, others said why):

$mysql_timestamp = date("Y-m-d",strtotime($unknown_timestamp));

If you already have thoses dates at hand, you might have a lot of work ahead of you, but if you have to collect it you can easily force a standard through forms

Month : [   ] / Day : [   ] / Year : [    ] 

and then you build your date through mktime / date in PHP

But if you have already the data at hand, you might have to make some rules to flag weird data since strtotime() will work based on your locale (french and english dates aren't written the same way ie : french date dd/mm/YYYY and english mm/dd/YYYY).

You would have to define a format you want to use and flag all data that doesn't fit in it for manual rework

ie : 12/24/2010 (perfectly makes sense in english date format but that would be 12/12/2011 in french (notice the year change)).

Edit :

Here is a start to review your dates before converting them :

<?php

$dates = array('12-12-2010', '24-12-2010','12-24-2010','12-24-10','24-12-10',
               '12-12-10','12/12/2010','24/12/2010','12/24/2010', '12/24/10',
               '24/12/10', '12/12/10','2010-12-12', '2010-24-12','2010-12-24',
               '10-12-24','10-24-12','10-12-12','2010/12/12','2010/24/12',
               '2010/12/24', '10/12/24','10/24/12','10/12/12','11-11-2011');

$regEx = array('YYYY-MM-DD' => '/^(19|20)\d\d[- \/.](0[1-9]|1[012])[- \/.](0[1-9]|[12][0-9]|3[01])$/',
               'MM-DD-YYYY' => '/^(0[1-9]|1[012])[- \/.](0[1-9]|[12][0-9]|3[01])[- \/.]((19|20)\d\d)$/',
               'DD-MM-YYYY' => '/^(0[1-9]|[12][0-9]|3[01])[- \/.](0[1-9]|1[012])[- \/.](\d\d)$/',
               'YY-MM-DD' => '/^\d\d[- \/.](0[1-9]|1[012])[- \/.](0[1-9]|[12][0-9]|3[01])$/',
               'MM-DD-YY' => '/^(0[1-9]|1[012])[- \/.](0[1-9]|[12][0-9]|3[01])[- \/.](\d\d)$/',
               'YY-DD-MM' => '/^\d\d[- \/.](0[1-9]|[12][0-9]|3[01])[- \/.](0[1-9]|1[012])$/',
               'No Match' => '//' );


echo '<table style="table-layout:fixed;width:400px">';
echo '<tr>'.'<th>'.'Date'.'</th>'.'<th>'.'PATTERN'.'</th>'.'</tr>';

foreach($dates as $date){
echo '<tr>';
    echo '<td>';
    echo $date . ' ';
    echo '</td>'.'<td>';
    foreach($regEx as $name=>$pattern)
        if(preg_match($pattern, $date)){
            echo $name;
            break;
        }
    echo '</td>';

echo '</tr>';

} echo ''; ?>

which will give you a table like

+-------------+-------------+
| Date        | PATTERN     | 
+-------------+-------------+
| 12-12-2010  | MM-DD-YYYY  | 
| 24-12-2010  | No Match    | 
| 12-24-2010  | MM-DD-YYYY  | 
| 12-24-10    | MM-DD-YY    | 
+-------------+-------------+

From that woul will have a way to proceed for building your substring to grab the right parts