Convert Date Time into desired format

[ahmurad]

Dear Brothers,

I am struggling the following four Date-Time type values which were inputted into MYSQL database in different tables. As
MYSQL Default Time Format: YYYY-MM-DD HH:MM:SS, So I used the MYSQL Tables DateTime Type as Text.

Time-formet 1. 04:02:27 16/01/2009
Time-formet 2. 16/01/2009 13:53:19
Time-formet 3. 00901E+13
Time-formet 4. Wed Jan 14 00:09:09 BDT 2009

I need to convert/change each date-time into j-M-y(14-Jan-09) H:i:s(00:00:00) format.

For converting Time-formet 1. I have used the following PHP code.

  $vocc_dt="04:02:27 16/01/2009";  
  $vrec_dt="08:02:27 16/01/2009";
  $occ_dt=strtotime($vocc_dt);
  $rec_dt=strtotime($vrec_dt);
  $for_vocc_d = date('j-M-y',$occ_dt);
  $for_vocc_t = date('H:i:s',$occ_dt);
  $for_vrec_d = date('j-M-y',$rec_dt);
  $for_vrec_t = date('H:i:s',$rec_dt);
 
   echo "Occurance Date Time: $for_vocc_d $for_vocc_t<br>";
   echo "Recovery Date Time : $for_vocc_d $for_vocc_t<br>";
   //This script gives invalid date time like 1-Jan-70 00:00:00 but if i use like below..
   $vocc_dt="04:02:27 1/16/2009";  
   $vrec_dt="08:02:27 1/16/2009";              
    //it gives accurate output....
 
   //for time difference I have used the following php code:
    function date_diff($d1, $d2){
    $d1 = (is_string($d1) ? strtotime($d1) : $d1);
    $d2 = (is_string($d2) ? strtotime($d2) : $d2);
    $diff_secs = abs($d1 - $d2);
    $base_year = min(date("Y", $d1), date("Y", $d2));
    $diff = mktime(0, 0, $diff_secs, 1, 1, $base_year);
    return $diff_secs;    
    }
 
  $vduration = date_diff("$vocc_dt", "$vrec_dt");
  $unith =3600;      
  $unitm =60;         
  $hh = intval($vduration / $unith);    
  $ss_remaining = ($vduration - ($hh * 3600));    
  $mm = intval($ss_remaining / $unitm);   
  $ss = ($ss_remaining - ($mm * 60));
  //It also gives the accurate duration. 
 

I have spent much time to change these time formats into my desired format (14-Jan-09 00:00:00).Please give me an efficient
solution for these (4 types) date time types.
I am waiting for your valuable help.

Kind Regards,
A H MUrad

[Atli]

Hi.

All you need to do, really, is to convert each date into a unix timestamp.
The strtotime function can do that for you, provided that you can provide a date in a common format.
Then you can use the date function to get them into whatever format you need.

The first two formats you had are almost correct. Just replace the forward-slashes (/) with dashes (-) and run them through strtotime().

The third and the fourth are already in valid format. Just remember that the third is a float, so it should not be used as a string.

[ahmurad]

Dear Brother Atli,

Thank you very much. Really your reply was helpful for me. Here I had to do an additional task besides your advice to convert the DB time string in US time format (d-m-y to m-d-y) , as strtotime() expects m-d-y format.

Thanks the group members also.

Regards
Murad

[Atli]

Glad I could help.

I did create a little example code tho, showing the most efficient way (I can think of) to do this. An alternative method, if you will:

<?php
header("Content-Type: text/plain");
 
// Target format
$format = "j-M-y H:i:s";
 
// Unformatted dates
$f1_raw = "04:02:27 16/01/2009";
$f2_raw = "16/01/2009 13:53:19";
$f3_raw = "00901E+13";
$f4_raw = "Wed Jan 14 00:09:09 BDT 2009";
 
// Convert the first and second
$f1_formatted = date($format, strtotime(str_replace("/", "-", $f1_raw)));
$f2_formatted = date($format, strtotime(str_replace("/", "-", $f2_raw)));
 
// Convert third
$f3_formatted = date($format, strtotime((float)$f3_raw));
 
// Convert fourth
$f4_formatted = date($format, strtotime($f4_raw));
 
// Print the results
echo "Format 1: $f1_formatted".PHP_EOL;
echo "Format 2: $f2_formatted".PHP_EOL;
echo "Format 3: $f3_formatted".PHP_EOL;
echo "Format 4: $f4_formatted".PHP_EOL;
?>

Which prints:

Format 1: 16-Jan-09 04:02:27
Format 2: 16-Jan-09 13:53:19
Format 3: 21-Jan-09 04:01:00
Format 4: 14-Jan-09 10:09:09

[ahmurad]

Yes !! your code is most efficient. I have done this task another 2 ways. But thats the most efficient, easiest solution !! I accepted.

Thnx brother !!