在php，wordpress CMS中将字符串转换为时间

Question

I am writing a function in WordPress to add data from an XML file to a MySQL database. The problems happened after I got a date value from the XML. It's a string type and I format it to a datetime using this code:

$mysqlDay = date('d/m/Y',strtotime($day));

$day is a date value retrieved from the XML: "25/01/2013" (d/m/Y). And the value of $mysqlDay is "01/01/1970" (d/m/Y). Why does the variable $mysqlDay have value of the beginning day instead the $day? What should I do if I want to format it from string to date?

In a similar problem to the one above, if I want to convert a string formatted like "18:00" (H:M) to time, what should I do?

I used this code in WordPress to add these values to the database:

$wpdb->insert('wp_lps',
  array('date'=>$mysqlDay,
  'start'=> $mysqlStartTime, 
  'end'=> $mysqlEndTime),
  array('D/M/Y',
  'H:M',
  'H:M')
  );

Is this right? wp_lps is the table I created, $mysqlStartTime and $mysqlEndTime need to be formatted as H:M.

Answer 1

In my other answer, I answered the question you actually asked. However, it turns out (after much discussion in comments), that this was the wrong question.

Here's the situation: you have some XML which contains a date in d/m/Y format. You can fetch that with SimpleXML. Now you have a SimpleXML object. Now you want to get that into the database.

1. Convert the object to a string:

   $date = (string)$date;
   

2. Convert the date into a format that MySQL recognises as a date.

   $date = date('Y-m-d', strtotime(str_replace('/', '-', $date)));
   

3. Insert the date into the database using the $wpdb library.

Your problem was in a couple of places:

1. The date was in a format which strtotime() didn't recognise, for reasons laid out in my other answer.
2. You seemed to be under the impression that you need to have a date object to save a date into the MySQL database. You don't. What you want is a string in a format that MySQL recognises as a date. All queries between your code and the database is happening in a language called SQL, which is entirely happening in strings. You may be using a library which abstracts that out for you ($wpdb), but it's still strings under the hood.
3. You seemed to think that date() and/or strtotime() returned date objects. They don't. The date() function returns a string. The strtotime() function returns an integer (which is a UNIX timestamp).
4. What you were trying to do was to take a date (a string) in the format d/m/Y, convert it into an integer representing a UNIX timestamp, turn that back into a string representing a date in the format d/m/Y, and then add that to the database. This was failing in two places:
   • The strtotime() function assumes that if a string has slashes in it, it's an American-style date.
   • MySQL doesn't actually like that date format either. It wants Y-m-d.