SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    difficult query calculating time

    I'm trying to write a query in MS Access that will return the total time from a start time and end time fields. here is what i've done.

    Code:
    SELECT clients.clientName, projectType.projectName, projectTime.projectDate, projectTime.endTime-projectTime.startTime AS ProjectTime
    FROM projectType INNER JOIN (clients INNER JOIN projectTime ON clients.clientID = projectTime.client)
    ON projectType.projectTypeID = projectTime.projectType
    ORDER BY clients.clientName;
    but it doesn't return the calculation in a time format. i tried putting # around the time fields, but it threw an error. what do i need to do?

  2. #2
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why would it return a Time though? If you take one timestamp from another, what you're asking for is a number of minutes (or seconds or microseconds) between two things. Surely that's best expressed as an integer? It would make no sense as a Time field, because Time represents a particular instant and not a duration.

  3. #3
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok. but i need to see how much time elapsed between the 2 time stamps. how should i do it? i don't understand the integer thing you mentioned. how will that solve it?

  4. #4
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just mean that timestamp1 - timestamp2 should give you a number, which will be the number of seconds or minutes or hours or days (not sure which) between the two.

    Acutally having tested this, it seems to give you the number of days between the two. So for example I made a table with two columns (f1 and f2) both of which were Data/Time fields. I inserted one row with values 01/01/2002 12:00:00 and 01/01/2002 11:00:00 in f1 and f2 respectively (i.e. one hour different). Then I did the query:

    SELECT (f1 - f2) FROM test_table;

    It gave me the result 0.0416666666642413 which is 1/24. So the result is the number of days between the two timestamps. Obviously you can then do whatever you like with this information.

  5. #5
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i probably should have mentioned that i have the data type on that field set to medium time, no date is being put in there, only time and am/pm.

  6. #6
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The same applies. I changed the table above so the fields are medium time, and therefore they hold the values 12:00 PM and 11:00 AM. Then running the same query gives the result 0.0416666666642413.

    Does this help at all? Basically it does give you the difference between the times, only it gives them as a number instead of a timestamp. But you can very easily manipulate that number however you want it.

  7. #7
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it does make sense now. been doing some reading elsewhere and understand now why it's returning that. just have to figure out how to format the value now. thanks.

  8. #8
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    EUREKA! got it to work!

    SELECT clients.clientName, projectType.projectName, projectTime.projectDate, Format([startTime]-1-[endTime],"Short Time") AS ProjectTime
    FROM projectType INNER JOIN (clients INNER JOIN projectTime ON clients.clientID = projectTime.client) ON projectType.projectTypeID = projectTime.projectType
    ORDER BY clients.clientName;


Bookmarks

Posting Permissions

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