I want to format a date for SQL, but I am getting an unexpected result:
echo date('Y-m-d H:i:s', strtotime("February 13 2022 10:08 PM")); //2022-02-13 22:08:00
echo date('Y-m-d H:i:s', strtotime("March 23 2022 00:20 AM")); //1970-01-01 00:00:00
How can I correctly parse these dates?
If the goal is to repair the data because you are tasked with using flawed data, you can use a regular expression to cut off any foul or unneeded AM or PM substrings.
Code: (Demo)
$tests = [
"February 13 2022 10:08 PM",
"March 23 2022 00:20 AM",
"April 3 2022 11:20 PM",
"May 28 2022 12:20 AM",
"June 12 2022 21:20 PM",
"July 4 2022 13:20 AM",
];
$tests = preg_replace(
[
'/(?:0\d|1[01]):\d{2}\K AM/', // 00:00 through 11:59 do not need AM
'/(?:2\d|1[3-9]):\d{2}\K [AP]M/', // 13:00 through 23:59 do not need AM or PM
],
'',
$tests
);
var_export($tests);
foreach ($tests as $test) {
echo "\n" . date('Y-m-d H:i:s', strtotime($test));
}
Output:
array (
0 => 'February 13 2022 10:08 PM',
1 => 'March 23 2022 00:20',
2 => 'April 3 2022 11:20 PM',
3 => 'May 28 2022 12:20 AM',
4 => 'June 12 2022 21:20',
5 => 'July 4 2022 13:20',
)
2022-02-13 22:08:00
2022-03-23 00:20:00
2022-04-03 23:20:00
2022-05-28 00:20:00
2022-06-12 21:20:00
2022-07-04 13:20:00
This is because strtotime("March 23 2022 00:20 AM") returns FALSE, because it is an invalid date as OMi Shah pointed out. And apparently FALSE being evaluated as 0 by date() function, try print date('Y-m-d H:i:s', FALSE); and see for yourself, it would print 1970-01-01 03:00:00 same as date('Y-m-d H:i:s', 0);
If you have invalid dates, you need to do error checking:
$unixTime = strtotime('March 23 2022 00:20 AM');
if ($unixTime === false) {
// Report error
} else {
$normalizedString = date('Y-m-d H:i:s', $unixTime);
}
Date parsing functions can often been too lenient (for example, strtotime('PDT') produces current time in PDT time zone), so it also doesn't hurt to convert back to the original format and see whether you get the same value back.
You may also want to fix errors, but that highly depends on what the errors look like and what resolution you want:
'March 23 2022 00:20 AM' // Replace 00:... AM dates with valid representation
'February 31 2023 09:55 PM' // Make it March 3? Throw error?
'(no data)' // Nothing fixable here
12:20 AMif it's 12 hours time instead of00:20 AMwhich represents 24-hours time style.