i want to sum weight in rows.

Quote:

date |weight|

1109746885,0 |
1110103200,11408 |
1110124465,13633 |

1110288332,0 |

i want to sum each weight by a certain date!
i want to sum each date like this :
1.1.2005 08:00:00 to 2.1.2005 07:59:59
2.1.2005 08:00:00 to 3.1.2005 07:59:59
and so on....
for example
1110103200=6.3.2005 12:00
1110124465=6.3.2005 17:45:25

and both of this dates from the table i want to some as 1 date!
can i do this in 1 query ormust i run in loops over the dates?
thnaks i nadvance
peleg

    you can use sum and group by to do this but the dates must be converted using a formula so that each row to be included in a sum row has the same value. for example if you want to group by the date regardless of time

    SELECT DATE_FORMAT(date, '%d/%m/%Y') AS theday, SUM(weight) FROM tablename GROUP BY theday

    to get the exact item you wanted would be more complex maybe something like
    1.1.2005 08:00:00 to 2.1.2005 07:59:59

    this needs to check the time and and depending on the time of day and date, create a date string as above.
    if the time is less then 8 then start the string with yesterdays date plus the time add the word "to" then stick todays date at the end with the final time

    SELECT 

IF(DATE_FORMAT(date, '%H')<8, 
CONCAT(DATE_FORMAT(DATE_SUB(date, INTERVAL 1 day), '%d/%m/%Y'), "08:00:00", "to", DATE_FORMAT(date, '%d/%m/%Y'), "07:59:59"),
CONCAT(DATE_FORMAT(date, '%d/%m/%Y'), "08:00:00", "to", DATE_FORMAT(DATE_ADD(date, INTERVAL 1 day),  '%d/%m/%Y'), "07:59:59"))

AS thedaystring, 
SUM(weight) FROM tablename GROUP BY thedaystring

    let me know how this goes.

      and i let it run only on the 4 lines i write (that are in a specific time) like this :

      SELECT  
      
IF(DATE_FORMAT(date, '%H')<8,
CONCAT(DATE_FORMAT(DATE_SUB(date, INTERVAL 1 day), '%d/%m/%Y'), "08:00:00", "to", DATE_FORMAT(date, '%d/%m/%Y'), "07:59:59"), 
CONCAT(DATE_FORMAT(date, '%d/%m/%Y'), "08:00:00", "to", DATE_FORMAT(DATE_ADD(date, INTERVAL 1 day),  '%d/%m/%Y'), "07:59:59")) 

AS thedaystring,  
      
SUM(total_weight) FROM delivery
where driver_id=502018 AND date>=1109628000 AND date<=1111528799
GROUP BY thedaystring

      and still the same SUM(total_weight) result!
      any idea?

        what datatype is the date field?

        can you post the exact results of that query?

          the date is int(11) unsigned
          and the result i get is : 25564
          where actualy i shuld get :
          123 on 2/3/2005
          25401 on 6/3/2005
          400 on 8/3/2005
          where current i recive the total of all of this!

            Seems like you try formating an unix timestamp?

            the mysql date functions work with date, time, datetime and timestamp types only

            mysql timestamp for 28/01/2005 (dd/mm/yyyy) is 20050128000000 (last 6 letters are for time)

            either you convert the column to timestamp type and correct the value sor you have a look at FROM_UNIXTIME(unix_timestamp) at http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

              all i wantto do is to get the SUM of certain row's by certin dates!

                don't really read my post?

                you select entries where date is between 1109628000 and 1111528799
                These two values are no valid mysql date/time specifications and therefore the mysql date functions return crap

                1109628000 and 1111528799 are unix timestamps instead

                unix timestamp specifies the seconds since jan 1st 1970 whereas mysql timestamp is of the form YYYYYYMMDDHHIISS (year month date hour minute second)
                you see the difference?

                1109628000 and 1111528799 are 1109-62-80 (year-month-day) and 1111-52-87 for mysql

                1109628000 and 1111528799 as unix timestamps are 20050228230000
                20050322225959 if converted (formatted) to mysql timestamps.
                to do this ocnversion you need the mysql function FROM_UNIXTIME

                got it?

                  you are storing unix stamps in an integer field this makes the dates hard to work with.

                  do you understand the sql I suggested earlier? if so then this can be modified to work with the numbers you are using but you will need to work out the pattern in the timestamps and then create a field that groups them by the appropriate digits in the timestamp integer.

                  I would suggest the same as the previous response - that you change the dbase to use a date or datetime field rather than an int.

                    is there a way that i can change the current int col to a date or timestamp field at once?withougl oosing data?

                    and what the diffrence between my defenition of int(11) and tthe timestemp (tht as myuch as i see is int(14))?

                      its just different - its the number of seconds since the unix epoch some time in 1970 probably the 1st of jan.

                      try adding a column which is a datefield
                      ALTER TABLE tablename ADD COLUMN datefield DATETIME;

                      then running an update query like
                      UPDATE tablename SET datefield = FROM_UNIXTIME(date);

                      obviously your other code would need to change then to deal with date and times rather than timestamps.

                      or you could just use a timestamp field type
                      ALTER TABLE tablename ADD COLUMN timestampfield TIMESTAMP;

                      UPDATE tablename SET timestampfield = FROM_UNIXTIME(date);

                      both of these should work.

                      but i did just think of something else. since there is a FROM_UNIXTIME function which converts to a date then you might well be able to use that inside the sql i gave you before making it more complex but it should still work

                      SELECT

                      IF(DATE_FORMAT(FROM_UNIXTIME(date), '%H')<8,
                      CONCAT(DATE_FORMAT(DATE_SUB(FROM_UNIXTIME(date), INTERVAL 1 day), '%d/%m/%Y'), "08:00:00", "to", DATE_FORMAT(FROM_UNIXTIME(date), '%d/%m/%Y'), "07:59:59"),

                      CONCAT(DATE_FORMAT(FROM_UNIXTIME(date), '%d/%m/%Y'), "08:00:00", "to", DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(date), INTERVAL 1 day), '%d/%m/%Y'), "07:59:59"))

                      AS thedaystring,

                      SUM(total_weight) FROM delivery
                      where driver_id=502018 AND date>=1109628000 AND date<=1111528799
                      GROUP BY thedaystring

                      let me know if this works coz you've got me interested now.

                      good luck

                      Kris

                        so do u still recommand me to chage the columns?

                          up to you.

                          generally you should use the most appropriate data type for whatever you are storing - but since it works i'd probably leave it for now and make a note to change it in the next version of the system.

                            beacuse working with a number is faster then comparing a datedime type isnt it?

                              Write a Reply...