Convert string to time in php, wordpress CMS

2 Views

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?

Read More

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.

Post Views: 2

Related posts

Leave a Reply

3 comments

  2. 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.

  3. Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed.

    Says the documentation.

    So you want to convert those slashes to hyphens before calling strtotime().