From: Peter Brawley
Date: May 18 2006 3:10pm
Subject: Re: duration query: how to ?
List-Archive: http://lists.mysql.com/mysql/198058
Message-Id: <446C8E4E.3090308@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Bob

 >I'd like to retrieve the total amount of time the articles have been 
in the
 >site from 2005-01-01 to 2005-01-31 and divide them by 365 (or 366 if leap
 >year).

In a valid-time app like yours, it is usually more sound to mark 
"current" with a
far-in-the-future date than with NULL. Arguably, using NULL to denote 
"current"
misuses NULL. NULLs slow query performance.Queries are more straightforward
if you denote "current" as ending on the largest date possible. For 
MySQL, that would
be 9999-12-31.

Duration in years between a startdate and NOW(), averaging leap years, is
DATEDIFF( NOW(), startdate)/365.2422. To account for leap years exactly
in such a formula requires a UDF, a stored function, or a calendar table.

PB

-----

> Hi,
>
> My clients can put their articles on my website. They pay for the real
> utilisation of the site. If a client has used 2 slots for all year, 
> then he
> pays for them.
>
> Now my articles table has a starddate (when the client has started to 
> sell
> his product) and an enddate (when the article is sold).
>
> I'd like to retrieve the total amount of time the articles have been 
> in the
> site from 2005-01-01 to 2005-01-31 and divide them by 365 (or 366 if leap
> year).
>
> if an article has been online from 2004-10-01 to null (meaning the 
> article
> hasn't been sold yet) then I may count 365 days
>
> How to do so with a query ? fields are client.idclient, article.idclient,
> article.idarticle, article.startdate, article.enddate.
>
> Please help me, I don't know how to retrieve those values and I need 
> them to
> get payed.
>
> Bob
>
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.6.0/341 - Release Date: 5/16/2006