Actually, these are a portion of a table that's contents are generated by
Radius records imported into a MySQL table, so both values are generated
simultaneously (or, at least as far as MySQL as concerned).
-----Original Message-----
From: Oson, Chris M. [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 28, 2001 15:53
To: Alberni-dot-Net
Cc: [EMAIL PROTECTED]
Subject: RE: Complex SQL Query
Aaron,
Are you inserting both values at once? Or inserting the startdate first,
then
updating the stopdate later. You may or may not be able to do this in one
query.
To determine how much time is being spent between Midnight and 6am...
select if (
time_to_sec(time_format(dateModified, "%H:%I:%s")) > 21600,
time_to_sec(time_format(dateModified, "%H:%I:%s")) -
(time_to_sec(time_format(dateModified, "%H:%I:%s")) - 21600),
time_to_sec(time_format(dateModified, "%H:%I:%s"))
)
as '12AM-6AM'
from newsStories where storyID = 1105;
**********************
You'll need to make changes to the last line (the from clause to meet your
needs)
21600 is the number of seconds between 00:00:00 and 06:00:00
The documentation on IF (expr1, expr2, expr3) goes something like this:
expr1 evaluates to T/F
IF (expr1 = T) THEN expr2 ELSE expr3
The select statment basically says...
compute the number of seconds and see if it's greater than 21600..
If so, subtract the EXCESS number of seconds AFTER 6am from the total
else keep the total number of seconds....
Now, I belive that in order to solve your problem you'll need to compare the
dates between the
first two columns. If the *days* are different, then the stopdate occurred
sometime after midnight.
In which case, you now have the means to determine what the number of
seconds between 12am-6am. You'll
then take the TOTAL number of seconds (between startdate/stopdate) and
subtract the number of seconds
from the query above and that will give you the number of seconds between
6am-12am.
You'll definitely need to lookinto IF(expr1, expr2, expr3) AND maybe
CASE something THEN something ELSE something else END to meet your needs,
but I believe I've given
you enough information to get you on your way....
one more thing....
make sure you're syntax is correct with the previous query or you WILL
return a null value. I found that out
the hard way.
one more thing.... part II
this solution doesn't take into account if the startdate/stopdate spans more
than two days....
HTH, and good luck, and if you're in a good mood, can you send me your final
solution;)
christopher oson
-----Original Message-----
From: Alberni-dot-Net [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 28, 2001 1:04 PM
To: [EMAIL PROTECTED]
Subject: Complex SQL Query
I have been trying for the last while to come up with a query that will do
the following:
Say I have table with these records:
StartDate StopDate
-------------------------------------------
2001-01-23 23:43:12 2001-01-24 04:29:12
2001-01-24 15:24:00 2001-01-26 01:15:02
2001-01-24 23:10:16 2001-01-24 23:15:59
2001-01-25 09:36:44 2001-01-27 12:11:41
The query to get the sum of all the seconds between the "StopDate" and
"StartDate" is easy. What I need to do is break that down into how many
seconds of that total were spent between midnight and 6am (and, conversely,
how many seconds of that total were spent between 6am and midnight). I know
the answer is probably very simple, but I have yet to figure it out.
Aaron Clausen
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
