Sorry, but I've omitted part of the where clause to make the message shorter
and now I see that it was important. Here goes the select again:
SELECT A.cod_call, A.cod_link, A.nr_call , A.status,
B.dthr_occurrence as open,
DATE_SUB(C.dthr_occurence, INTERVAL B.dthr_occurrence HOUR_MINUTE) AS
during,
DATE_SUB(D.dthr_occurrence, INTERVAL B.dthr_occurrence HOUR_MINUTE)
AS late
FROM CALL A, OCCURRENCE B, OCCURRENCE C, OCCURRENCE D
WHERE A.COD_CALL = B.COD_CALL AND
A.COD_CALL = C.COD_CALL AND
A.COD_CALL = D.COD_CALL AND
A.COD_LINK = '$lnk' AND
B.DESC_OCCURRENCE = 'open' AND
C.DESC_OCCURRENCE = 'close' AND
D.DESC_OCCURRENCE = 'stop' AND
(B.DTHR_OCCURRENCE BETWEEN '$datef' AND '$datet')
Now... another possibility? Why it isn't working?
-----------------------------------------------------------
Felipe D. Ramalho
Estagiário - Datapuc - Suporte Tecnológico
+55 31 3249-7300
----- Original Message -----
From: "Keith C. Ivey" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "Felipe D. Ramalho" <[EMAIL PROTECTED]>
Sent: Wednesday, November 20, 2002 3:09 PM
Subject: Re: datetime arithmetic
> On 20 Nov 2002, at 14:40, Felipe D. Ramalho wrote:
>
> > SELECT ..., B.dthr_occurrence as open,
> > DATE_SUB(C.dthr_occurrence, INTERVAL B.dthr_occurrence
> > HOUR_MINUTE) AS during,
> > DATE_SUB(D.dthr_occurrence, INTERVAL B.dthr_occurrence
> > HOUR_MINUTE) AS late
> >
> > FROM CALL A, OCCURRENCE B, OCCURRENCE C, OCCURRENCE D
>
> The DATE_SUBs in your query make no sense. You're treating the same
> column as both a date and an "HH:MM" string representing a duration
> in hours and minutes. It can't be both.
>
> --
> Keith C. Ivey <[EMAIL PROTECTED]>
> Tobacco Documents Online
> http://tobaccodocuments.org
> Phone 202-667-6653
>
----- Original Message -----
> Hi,
>
> I'd like to obtain the number of hours and minutes between two fields
> datetime and I'm trying to proceed just like the documentation says.
>
> SELECT ..., B.dthr_occurrence as open,
> DATE_SUB(C.dthr_occurrence, INTERVAL B.dthr_occurrence
> HOUR_MINUTE) AS during,
> DATE_SUB(D.dthr_occurrence, INTERVAL B.dthr_occurrence
> HOUR_MINUTE) AS late
>
> FROM CALL A, OCCURRENCE B, OCCURRENCE C, OCCURRENCE D
>
> WHERE A.COD_CALL = B.COD_CALL AND
> A.COD_CALL = C.COD_CALL AND
> A.COD_CALL = D.COD_CALL AND
> A.COD_LINK = '$lnk' AND
> B.DESC_OCCURRENCE = 'open' AND
> C.DESC_OCCURRENCE = 'close' AND
> D.DESC_OCCURRENCE = 'stop' AND
> (B.DTHR_OCCURRENCE BETWEEN '$datef' AND '$datet')
>
> But there is a problem. At the end this query returns a the correct
result,
> but all the
> lines with the fields "during" and "late" in blank.
>
> The function DATE_SUB is returning NULL and there isn't more examples.
>
>
> Any idea about what to do?
>
> []s,
>
> -----------------------------------------------------------
> Felipe D. Ramalho
> Estagiário - Datapuc - Suporte Tecnológico
> +55 31 3249-7300
---------------------------------------------------------------------
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
