Identify date format from a string in PHP

Question

I am trying to update all of the date fields in the database to add 5 days to each date in all tables.

I managed to pull the dates and row id into an array then generate a SQL code statement to update them. However, each table has a different date format some with time included some without. I want to add 5 days to a date then save it back. At the moment I can do this if all dates have the same format but that's not good enough to solve my problem.

What I want is a code that can generate string format from a date string. For example:

Date String 2014-12-04 I want the code to say this date has Y-m-d format. If date string is 2017-04-03 11:11:48.653 I want the code to say this date format is Y-m-d h:i:s.

Answer 1

If you don't need to preserve the format (i.e. you can change the format in the database while adding five days), you can just throw the string at strtotime. It will try and detect the format, if possible:

$timestamp = strtotime($string_with_unknown_format);

Alternatively, you can check for different formats with regex:

function extractDateTimeFormat($string) {
    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $string))
        return 'Y-m-d';

    if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $string))
        return 'Y-m-d H:i:s';

    if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3}$/', $string))
        return 'Y-m-d H:i:s.v';

    if (preg_match('/^\d{2}\/\d{2}\/\d{4}$/', $string))
        return 'm/d/Y';

    if (preg_match('/^\d{2}\.\d{2}\.\d{4}$/', $string))
        return 'd.m.Y';
}

However, that could get tiresome, depending on how many formats you expect. On the other hand - how many can there be?

The next alternative would be to replace the digits by their placeholders directly in the string - this would be more flexible, but possibly a little less predictable. In this case, the order of the lines is important. If you need to add 12 hour formats (AM / PM), you need to insert the lines prior to the line for H:i:s or it won't work.

function extractDateTimeFormat($string) {
    $string = preg_replace('/\b\d{4}-\d{2}-\d{2}\b/', 'Y-m-d');
    $string = preg_replace('/\b\d{2}\/\d{2}\/\d{4}\b/', 'm/d/Y');
    $string = preg_replace('/\b\d{2}\.\d{2}\.\d{4}\b/', 'd.m.Y');
    $string = preg_replace('/\b\d{2}:\d{2}\b:\d{2}\b/', 'H:i:s');
    $string = preg_replace('/\b\d{2}:\d{2}\b/', 'H:i');
    $string = preg_replace('/\.\d{3}\b/', '.v');

    if (preg_match('/\d/', $string) 
        return false;

    return $string;
}

That way, you'll detect date and time formats independently so you don't have to think of every possible combination.
You'll have to check with your live data which method works better.

Answer 2

The best way to format dates and know what date format, solution for those who are developing in date format. I also add the ISO8601 date format

function date_extract_format( $d, $null = '' ) {
    // check Day -> (0[1-9]|[1-2][0-9]|3[0-1])
    // check Month -> (0[1-9]|1[0-2])
    // check Year -> [0-9]{4} or \d{4}
    $patterns = array(
        '/\b\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}.\d{3,8}Z\b/' => 'Y-m-d\TH:i:s.u\Z', // format DATE ISO 8601
        '/\b\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])\b/' => 'Y-m-d',
        '/\b\d{4}-(0[1-9]|[1-2][0-9]|3[0-1])-(0[1-9]|1[0-2])\b/' => 'Y-d-m',
        '/\b(0[1-9]|[1-2][0-9]|3[0-1])-(0[1-9]|1[0-2])-\d{4}\b/' => 'd-m-Y',
        '/\b(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])-\d{4}\b/' => 'm-d-Y',

        '/\b\d{4}\/(0[1-9]|[1-2][0-9]|3[0-1])\/(0[1-9]|1[0-2])\b/' => 'Y/d/m',
        '/\b\d{4}\/(0[1-9]|1[0-2])\/(0[1-9]|[1-2][0-9]|3[0-1])\b/' => 'Y/m/d',
        '/\b(0[1-9]|[1-2][0-9]|3[0-1])\/(0[1-9]|1[0-2])\/\d{4}\b/' => 'd/m/Y',
        '/\b(0[1-9]|1[0-2])\/(0[1-9]|[1-2][0-9]|3[0-1])\/\d{4}\b/' => 'm/d/Y',

        '/\b\d{4}\.(0[1-9]|1[0-2])\.(0[1-9]|[1-2][0-9]|3[0-1])\b/' => 'Y.m.d',
        '/\b\d{4}\.(0[1-9]|[1-2][0-9]|3[0-1])\.(0[1-9]|1[0-2])\b/' => 'Y.d.m',
        '/\b(0[1-9]|[1-2][0-9]|3[0-1])\.(0[1-9]|1[0-2])\.\d{4}\b/' => 'd.m.Y',
        '/\b(0[1-9]|1[0-2])\.(0[1-9]|[1-2][0-9]|3[0-1])\.\d{4}\b/' => 'm.d.Y',

        // for 24-hour | hours seconds
        '/\b(?:2[0-3]|[01][0-9]):[0-5][0-9](:[0-5][0-9])\.\d{3,6}\b/' => 'H:i:s.u',
        '/\b(?:2[0-3]|[01][0-9]):[0-5][0-9](:[0-5][0-9])\b/' => 'H:i:s',
        '/\b(?:2[0-3]|[01][0-9]):[0-5][0-9]\b/' => 'H:i',

        // for 12-hour | hours seconds
        '/\b(?:1[012]|0[0-9]):[0-5][0-9](:[0-5][0-9])\.\d{3,6}\b/' => 'h:i:s.u',
        '/\b(?:1[012]|0[0-9]):[0-5][0-9](:[0-5][0-9])\b/' => 'h:i:s',
        '/\b(?:1[012]|0[0-9]):[0-5][0-9]\b/' => 'h:i',

        '/\.\d{3}\b/' => '.v'
    );
    //$d = preg_replace('/\b\d{2}:\d{2}\b/', 'H:i',$d);
    $d = preg_replace( array_keys( $patterns ), array_values( $patterns ), $d );

    return preg_match( '/\d/', $d ) ? $null : $d;
}


function date_formating( $date, $format = 'd/m/Y H:i', $in_format = false, $f = '' ) {
    $isformat = date_extract_format( $date );
    $d = DateTime::createFromFormat( $isformat, $date );
    $format = $in_format ? $isformat : $format;
    if ( $format ) {
        if ( in_array( $format, [ 'Y-m-d\TH:i:s.u\Z', 'DATE_ISO8601', 'ISO8601' ] ) ) {
            $f = $d ? $d->format( 'Y-m-d\TH:i:s.' ) . substr( $d->format( 'u' ), 0, 3 ) . 'Z': '';
        } else {
            $f = $d ? $d->format( $format ) : '';
        }
    }
    return $f;
} // end function


function date_convert_format( $old = '' ) {
    $old = trim( $old );
    if ( preg_match( '/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/', $old ) ) { // MySQL-compatible YYYY-MM-DD format
        $new = $old;
    } elseif ( preg_match( '/^[0-9]{4}-(0[1-9]|[1-2][0-9]|3[0-1])-(0[1-9]|1[0-2])$/', $old ) ) { // DD-MM-YYYY format
        $new = substr( $old, 0, 4 ) . '-' . substr( $old, 5, 2 ) . '-' . substr( $old, 8, 2 );
    } elseif ( preg_match( '/^(0[1-9]|[1-2][0-9]|3[0-1])-(0[1-9]|1[0-2])-[0-9]{4}$/', $old ) ) { // DD-MM-YYYY format
        $new = substr( $old, 6, 4 ) . '-' . substr( $old, 3, 2 ) . '-' . substr( $old, 0, 2 );
    } elseif ( preg_match( '/^(0[1-9]|[1-2][0-9]|3[0-1])-(0[1-9]|1[0-2])-[0-9]{2}$/', $old ) ) { // DD-MM-YY format
        $new = substr( $old, 6, 4 ) . '-' . substr( $old, 3, 2 ) . '-20' . substr( $old, 0, 2 );
    } else { // Any other format. Set it as an empty date.
        $new = '0000-00-00';
    }
    return $new;
}

$date_1 = '13/05/2020 19:20:15.156457';
$date_2 = '25-05-2020 10:20';
$date_3 = '2020.05.20 10:20';
$date_4 = '2020.25.05 18:20';
$date_5 = '05/05/2020 12:20';
$date_6 = '05.05.2020 10:20';
$date_7 = '2020-20-05';
//-----------------------------
echo "1($date_1): " . date_formating( $date_1, false, true ) . PHP_EOL;
// echo-> isformat: d/m/Y H:i:s.u
// 1(13/05/2020 19:20:15): 2020-05-13 19:20
echo "2($date_2): " . date_formating( $date_2 ) . PHP_EOL;
// echo-> isformat: d-m-Y H:i
// 2(25-05-2020 10:20): 25/05/2020 10:20
echo "3($date_3): " . date_formating( $date_3 ) . PHP_EOL;
// echo-> isformat: Y.m.d H:i
// 3(2020.05.20 10:20): 20/05/2020 10:20
echo "4($date_4): " . date_formating( $date_4 ) . PHP_EOL;
// echo-> isformat: Y.d.m H:i
// 4(2020.25.05 18:20): 25/05/2020 18:20
echo "5($date_5): " . date_formating( $date_5 ) . PHP_EOL;
// echo-> isformat: d/m/Y H:i
// 5(05/05/2020 12:20): 05/05/2020 12:20
echo "6($date_6): " . date_formating( $date_6 ) . PHP_EOL;
// echo-> isformat: d.m.Y H:i
// 6(05.05.2020 10:20): 05/05/2020 10:20
echo "7($date_7): " . date_formating( $date_7, false, true ) . PHP_EOL;
// echo-> isformat: Y-d-m
// 7(2020-20-05): 2020-20-05
echo "Date ISO8601: = " . date_formating( $date_1, 'DATE_ISO8601' ) . PHP_EOL;
// echo-> isformat: d/m/Y H:i:s.u
// Date ISO8601: = 2020-05-13T19:20:15.156Z
echo "7($date_7): = " . date_convert_format( $date_7 );
// 7(2020-20-05): = 2020-20-05