Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Cool Unanswered: Is there an aggregate function to sum a datetime field values?

    Hi all...

    how I can obtain the sum of a datetime field as aggregate function?

    Given a set of records I need to calculate the number of records (count (*)) and the sum of a field of type datetime.
    Is this possible? how?

    Thanks..

    Massimo

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    massimo, i don't think you can do it, it doesn't make sense

    please tell me, what is the sum of 2003-12-31 plus 1969-07-20 plus 1991-01-16 plus 1986-01-28?

    thank you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Is there an aggregate function to sum a datetime field values?

    Originally posted by maxdaros
    Hi all...

    how I can obtain the sum of a datetime field as aggregate function?

    Given a set of records I need to calculate the number of records (count (*)) and the sum of a field of type datetime.
    Is this possible? how?

    Thanks..

    Massimo
    what do u mean by sum of dates?

  4. #4
    Join Date
    Jan 2004
    Posts
    3
    Hi,

    I need to sum only hh:mm:ss fields, not all the data entry.
    So, if I've 00:43:21 + 01:10:00, then I obtain 01:53:21...

    All the datetime entry doesn't make sense.

    Thanks

    Massimo

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "All the datetime entry doesn't make sense."

    yeah, but that's what you asked

    you are using a datetime column to store intervals, i.e. hours and minutes?

    why?

    okay, this may not be the best approach, but here's what i would do

    pull out the hours and minutes and seconds as substrings from a CONVERTed string, cast them as integers, calculate an expression in seconds

    here's my test query --
    Code:
    select convert(char(26),adate,109) as mydate 
    , cast(substring(convert(char(26),adate,109),13,2) as integer) * 3600 
    + cast(substring(convert(char(26),adate,109),16,2) as integer) * 60 
    + cast(substring(convert(char(26),adate,109),19,2) as integer)    as myseconds
    from somedates
     order by 1
    
    mydate	                     myseconds
    Feb  2 2002  9:37:00:000AM	34620
    Jan  1 2001 11:11:00:000AM	40260
    Jan  2 2001 12:34:00:000PM	45240
    Jan  3 2001  3:03:00:000AM	10980
    Jul  7 2007 12:00:00:000AM	43200
    Sep  9 1949 12:00:00:000AM	43200
    now, you can sum up the seconds, and then figure out some way of representing the total --
    Code:
    select sum(
     cast(substring(convert(char(26),adate,109),13,2) as integer) * 3600 
    + cast(substring(convert(char(26),adate,109),16,2) as integer) * 60 
    + cast(substring(convert(char(26),adate,109),19,2) as integer) 
       ) as totalseconds
    , sum(
     cast(substring(convert(char(26),adate,109),13,2) as integer) * 3600 
    + cast(substring(convert(char(26),adate,109),16,2) as integer) * 60 
    + cast(substring(convert(char(26),adate,109),19,2) as integer) 
       ) / 60.00 as totalminutes
    , sum(
     cast(substring(convert(char(26),adate,109),13,2) as integer) * 3600 
    + cast(substring(convert(char(26),adate,109),16,2) as integer) * 60 
    + cast(substring(convert(char(26),adate,109),19,2) as integer) 
       ) / 3600.00 as totalhours
       
    from somedates
     order by 1
     
     totalseconds	totalminutes	totalhours
    217500	3625.000000	60.4166666
    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This give the sum of the seconds since midnight for each record.

    select sum(datediff(second, convert(char(10), YourDate, 120), YourDate)) TotalSeconds
    from YourTable
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    doh!

    good one, blindman
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2004
    Posts
    3
    Hi,

    I've used this function :
    SUM ( DATEPART(hh, MyDateField) * 3600 + DATEPART(mi, MyDateField) * 60 + DATEPART(ss, MyDateField) )

    now SQL returns the sum in seconds.....

    and I use a java method called fromSecondsToStringData () in a java bean that I use to render the query result in a JSP page...

    private String fromSecondsToStringDate ( int totalSeconds )
    {
    int hours = totalSeconds / 3600;
    int minutes = ( totalSeconds % 3600 ) / 60;
    int seconds = ( totalSeconds % 3600 ) % 60;

    String hoursStr = new Integer ( hours ).toString ();
    String minutesStr = new Integer ( minutes ).toString ();
    String secondsStr = new Integer ( seconds ).toString ();

    if ( hoursStr.length () < 2 )
    hoursStr = "0" + hoursStr;

    if ( minutesStr.length () < 2 )
    minutesStr = "0" + minutesStr;

    if ( secondsStr.length () < 2 )
    secondsStr = "0" + secondsStr;

    return new String ( hoursStr + ":" + minutesStr + ":" + secondsStr );
    }


    and this appear to work fine.
    Thanks

    Massimo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •