By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,189 Members | 2,167 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,189 IT Pros & Developers. It's quick & easy.

Convert Date Time into desired format

P: 19
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.

Expand|Select|Wrap|Line Numbers
  1.   $vocc_dt="04:02:27 16/01/2009";  
  2.   $vrec_dt="08:02:27 16/01/2009";
  3.   $occ_dt=strtotime($vocc_dt);
  4.   $rec_dt=strtotime($vrec_dt);
  5.   $for_vocc_d = date('j-M-y',$occ_dt);
  6.   $for_vocc_t = date('H:i:s',$occ_dt);
  7.   $for_vrec_d = date('j-M-y',$rec_dt);
  8.   $for_vrec_t = date('H:i:s',$rec_dt);
  9.  
  10.    echo "Occurance Date Time: $for_vocc_d $for_vocc_t<br>";
  11.    echo "Recovery Date Time : $for_vocc_d $for_vocc_t<br>";
  12.    //This script gives invalid date time like 1-Jan-70 00:00:00 but if i use like below..
  13.    $vocc_dt="04:02:27 1/16/2009";  
  14.    $vrec_dt="08:02:27 1/16/2009";              
  15.     //it gives accurate output....
  16.  
  17.    //for time difference I have used the following php code:
  18.     function date_diff($d1, $d2){
  19.     $d1 = (is_string($d1) ? strtotime($d1) : $d1);
  20.     $d2 = (is_string($d2) ? strtotime($d2) : $d2);
  21.     $diff_secs = abs($d1 - $d2);
  22.     $base_year = min(date("Y", $d1), date("Y", $d2));
  23.     $diff = mktime(0, 0, $diff_secs, 1, 1, $base_year);
  24.     return $diff_secs;    
  25.     }
  26.  
  27.   $vduration = date_diff("$vocc_dt", "$vrec_dt");
  28.   $unith =3600;      
  29.   $unitm =60;         
  30.   $hh = intval($vduration / $unith);    
  31.   $ss_remaining = ($vduration - ($hh * 3600));    
  32.   $mm = intval($ss_remaining / $unitm);   
  33.   $ss = ($ss_remaining - ($mm * 60));
  34.   //It also gives the accurate duration. 
  35.  
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
Jan 17 '09 #1
Share this Question
Share on Google+
4 Replies


Atli
Expert 5K+
P: 5,058
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.
Jan 17 '09 #2

P: 19
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
Jan 21 '09 #3

Atli
Expert 5K+
P: 5,058
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:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. header("Content-Type: text/plain");
  3.  
  4. // Target format
  5. $format = "j-M-y H:i:s";
  6.  
  7. // Unformatted dates
  8. $f1_raw = "04:02:27 16/01/2009";
  9. $f2_raw = "16/01/2009 13:53:19";
  10. $f3_raw = "00901E+13";
  11. $f4_raw = "Wed Jan 14 00:09:09 BDT 2009";
  12.  
  13. // Convert the first and second
  14. $f1_formatted = date($format, strtotime(str_replace("/", "-", $f1_raw)));
  15. $f2_formatted = date($format, strtotime(str_replace("/", "-", $f2_raw)));
  16.  
  17. // Convert third
  18. $f3_formatted = date($format, strtotime((float)$f3_raw));
  19.  
  20. // Convert fourth
  21. $f4_formatted = date($format, strtotime($f4_raw));
  22.  
  23. // Print the results
  24. echo "Format 1: $f1_formatted".PHP_EOL;
  25. echo "Format 2: $f2_formatted".PHP_EOL;
  26. echo "Format 3: $f3_formatted".PHP_EOL;
  27. echo "Format 4: $f4_formatted".PHP_EOL;
  28. ?>
Which prints:
Expand|Select|Wrap|Line Numbers
  1. Format 1: 16-Jan-09 04:02:27
  2. Format 2: 16-Jan-09 13:53:19
  3. Format 3: 21-Jan-09 04:01:00
  4. Format 4: 14-Jan-09 10:09:09
Jan 21 '09 #4

P: 19
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 !!
Jan 22 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.