Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: how to compute the aging time from datetime data's

    Hi..

    I have datetime data in my table kanban_data field chemicalweighing_dateEntry.
    sample data of chemicalweighing_dateEntry:

    2012-03-20 08:12:55
    2012-03-20 08:16:44
    2012-03-20 08:19:28
    2012-03-20 08:20:17
    2012-03-20 08:27:52
    2012-03-20 08:28:36
    2012-03-20 08:29:03
    2012-03-20 08:31:47
    2012-03-20 08:32:24
    2012-03-20 08:35:45

    I need to get the aging of chemicalweighing_dateEntry.

    using this formula:

    Aging time (00:00:00) = now() - SUM of chemicalweighing_dateEntry.


    I don't know what mysql function should I used to get the aging time.
    Thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "SUM of chemicalweighing_dateEntry" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by r937 View Post
    what does "SUM of chemicalweighing_dateEntry" mean?
    I mean total of chemicalweighing_dateEntry.

    Thank you

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by newphpcoder View Post
    I mean total of chemicalweighing_dateEntry.

    Thank you
    you can't add datetimes

    you're welcome
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    I tried this:

    Code:
    SELECT sec_to_time((SUM(time_to_sec(chemicalweighing_dateEntry))) - TIME_TO_SEC (NOW())) FROM kanban_data;
    I'm not sure if my output : 95:24:15 is correct.

    Thank you

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by newphpcoder View Post
    I'm not sure if my output : 95:24:15 is correct.

    Thank you
    it isn't

    HINT: YOU CAN'T ADD DATETIMES

    you're welcome
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well if you don't know if 95:24:15 is correct, then how the heck are we supposed to know?

    if your sum is acting on elapsed time then its fine. but in all honesty given that a column is called 'chemicalweighing_dateEntry' then I'd expect that to be the date and time the chemicals were weighed.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by healdem View Post
    well if you don't know if 95:24:15 is correct, then how the heck are we supposed to know?

    if your sum is acting on elapsed time then its fine. but in all honesty given that a column is called 'chemicalweighing_dateEntry' then I'd expect that to be the date and time the chemicals were weighed.
    yes the elapsed time or just the chemicals stay on that station

    Thank you

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is the meaning of this metric?
    in any event you'd need to use the appropriate function from the date time functions, one of which is probably going to be subtimes

    if you then need to look at statistical functions
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by newphpcoder View Post
    Hi..
    I need to get the aging of chemicalweighing_dateEntry.

    using this formula:

    Ageing time (00:00:00) = now() - SUM of chemicalweighing_dateEntry.
    actually I doubt that equation is correct
    do you want the total elapsed time since weighing?
    presumably thats going to be
    sum( now() - chemicalweighing_dateEntry)

    or the average elapsed time
    average ( now() - chemicalweighing_dateEntry)

    but as said above you would be best using the appropriate date time function(s) to work out the elapsed time

    the concept of summing the elapsed time seems peculiar to me. in all chemical process's I've come across its usually age of the oldest batch that is tracked as this usually limits the chemical reaction properties

    technically you can sum date time values as they are stored inside the db as numeric representations but effectively its meaningless. you can summate elapsed time, but not time itself
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Dec 2010
    Posts
    136
    First, I get the elapse time using this code:

    Code:
     TIMEDIFF(compounding_DATE_ENTRY,chemicalweighing_DATE_ENTRY) AS elapse1,
               TIMEDIFF(extrusion_DATE_ENTRY,compounding_DATE_ENTRY) AS elapse2,
               TIMEDIFF(forming_DATE_ENTRY,extrusion_DATE_ENTRY) AS elapse3,
               TIMEDIFF(deflashing_DATE_ENTRY,forming_DATE_ENTRY) AS elapse4
    then I use avg to get the aging per elapse.

    here is the code:
    Code:
    age_count_wip_chemical_weighing = (SELECT SEC_TO_TIME(time_to_sec(NOW()) - AVG(TIME_TO_SEC(elapse1))) AS age_count_wip_chemical_weighing FROM kanban_data ORDER BY elapse1),
    age_count_wip_compounding = (SELECT SEC_TO_TIME(time_to_sec(NOW()) - AVG(TIME_TO_SEC(elapse2))) AS age_count_wip_compounding FROM kanban_data ORDER BY elapse2),
    age_count_wip_extrusion = (SELECT SEC_TO_TIME(time_to_sec(NOW()) - AVG(TIME_TO_SEC(elapse3))) AS age_count_wip_extrusion FROM kanban_data ORDER BY elapse3),
    age_count_wip_forming = (SELECT SEC_TO_TIME(time_to_sec(NOW()) - AVG(TIME_TO_SEC(elapse4))) AS age_count_wip_forming FROM kanban_data ORDER BY elapse4);
    Is it right my query to get the aging?

    Thank you so much
    Last edited by newphpcoder; 03-25-12 at 20:58.

Posting Permissions

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