db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Sum of time
Date Thu, 19 Jul 2007 12:51:44 GMT

> -----Original Message-----
> From: Mark Thornton [mailto:mthornton@optrak.co.uk]
> Sent: Thursday, July 19, 2007 2:38 AM
> To: Derby Discussion
> Subject: Re: Sum of time
> Dmitri Pissarenko wrote:
> > On 7/18/07, Raymond Kroeker <raymond@thinkparity.com> wrote:
> >> Can I ask why you wouldn't store the duration as a simple bigint
> >> representing the number of seconds/milliseconds?
> >
> > This field must have time type because a report is based on it. The
> > report is done with Crystal Reports and I need the duration to be
> > printed as hours, minutes and seconds (01:30:40), not as
> > seconds/milliseconds.
> Your approach may fail if the duration reaches or exceeds 24 hours.
> Attempting to set a time field to a value greater than 24 hours often
> results in an exception.
> Mark Thornton

There are a couple of ways of doing this.

You could store the duration in milliseconds and then have a "duration"
column which is an alpha numeric representation of the duration.

On an after insert/update trigger you would take the number being stored,
and convert it in to the hours:minutes:seconds format via a store procedure,
then store it in your second column. 

One column for mathematics use and one for human reporting use.

Or you could try and figure out if Crystal reports will let you reformat a
value from the database so you don't have to store the human readable form.
(I haven't looked at crystal reports in *ages* but I'm pretty sure that they
should have some control over formatting of data from the database.



View raw message