There is a database in which about 600 entries have entries that have a publication date that someone just did not enter as horrible. As a result, in one column I have the following types of dates:

12-08-14

08/31/15

01/15/13

How would I massively bring all of them to the timestamp format? well, or at least to some one?

My fantasies:

I roughly imagine it this way: I make a selection of all records from the database - then you need to create some kind of tricky if condition (type format 12-08-14) then foreach where all the records that passed through the condition of format 1 are converted to the timestamp and the string is updated to the database. Accordingly, I run the file for execution. And so we repeat with each type of format.

Question:

Actually how to make a condition for the type of a specific date format? And how to do the conversion thereof in the timestamp?

I admit - my PHP skills are very modest, so I will be very grateful for the examples developed. Thank you for attention.

  • if guaranteed each element takes 2 digits, i.e. both day and month and year have two digits, then either the substr or regulars is quietly cut out .... although with a regular schedule, you can cut off in principle at once and the matter is over - Alexey Shimansky

2 answers 2

You can use the regular DateTime class. For the formats listed:

 function normalizeDate($input) { $possibleFormats = [ 'dm-y', 'd/m/y', 'dmy', ]; foreach ($possibleFormats as $format) { $rDate = \Datetime::createFromFormat($format, $input); if (!$rDate) { continue; // точно не этот формат } if ($rDate->format($format) == $input) { // перепроверили формат, это именно нужный return $rDate->format('Ym-d'); } } throw new \exception('unknown format in date string '.$input); } $testInput = [ '12-08-14', '31/08/15', '15.01.13', ]; foreach ($testInput as $input) { echo $input, "\t=> ",normalizeDate($input),PHP_EOL; }

I result in the standard for the database year-month-day data type date . Still, you must use the appropriate data types.

Separately, I will explain the strange second check: the datetime class considers that the date can be specified not necessarily according to the calendar. For example, a date with a month of 20 is acceptable, which the class interprets as August of the following year. Therefore, I format the date back to the original format and check if it matches.

  • Thank you, very interesting decision! I understand that you can add others to the array of possible formats, it is not necessary that they be in the same order (for example, 'm / d / y',) right? And the moment how should I make requests in the database? Cycle to extract / inside the second cycle to update and the input function call? - Mark Bg
  • Yes, recognizable format notation is described here: php.net/manual/ru/function.date.php m / d / y as an option, too, can be specified. Only one thing - how to define even a person, 10/01/16 - is it January 10 or October 1? Updates in the database - for only 600 lines of performance without a significant difference, therefore, an update request right within the cycle is a simple and working solution. - Small
  • Thanks for the tip, very lucidly. As for the format, the peculiarity is that, depending on the separator, there is a different order, an example where 01/02 will be in one case on February 1 and in the second on January, will not happen. In the database, something like this: separator point is always - dmy, separator dash - mdy, separator slash - d / m / y - Mark Bg
  • Then there are no problems; the example of the function will not confuse formats with different delimiters. - Small

If the format is always determined by the mask dd-mm-yy , where - a certain separator, then you can do everything with one update in the database. For MySQL, it will look like this:

 UPDATE mytable SET fld_timestamp = STR_TO_DATE( CONCAT( SUBSTRING(fld_str, 1, 2), '-', SUBSTRING(fld_str, 4, 2), '-', SUBSTRING(fld_str, 7) ), '%d-%m-%y' )
  • we first isolate the day, month and year from the string
  • further we form a line of already known format and with a known delimiter
  • convert this string to date
  • Thank you for the information! The difficulty is that there are several formats. - Mark Bg
  • @MarkBg Well then, you first need to develop a format identification algorithm - Anton Shchyrov