Need help with PHP & SQL code, new to this.

[JA]

I am trying to pull a time stamp out of a database,looks like this
2004-06-24 10:12:03 . Then I need to substring it so that I can take
a difference of 2 times. Basically I making a clock in/clock out
script for my job. But I can't figure out the query and then how to
substring the time so that I can do math with just the time part and
not the date. Or if you guys/gals no if any code out there, were
someone has written a script like this, that would be great. I am
willing to send my code if you need it. It should be easy to code,
but i've never done PHP before. Thanks in advance.

Jimmy Allen

[Gary L. Burnore]

Re: Need help with PHP & SQL code, new to this.

On 24 Jun 2004 09:59:05 -0700, jimmyallen0@hot mail.com (JA) wrote:
[color=blue]
>I am trying to pull a time stamp out of a database,looks like this
>2004-06-24 10:12:03 . Then I need to substring it so that I can take
>a difference of 2 times.[/color]


You can find the difference between two timestamps with a select from
the database.


Something like elect t_start, t_end, time_format((t_ start - t_end),
'%H:%M%S) from testtab;

But I can't recall how to convert the hours (1 hr = 100, not 60)





You can also select each part of the timestamp into its own slot:


mysql> select extract(year from t_start) ys, extract(year from t_end)
ye, extract(month from t_start) ms from testtab;

Long but it works.


+------+------+------+------+------+------+
| ys | ye | ms | me | ds | de |
+------+------+------+------+------|------|
| 2004 | 2004 | 1 | 2004 | 2004 | 1 |
+------+------+------+------+------+------+
--
gburnore@databa six dot com
---------------------------------------------------------------------------
How you look depends on where you go.
---------------------------------------------------------------------------
Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
| ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
DataBasix | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
| ÝÛ³ 3 4 1 4 2 ݳ޳ 6 9 0 6 9 ÝÛ³
Black Helicopter Repair Svcs Division | Official Proof of Purchase
=============== =============== =============== =============== ===============
Want one? GET one! http://www.databasix.com
=============== =============== =============== =============== ===============

[Andy Barfield]

Re: Need help with PHP & SQL code, new to this.

JA wrote:[color=blue]
> I am trying to pull a time stamp out of a database,looks like this
> 2004-06-24 10:12:03 . Then I need to substring it so that I can take
> a difference of 2 times. Basically I making a clock in/clock out
> script for my job. But I can't figure out the query and then how to
> substring the time so that I can do math with just the time part and
> not the date.[/color]

Hi Jimmy,

Why do you need to ignore the date - you could confuse the issue if you
have people working over the midnight hour. Why not just do the math on
the whole date/time string.

Extract the data from the database, using something like:
SELECT login_time from tbl_whatever;
SELECT NOW();

- assigning each to a variable within your script. I've hardcoded the
date/time string below for ease, but you would populate them using the
queres above. Using NOW() will give you the current time from the MySQL
server.

run your two dates through a function which does this:
<?php
$time1 = strtotime("2004-06-01 07:30");
$time2 = strtotime("2004-06-01 18:32");

$interval = $time2 - $time1;

echo "The time difference is : {$interval} seconds<br>";
echo "which equates to : ".strftime("%H: %M", $interval);

?>

Hope this helps, regards,

Andy

[Michael Austin]

Re: Need help with PHP &amp; SQL code, new to this.

JA wrote:[color=blue]
> I am trying to pull a time stamp out of a database,looks like this
> 2004-06-24 10:12:03 . Then I need to substring it so that I can take
> a difference of 2 times. Basically I making a clock in/clock out
> script for my job. But I can't figure out the query and then how to
> substring the time so that I can do math with just the time part and
> not the date. Or if you guys/gals no if any code out there, were
> someone has written a script like this, that would be great. I am
> willing to send my code if you need it. It should be easy to code,
> but i've never done PHP before. Thanks in advance.
>
> Jimmy Allen[/color]

From the MySQL Docs:

TIMEDIFF(expr,e xpr2)
TIMEDIFF() returns the time between the start time expr and the end
time expr2. expr and expr2 are time or date-and-time expressions, but
both must be of the same type.

mysql> SELECT TIMEDIFF('2000: 01:01 00:00:00',
-> '2000:01:01 00:00:00.000001 ');
-> '-00:00:00.000001 '
mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001 ',
-> '1997-12-30 01:01:01.000002 ');
-> '46:58:57.99999 9'

TIMEDIFF() was added in MySQL 4.1.1.


Michael Austin.