Stuck with dates - Codewalkers

Codewalkers Forums Sponsor:

February 14th, 2004, 10:35 AM

Anonymous

Registered User

Codewalkers God 35th Plane (22000 - 22499 posts)

Join Date: Apr 2007

Posts: 22,309

Time spent in forums: < 1 sec
Reputation Power: 24

Stuck with dates

Hi guys
I really don't understand datetime columns in Mysql or do I know where to start when it comes to php. Can anyone get me started with possible a php snibblit of code that takes two datetime fields in MySql and gives me average of time between the to columns.

February 14th, 2004, 01:27 PM

nawlej

Contributing User

Join Date: Apr 2007

Location: Dallas, Tx. USA

Posts: 2,008

Time spent in forums: 11 h 7 m 51 sec
Reputation Power: 4

RE: Stuck with dates

Quote:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. (``Supported'' means that although earlier values might work, there is no guarantee that they will.)

Now, for what you would like to accomplish, I would use a timestamp field in mysql instead. I say this because it stores the information as a unix timestamp, and can easily be added and subtracted to. A unix timestamp is how many seconds it has been since January 1st, 1970. You can utilize the time() function when you want to generate a timestamp on the current time, date. If you need a custom timestamp, you can make one with the function mktime. I recommend using this over the datetime format, because there is a lot less work involved in using it, and because you are new to php, it would be a little better for you to "cut your teeth" on.

February 14th, 2004, 04:10 PM

brewthatistrue

Contributing User

Join Date: Apr 2007

Location: CA, USA

Posts: 277

Time spent in forums: 38 sec
Reputation Power: 2

RE: Stuck with dates

working with time in php might get a little more in depth

i agree with nawlej. however, if you need it working NOW (and you WILL fix it later) you can always use a mysql function

Code:

SELECT UNIX_TIMESTAMP(YOUR_DATE_COLUMN) AS CUSTOM_NAME FROM YOUR_TABLE

then you can refer to it in php (assuming you do the intermediate steps) as

php Code:

Original - php Code
<?php echo $row['custom_name']; //if you don't use an alias you would have to use //echo $row['UNIX_TIMESTAMP(YOUR_DATE_COLUMN)']; ?>

<?php echo $row['custom_name'];
//if you don't use an alias you would have to use
//echo $row['UNIX_TIMESTAMP(YOUR_DATE_COLUMN)'];
?>

February 15th, 2004, 06:51 PM

Anonymous

Registered User

Join Date: Apr 2007

Posts: 22,309

Time spent in forums: < 1 sec
Reputation Power: 24

RE: Stuck with dates

O.k. I am almost there.

php Code:

Original - php Code
SELECT sum((UNIX_TIMESTAMP(cust_serv_agent_time_compl) - UNIX_TIMESTAMP(cust_serv_agent_time_in))) sum from talbe_name

SELECT sum((UNIX_TIMESTAMP(cust_serv_agent_time_compl) - UNIX_TIMESTAMP(cust_serv_agent_time_in))) sum from talbe_name

then I do a quick division sum / (rows returned) and I have an average. Now my only problem is, as an example how do I convert 120 seconds into 02:00 minutes from the unix_time stamp???

February 16th, 2004, 12:32 PM

Anonymous

Registered User

Join Date: Apr 2007

Posts: 22,309

Time spent in forums: < 1 sec
Reputation Power: 24

RE: Stuck with dates

O.k. got it Thought I would post it. someone might need this or could mod it for their own use.
Created this function so that you can pass it a average number of seconds. Once you have your unix seconds, say 120 send that number to this function and it will return it all nice and tidey in minute:second look and feel.

php Code:

Original - php Code
// ---------------------- // convert unix time to minutes second function get_seconds_to_minutes($seconds) { // $seconds = 1200; $time = date('i s',$seconds); $minSecs = explode(' ',$time); $wrapit = "{$minSecs[0]}:{$minSecs[1]} "; return $wrapit; }

 
// ----------------------
// convert unix time to minutes second
function get_seconds_to_minutes($seconds)
{
// $seconds = 1200;
$time = date('i s',$seconds);
$minSecs = explode(' ',$time);
$wrapit = "{$minSecs[0]}:{$minSecs[1]} ";
return $wrapit;
}

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: