You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Dmitri Pissarenko <dm...@gmail.com> on 2007/07/17 09:57:31 UTC
Sum of time
Hello!
I have a table with with time fields. That is, each record has
a) field "place" (string) and
b) field "duration" (time).
I want to run a query like this
SELECT PLACE, SUM(DURATION)
FROM MyTable
GROUP BY PLACE
But I'm getting following error message:
SQL State = 42Y22 SQL Code = 30000 SQL Message = Aggregate SUM cannot
operate on type TIME. Exception message =
java.sql.SQLSyntaxErrorException: Aggregate SUM cannot operate on type
TIME.
How can I run this query (determine sum of duration per place) nevertheless?
TIA
Dmitri Pissarenko
--
http://www.xing.com/profile/Dmitri_Pissarenko
http://dapissarenko.blogspot.com/
Re: Sum of time
Posted by Dmitri Pissarenko <dm...@gmail.com>.
Thanks!
I solved the problem in my program.
Best regards
Dmitri Pissarenko
RE: Sum of time
Posted by Pertti Ylijukuri <pe...@gmail.com>.
Maybe easiest way is use big int type in duration field and store duration
time in milliseconds
-----Original Message-----
From: Dmitri Pissarenko [mailto:dmitri.pissarenko@gmail.com]
Sent: 17. heinäkuuta 2007 11:08
To: Derby Discussion
Subject: Re: Sum of time
Hello!
On 7/17/07, Peter Ondruska <pe...@gmail.com> wrote:
> Well, first of all:
>
> what do you expect as a value for (Date)A + (Date)B ?
>
> What you want is an interval (perhaps in seconds) instead of time as
> duration.
I don't see the difference between interval and "time as duration".
If I have following records
Place: place1 Duration: 00:00:10
Place: place2 Duration: 01:30:40
Place: place3 Duration: 20:00:00
I need the output
place1 - 21:30:50.
Best regards
Dmitri Pissarenko
--
http://www.xing.com/profile/Dmitri_Pissarenko
http://dapissarenko.blogspot.com/
Re: Sum of time
Posted by Dmitri Pissarenko <dm...@gmail.com>.
Hello!
On 7/17/07, Peter Ondruska <pe...@gmail.com> wrote:
> Well, first of all:
>
> what do you expect as a value for (Date)A + (Date)B ?
>
> What you want is an interval (perhaps in seconds) instead of time as
> duration.
I don't see the difference between interval and "time as duration".
If I have following records
Place: place1 Duration: 00:00:10
Place: place2 Duration: 01:30:40
Place: place3 Duration: 20:00:00
I need the output
place1 - 21:30:50.
Best regards
Dmitri Pissarenko
--
http://www.xing.com/profile/Dmitri_Pissarenko
http://dapissarenko.blogspot.com/
Re: Sum of time
Posted by Peter Ondruska <pe...@gmail.com>.
Well, first of all:
what do you expect as a value for (Date)A + (Date)B ?
What you want is an interval (perhaps in seconds) instead of time as
duration.
p.
On 17.7.2007, at 9:57, Dmitri Pissarenko wrote:
> Hello!
>
> I have a table with with time fields. That is, each record has
>
> a) field "place" (string) and
> b) field "duration" (time).
>
> I want to run a query like this
>
> SELECT PLACE, SUM(DURATION)
> FROM MyTable
> GROUP BY PLACE
>
> But I'm getting following error message:
>
> SQL State = 42Y22 SQL Code = 30000 SQL Message = Aggregate SUM cannot
> operate on type TIME. Exception message =
> java.sql.SQLSyntaxErrorException: Aggregate SUM cannot operate on type
> TIME.
>
> How can I run this query (determine sum of duration per place)
> nevertheless?
>
> TIA
>
> Dmitri Pissarenko
> --
> http://www.xing.com/profile/Dmitri_Pissarenko
> http://dapissarenko.blogspot.com/
RE: Sum of time
Posted by de...@segel.com.
> -----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 <ra...@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.
HTH
-G
Re: Sum of time
Posted by Mark Thornton <mt...@optrak.co.uk>.
Dmitri Pissarenko wrote:
> On 7/18/07, Raymond Kroeker <ra...@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
Re: Sum of time
Posted by Dmitri Pissarenko <dm...@gmail.com>.
On 7/18/07, Raymond Kroeker <ra...@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.
Of course, I could do the conversion (seconds/milliseconds to
HH:MM:SS) in Crystal Reports, but it's easier for me to do it this
way.
Best regards
Dmitri Pissarenko
--
http://www.xing.com/profile/Dmitri_Pissarenko
http://dapissarenko.blogspot.com/
Re: Sum of time
Posted by Raymond Kroeker <ra...@thinkparity.com>.
Can I ask why you wouldn't store the duration as a simple bigint
representing the number of seconds/milliseconds?
On 7/18/07, Dmitri Pissarenko <dm...@gmail.com> wrote:
>
> Hello!
>
> > > SELECT PLACE, SUM(SECOND(DURATION) +
> > > MINUTE(DURATION)*60 + HOUR(DURATION)*60*60)
> > > FROM MyTable
> > > GROUP BY PLACE
> > >
> > > But I still need to convert int to time.
> >
> > Out of curiosity, what happens if you add this int to the Time 00:00:00?
>
> I don't add this int to any Time. That won't work.
>
> Instead, first I convert this int (seconds) to
>
> a) hours
> b) minutes
> c) seconds.
>
> Then I create a string "hours:minutes:seconds" and parse it with
> TimeFormat.
>
> Best regards
>
> Dmitri Pissarenko
>
> --
> http://www.xing.com/profile/Dmitri_Pissarenko
> http://dapissarenko.blogspot.com/
>
--
--------------------------------------------------------------------------------
Raymond Kroeker
thinkParity Solutions Inc.
Re: Sum of time
Posted by Dmitri Pissarenko <dm...@gmail.com>.
Hello!
> > SELECT PLACE, SUM(SECOND(DURATION) +
> > MINUTE(DURATION)*60 + HOUR(DURATION)*60*60)
> > FROM MyTable
> > GROUP BY PLACE
> >
> > But I still need to convert int to time.
>
> Out of curiosity, what happens if you add this int to the Time 00:00:00?
I don't add this int to any Time. That won't work.
Instead, first I convert this int (seconds) to
a) hours
b) minutes
c) seconds.
Then I create a string "hours:minutes:seconds" and parse it with TimeFormat.
Best regards
Dmitri Pissarenko
--
http://www.xing.com/profile/Dmitri_Pissarenko
http://dapissarenko.blogspot.com/
Re: Sum of time
Posted by Daniel Noll <da...@nuix.com>.
On Tuesday 17 July 2007 18:03:43 Dmitri Pissarenko wrote:
> Hello!
>
> One obvious way is to use this:
>
> SELECT PLACE, SUM(SECOND(DURATION) +
> MINUTE(DURATION)*60 + HOUR(DURATION)*60*60)
> FROM MyTable
> GROUP BY PLACE
>
> But I still need to convert int to time.
Out of curiosity, what happens if you add this int to the Time 00:00:00?
Daniel
--
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://nuix.com/ Fax: +61 2 9212 6902
Re: Sum of time
Posted by Dmitri Pissarenko <dm...@gmail.com>.
Hello!
One obvious way is to use this:
SELECT PLACE, SUM(SECOND(DURATION) +
MINUTE(DURATION)*60 + HOUR(DURATION)*60*60)
FROM MyTable
GROUP BY PLACE
But I still need to convert int to time.
Best regards
Dmitri Pissarenko
--
http://www.xing.com/profile/Dmitri_Pissarenko
http://dapissarenko.blogspot.com/