How to convert these date formats in mysql date format

I m trying to convert date format from one format into another format for mysql database as yyyy-mm-dd

I managed to convert the following date format

2011-04-27 08:00:00 to 27-04-2011

usng the following code:

$date1 = $deal[‘departureDate’];
list($day, $month, $year) = split( ‘[/.-]’, $date1);
$date3 = "$year-$month-$day<br />
";

but it doesnt convert the following two date formats
12/11/2010 20:00:00
20110217

how do i change the code so it can convert these to the mysql format yyyy-mm-dd ?

It is beyond my control on the format of these dates as I know they include time as well and the second one does n t have even space or dashes bewtween the year, month and day

Any ideas?

thanks in advance

Try the function strtotime()

http://php.net/manual/en/function.strtotime.php

date("Y -m-d", strtotime($your_datetime_to_convert)); 

Can you provide a definitive list of date formats you need to cover?

This is the list that I need to cover at the moment:

2011-04-27 08:00:00
12/11/2010 20:00:00
2011/11/20 20:00:00
20110217

The first one seems to work.

Check this out…


<?php
$dates = array(
  'A' => '2011-04-27 08:00:00',
  'B' => '12/11/2010 20:00:00',
  'C' => '2011/11/20 20:00:00',
  'D' => '20110217'
);

$parser = new CompositeDateParser;
$parser->add(new TypeADateParser);
$parser->add(new TypeDDateParser);

foreach($dates as $type => $date){
  printf(
    "(%s) %s \
",
    $type,
    date('r', $parser->parse($date))
  );
}

/*
    (A) Wed, 27 Apr 2011 00:00:00 +0100
    (B) Thu, 01 Jan 1970 01:00:00 +0100
    (C) Thu, 01 Jan 1970 01:00:00 +0100
    (D) Thu, 17 Feb 2011 00:00:00 +0000
*/
?>

You essentially just create a new parser to handle each date format, how each object does this is up to you, but the parse method must return false on failure or a timestamp on success.

For example, to handle Type A we use…


class TypeADateParser implements IDateParse
{
  public function parse($date){
    preg_match('~^([0-9]{4})-([0-9]{2})-([0-9]{2})~', $date, $match);
    if(4 !== count($match)){
      return false;
    }
    return mktime(0, 0, 0, $match[2], $match[3], $match[1]);
  }
}

… and for Type D…


class TypeDDateParser implements IDateParse
{
  public function parse($date){
    preg_match('~([0-9]{4})([0-9]{2})([0-9]{2})~', $date, $match);
    if(4 !== count($match)){
      return false;
    }
    return mktime(0, 0, 0, $match[2], $match[3], $match[1]);
  }
}


<?php
interface IDateParse
{
  public function parse($date);
}

class CompositeDateParser implements IDateParse
{
  protected
    $parsers = array();

  public function add(IDateParse $parser){
    array_push($this->parsers, $parser);
    return $this;
  }
  public function parse($date){
    foreach($this->parsers as $parser){
      if(false !== ($time = $parser->parse($date))){
        return $time;
      }
    }
    return false;
  }
}
?>

You don’t have to use Regular Expressions, use whatever you see fit and be a strict as possible for that particular format.

How cool is that!?

Why don’t you try and create TypeBDateParser & TypeCDateParser?

Just shout if you get stuck or have any questions. :slight_smile:

I am getting an error message: Fatal error: Class ‘TypeDDateParser’ not found in line

I have added the interface IDateParse to the same page but not sure why it cant find it?

note the date will be stored in a variable

Place all of the code I posted in the same file before this bit…



<?php
$dates = array(
  'A' => '2011-04-27 08:00:00',
  'B' => '12/11/2010 20:00:00',
  'C' => '2011/11/20 20:00:00',
  'D' => '20110217'
);

$parser = new CompositeDateParser;
$parser->add(new TypeADateParser);
$parser->add(new TypeDDateParser);

foreach($dates as $type => $date){
  printf(
    "(%s) %s \
",
    $type,
    date('r', $parser->parse($date))
  );
}

/*
    (A) Wed, 27 Apr 2011 00:00:00 +0100
    (B) Thu, 01 Jan 1970 01:00:00 +0100
    (C) Thu, 01 Jan 1970 01:00:00 +0100
    (D) Thu, 17 Feb 2011 00:00:00 +0000
*/
?>

ok. but its printing this line: (A) Wed, 27 Apr 2011 00:00:00 -0700 (B) Wed, 31 Dec 1969 16:00:00 -0800 (C) Wed, 31 Dec 1969 16:00:00 -0800 (D) Thu, 17 Feb 2011 00:00:00 -0800

how to print the conversion?

The [noparse]IDateParse:parse[/noparse] method returns a Unix time-stamp if it can parse the supplied string, or false otherwise.

So, first ask [noparse]IDateParse:parse[/noparse] to parse the string, if it does not return false, format the Unix time-stamp however you wish.

Let me know if you’re having a hard time (no pun intended) understanding how this works.

Off Topic:

… sodding word filter

so I assume its rturns false in this cae that s why its not parsing it?
“format the Unix time-stamp however you wish” is that we did in the code?

I am afraid I need more clarification or a working example.

You have a working example, just not one covering all your requirements. :wink:

This line, formats the time-stamp.

date('r', $parser->parse($date))

Because there isn’t a suitable parser yet for the B & C date types, the date function casts the (bool)false to an (int)0, which means you get ‘Thu, 01 Jan 1970’ (the Unix Epoch).

Following?

Where do these dates come from and/or why are they beyond your control?

Ok I removed B and C date formats to check that it works first but it still prints the two dates as shown: (A) Wed, 27 Apr 2011 00:00:00 -0700 (D) Thu, 17 Feb 2011 00:00:00 -0800 Array.

These two dates already have parser but why its not doing the conversion?

Response to Salathe:

The dates comes in the form of a variable from a third parties in an xml file and i extract the dates as well as other details and place them in the database. Unfortunately, Each third party has a different type of date format. the good thing is I only need to implement one format at a time in my page

It is doing the conversion, it’s converting the string to a time-stamp for you. I don’t mean to offend, but how are your basic PHP skills, barring the date parser, can you read/understand the surrounding code?

Do you have any desire to understand/learn how this works or are you just looking for a solution? :slight_smile:

I think I need to clarify a bit my requirements as I think there may be a misunderstanding here. What I atually want is to convert the date from a format like this ‘12/11/2010 20:00:00’ to mysql format like 2011-04-21. I dont need the time stamp or the day name on it. The date must be in this format yyyy-mm-dd.

Also, the dates are not stored in an array. they come from an xml file and I extract them one by one and insert them in the mysql table.
Just to give you an idea, the date comes from the xml file is stored in the variable ‘Departure_Date’ and then I apply the conversion and store the conversion in another variable e.g $date as yyyy-mm-dd. the latter is inserted in the mysql table. below is just an extract from the form that does that, but using the original conversion


case "Departure_Date" 
:

.....More code..........

$product[$name] = $xmlReader->readString
    ( );
$date1 = $product['Departure_Date'];
list($day, $month, $year) = split( '[/.-:]', $date1);
$date3 =  "$year-$month-$day<br />\
";

......insert into mysql code bit---------

The good thing is I only need to do one date format at a time on a page so I dont need to create a code that convert all types in the same page

Hope this make sense?

Yes, this all makes sense.

I’m not offering a solution to your problem, I’m offering a solution which you can use to solve it. This is why I’ve asked the questions I have, which you haven’t answered.

I know basics of PHP.
A solution would help


$out = date('Y-m-d',strtotime($in));

StarLion:

I applied your code to a date in this format: 12/12/2010 09:40:00 and it
has converted some dates but some other dates are shown as 1969-12-31 on the mysql table so it hasnt converted every date. I m not sure if the present of the time stamp that makes it not work.