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

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

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 24 Jun 2004 09:59:05 -0700, ji*********@hotmail.com (JA) wrote:
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.

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@databasix 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
================================================== =========================
Jul 17 '05 #2

P: n/a
JA wrote:
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.


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

Jul 17 '05 #3

P: n/a
JA wrote:
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


From the MySQL Docs:

TIMEDIFF(expr,expr2)
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.999999'

TIMEDIFF() was added in MySQL 4.1.1.
Michael Austin.
Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.