Date conversion in PHP (and back)

Jaeger

Hi folks,

Before I ask, I realise there's tonnes of pages out there on PHP and the various functions you can use to manipulate dates and times, but having read a lot of it, I think I'm further away from the solution I went looking for in the first place.

I have a form that takes an input in the form of "dd/mm/yyyy hh:mm:ss", and wish to store it in proper date & time format in an SQL table. Initially I had been OK with storing them as a string, but now I need to manipulate dates and times individually, and as proper dates and times.

Ideally I'm looking for the form to take the same input of dd/mm/yyyy hh:mm:ss, but then convert this into a date and time useable in SQL. I also need to be able to take the stored information in the database, and re-present it to the user in the same dd/mm/yyyy format.

I had initially considered writing a basic function to convert dd/mm/yyyy hh:mm:ss into a string format of yyyy/mm/dd hh:mm:ss, and then storing the returned value in the SQL table, before I gathered I should be able to do that using existing functionality.

Any advice appreciated.

PS. Do I need to store the date and time in separate fields?

Liam Byrne

Jaeger wrote: »

I had initially considered writing a basic function to convert dd/mm/yyyy hh:mm:ss into a string format of yyyy/mm/dd hh:mm:ss, and then storing the returned value in the SQL table, before I gathered I should be able to do that using existing functionality.

That's pretty much what you need to do, except you also need to replace the slashes with hyphens.

Jaeger wrote: »

PS. Do I need to store the date and time in separate fields?

Depends on whether you'll ever need them separately. If not, it can a "date and time" field

Jaeger

Thanks for that. Bugger, was hoping there was already something readymade

If I need them separately, I think it'll mostly be for display purposes (at least for now). Have been playing around and am able to use something like $date = date("d/m/Y H:m:s", strtotime($dateinSQLtable)), or for just time $time = date("H:m:s", strtotime($dateinSQLtable));

Is this a no-no?

Liam Byrne

$tmp=explode(" ",$dateinSQLtable);
$date=implode("/",array_reverse(explode("/",$tmp[0])));
$time=$tmp[1];

louie

strtotime function available in PHP is your answer
[php]
//e.g.
$date = "2009/03/30 12:00:00";
$new_date = date("d-m-Y H:i:s", strtotime($date)); //for DB
$display_date = date("d-m-Y", strtotime($date));
//or
$display_date_x = date("D M Y", strtotime($date));
[/php]

Jaeger

Thanks Louie.

Edit: Excitement past, read that initially thinking your $date variable matched the format dd/mm/yyyy hh:mm:ss. Still need to write a conversion function myself?

louie

no you don't.
using strtotime function you can convert the string to any format you need. My example was just that.
[php]
$new_date = date("d/m/Y H:i:s",strtotime($date));
[/php]

Jaeger

Given today's date, it's mixing the day and month figures. Any easy way to resolve?

$inputdate = "08/07/2009 07:32:30";
$outputdate = date("d-m-Y H:i:s", strtotime($inputdate));
echo $outputdate; // produces "07-08-2009 07:32:30"

Edit: this may be down to a config setting in php.ini somewhere.

louie

try this
[php]
$inputdate = "08/07/2009 07:32:30";
$outputdate = date("d-m-Y H:i:s", strtotime(format_date($inputdate)));
echo $outputdate;
function format_date($str){
if($str == "") return;
$x = explode(" ",$str);
$y = explode("/",$x[0]);
$str = $y[2]."/".$y[1]."/".$y[0]." ".$x[1];//format the date to Y/m/d H:i:s
return $str;
}
[/php]

Jaeger

That works, nicely done. Thanks for the method, should be able to build on that if I need to

turgon

Is it bad practice to store times in mysql as simply unix timestamps in integer fields?

Jaeger

Can't say that I know turgon, but if it was me, I'd store in the MySQL format, and when looking to get the unix equivalent use an SQL query like:

SELECT UNIX_TIMESTAMP(columnwithdatetime) as unixdatetime from tablename

...then again, I'm here for help too, so don't take my word

Speaking of which: Have run into trouble further down the line when trying to calculate the number of hours and minutes between a given date and time, and the current date and time.

Liam & Louie - Recommended methods of calculating hours and minutes from date and time to another date and time, and output as hh Hours mm Minutes? Convert to unix timestamp or not? Have also googled this one, and attempting to customise a couple of different functions, but am wondering if I'm trying to reinvent the wheel again :-/

louie

Have a look at "mktime" php function

http://ie.php.net/mktime

Jaeger

Not sure if that was directed at me, but here's where I am now:

function timeDifference($oldtime) {
  $time = time(); 
  $elapsed = $time - $oldtime; 
  $years = date('Y', $elapsed) - 1970; // Calculate the year subtracting the beginning year 1970
  $months = date('m', $elapsed); 
  $days = date('d', $elapsed); 
  $hours = date('H', $elapsed);
  $min = date('i', $elapsed); // 
  return array('years'=>$years, 'months'=>$months, 'days'=>$days, 'hours'=>$hours, 'minutes'=> $min); // Return values as an array
}
// $arrayname[starttime] is populated by SELECT UNIX_TIMESTAMP(StartTime) as StartTime from table;

$starttime = $arrayname[starttime];
$friendlystarttime = date("d/m/Y H:i:s", $starttime);
$friendlycurrenttime = date("d/m/Y H:i:s", time());
echo "Time held in table: " .$starttime ." <b>(".$friendlystarttime.")</b><br />";
echo "Current date/time (UNIX): " . time() ." <b>(".$friendlycurrenttime.")</b><br />";
$timediff = timeDifference($starttime);
print_r($timediff);

Produces:

Time held in table: 1247229600 (10/07/2009 13:40:00)
Current date/time (UNIX): 1247231362 (10/07/2009 14:09:22)
Array ( [years] => 0 [months] => 01 [days] => 01 [hours] => 01 [minutes] => 29 [seconds] => 22 )

The minutes and seconds look good, but why are the month, day and hour variables populated with 01? The function is one I picked up over several searches, and originally did not have hours, minutes and seconds. Which is why I'm guessing it's happening.

Jaeger

Nevermind! Slightly tweaked something I came across whilst looking up date/time functions in PHP.

<?php
function compare_dates($date1, $date2){
    $blocks = array(
        array('name'=>'year','amount' => 60*60*24*365),
        array('name'=>'month','amount' => 60*60*24*31),
        array('name'=>'week','amount' => 60*60*24*7),
        array('name'=>'day','amount' => 60*60*24),
        array('name'=>'hour','amount' => 60*60),
        array('name'=>'minute','amount' => 60),
        array('name'=>'second','amount' => 1)
        );
   
    $diff = abs($date1-$date2);
    $levels = 2;
    $current_level = 1;
    $result = array();
    foreach($blocks as $block){
        if ($current_level > $levels) {break;}
        if ($diff/$block['amount'] >= 1){
            $amount = floor($diff/$block['amount']);
	    if ($amount>1) {$plural='s';} else {$plural='';}
	    if ($amount <10) {$prefix='0';} else {$prefix='';}
            $result[] = $prefix . $amount.' '.$block['name'].$plural;
            $diff -= $amount*$block['amount'];
            }
        }
    return implode(' ',$result);
	}
?>

All credit to luck lil leprechaun, src: http://us2.php.net/manual/en/ref.datetime.php