Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

advanced rounding (time)

Discussion in 'Information Technology' started by realspido, Jul 28, 2006.

  1. realspido

    realspido
    Expand Collapse
    Guest

    Hi,
    Could anyone help me with rounding time?
    I have a table containing in and out time of employees. I'd like to make a
    query which will round it to set interval of time and using median which
    again could be set.
    E.g. setting interval for 15mins and median for 4 mins, we should get:
    7:03 7:00
    7:04 7:15
    7:18 7:15
    7:19 7:30
    Please help me with this issue!!!!

    Thanks in advance.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Bibi lays stone of Advanced Gurmat Studies Centre in Mohali Breaking News Feb 15, 2005
    Controversy Surrounding Nanakshahi Sikh Calender Sikh Sikhi Sikhism Dec 3, 2009
    Controversy Surrounding Prof Darshan Singh Ji Hard Talk Nov 14, 2009

  3. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    realspido wrote:
    > Hi,
    > Could anyone help me with rounding time?
    > I have a table containing in and out time of employees. I'd like to make a
    > query which will round it to set interval of time and using median which
    > again could be set.
    > E.g. setting interval for 15mins and median for 4 mins, we should get:
    > 7:03 7:00
    > 7:04 7:15
    > 7:18 7:15
    > 7:19 7:30
    > Please help me with this issue!!!!
    >
    > Thanks in advance.


    It looks like a grace period for the time clock.

    Subtract (4 - 1) minutes. Then round up to the next 15 minute interval.

    See:

    http://groups.google.com/group/microsoft.public.access/msg/9614be58d0485dd0

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  4. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    James A. Fortune wrote:
    > > Could anyone help me with rounding time?
    > > I have a table containing in and out time of employees. I'd like to make a
    > > query which will round it to set interval of time and using median which
    > > again could be set.
    > > E.g. setting interval for 15mins and median for 4 mins, we should get:
    > > 7:03 7:00
    > > 7:04 7:15
    > > 7:18 7:15
    > > 7:19 7:30

    >
    > It looks like a grace period for the time clock.
    >
    > Subtract (4 - 1) minutes. Then round up to the next 15 minute interval.
    >
    > See:
    >
    > http://groups.google.com/group/microsoft.public.access/msg/9614be58d0485dd0


    A better approach could be for the 'rounding' to be data-driven rather
    than mathematical e.g. to make it easier to 'round' to the next
    enterprise day.

    A standard trick is to have a Calendar table:

    INSERT INTO Calendar (dt) VALUES (#2006-07-21 07:00:00#);
    INSERT INTO Calendar (dt) VALUES (#2006-07-21 07:15:00#);
    INSERT INTO Calendar (dt) VALUES (#2006-07-21 07:30:00#);

    and you do this for every considered time slot for every day in your
    enterprise (e.g. omit weekends, public holidays, etc). Hint: it may be
    easier to construct your Calendar table in a spreadsheet and import it.

    For example, you could have a procedure that takes the time from the
    'clock' as a parameter and inserts the corresponding timeslot from the
    Calendar:

    CREATE TABLE Test (
    employee_nbr CHAR(10) NOT NULL,
    start_date DATETIME NOT NULL
    REFERENCES Calendar (dt)
    );

    CREATE PROCEDURE testproc (
    arg_employee_nbr CHAR(10),
    clock_in_date DATETIME = NOW()
    )
    AS
    INSERT INTO test (employee_nbr, start_date)
    SELECT arg_employee_nbr AS employee_nbr,
    MIN(C1.dt) AS start_date
    FROM Calendar AS C1
    WHERE (clock_in_date - TIMESERIAL(0, 3, 0)) < C1.dt
    GROUP BY arg_employee_nbr;

    The three minutes' grace is hard coded but could be a held in a table
    of 'constants' a or passed as a parameter to the proc for a similar
    data-driven approach.

    Jamie.

    --
     
  5. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Jamie Collins wrote:
    > James A. Fortune wrote:
    >
    >>>Could anyone help me with rounding time?
    >>>I have a table containing in and out time of employees. I'd like to make a
    >>>query which will round it to set interval of time and using median which
    >>>again could be set.
    >>>E.g. setting interval for 15mins and median for 4 mins, we should get:
    >>>7:03 7:00
    >>>7:04 7:15
    >>>7:18 7:15
    >>>7:19 7:30

    >>
    >>It looks like a grace period for the time clock.
    >>
    >>Subtract (4 - 1) minutes. Then round up to the next 15 minute interval.
    >>
    >>See:
    >>
    >>http://groups.google.com/group/microsoft.public.access/msg/9614be58d0485dd0

    >
    >
    > A better approach could be for the 'rounding' to be data-driven rather
    > than mathematical e.g. to make it easier to 'round' to the next
    > enterprise day.
    >
    > A standard trick is to have a Calendar table:
    >
    > INSERT INTO Calendar (dt) VALUES (#2006-07-21 07:00:00#);
    > INSERT INTO Calendar (dt) VALUES (#2006-07-21 07:15:00#);
    > INSERT INTO Calendar (dt) VALUES (#2006-07-21 07:30:00#);
    >
    > and you do this for every considered time slot for every day in your
    > enterprise (e.g. omit weekends, public holidays, etc). Hint: it may be
    > easier to construct your Calendar table in a spreadsheet and import it.
    >
    > For example, you could have a procedure that takes the time from the
    > 'clock' as a parameter and inserts the corresponding timeslot from the
    > Calendar:
    >
    > CREATE TABLE Test (
    > employee_nbr CHAR(10) NOT NULL,
    > start_date DATETIME NOT NULL
    > REFERENCES Calendar (dt)
    > );
    >
    > CREATE PROCEDURE testproc (
    > arg_employee_nbr CHAR(10),
    > clock_in_date DATETIME = NOW()
    > )
    > AS
    > INSERT INTO test (employee_nbr, start_date)
    > SELECT arg_employee_nbr AS employee_nbr,
    > MIN(C1.dt) AS start_date
    > FROM Calendar AS C1
    > WHERE (clock_in_date - TIMESERIAL(0, 3, 0)) < C1.dt
    > GROUP BY arg_employee_nbr;
    >
    > The three minutes' grace is hard coded but could be a held in a table
    > of 'constants' a or passed as a parameter to the proc for a similar
    > data-driven approach.
    >
    > Jamie.
    >
    > --
    >


    I need to give this problem some more thought. As you point out, you
    need to be careful when you're near a date boundary. I realized
    afterward that my proposed solution requires mathematical manipulation
    of a date field and the subsequent reliance on the underlying data type
    for dates. For example, I think Microsoft should have created a
    function for, say, adding a date and time to another date and time to
    help us avoid bad practices. So a lookup table doesn't seem bad in that
    context. But why have a lookup table with all days instead of one for
    one day or one for the intervals in a single hour? Are you concerned
    with the carry for the day?

    I'll see if I can come up with a solution that doesn't require
    mathematical manipulation of dates.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  6. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    James A. Fortune wrote:
    > why have a lookup table with all days instead of one for
    > one day or one for the intervals in a single hour?


    Various reasons. Time is a continuum. Jet only has only one DATETIME
    (equivalent to TIMESTAMP in Standard SQL) rather than separate types
    for 'date' and 'time' elements; even if you did store them separately
    you'd be forever joining them back together, proving that you'd split a
    single attribute into two. I can use DRI (foreign keys) etc to ensure
    the combination of date + time is valid which would be trickier if the
    two elements were in different column in different tables. The data in
    a Calendar table is easy to change e.g. if the timeslots changed next
    month from 15 min slots to 10 min slots you'd just need to change the
    Calendar table going forward without affecting (recent) historical
    data. You could do the same with a table of timeslots with associated
    start date and dates but this would make queries a lot more complicated
    e.g. you would forever be joining this table to you regular Calendar of
    enterprise days - attribute splitting again. You should not be bothered
    by data being stored seemingly redundantly in an auxiliary Calendar
    table if it makes your regular queries easier to write. It is often
    said around here that you should not store something that can be
    calculated but that's not always true: if it is more conducive (e.g.
    queries are easier to define and maintain and may even run faster) to
    store data then it should at least be considered.

    Jamie.

    --
     
  7. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    James A. Fortune wrote:
    > I'll see if I can come up with a solution that doesn't require
    > mathematical manipulation of dates.


    If you are interested in these issues, check out this Joe Celko
    article:

    http://www.dbazine.com/ofinterest/oi-articles/celko37

    In the article he goes one step further by seemingly redundantly adding
    a 'wait_time' column, being the departure time of the previous bus,
    into his bus schedule table simply to eliminate an (entirely
    reasonable, I thought) subquery from his GetNextBus procedure.

    Jamie.

    --
     
  8. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Jamie Collins wrote:
    > James A. Fortune wrote:
    >
    >>why have a lookup table with all days instead of one for
    >>one day or one for the intervals in a single hour?

    >
    >
    > Various reasons. Time is a continuum. Jet only has only one DATETIME
    > (equivalent to TIMESTAMP in Standard SQL) rather than separate types
    > for 'date' and 'time' elements; even if you did store them separately
    > you'd be forever joining them back together, proving that you'd split a
    > single attribute into two. I can use DRI (foreign keys) etc to ensure
    > the combination of date + time is valid which would be trickier if the
    > two elements were in different column in different tables. The data in
    > a Calendar table is easy to change e.g. if the timeslots changed next
    > month from 15 min slots to 10 min slots you'd just need to change the
    > Calendar table going forward without affecting (recent) historical
    > data. You could do the same with a table of timeslots with associated
    > start date and dates but this would make queries a lot more complicated
    > e.g. you would forever be joining this table to you regular Calendar of
    > enterprise days - attribute splitting again. You should not be bothered
    > by data being stored seemingly redundantly in an auxiliary Calendar
    > table if it makes your regular queries easier to write. It is often
    > said around here that you should not store something that can be
    > calculated but that's not always true: if it is more conducive (e.g.
    > queries are easier to define and maintain and may even run faster) to
    > store data then it should at least be considered.
    >
    > Jamie.
    >
    > --
    >


    I just read the article. BTW, thanks for the link. That is certainly
    one way of getting around the mathematical manipulation of the date
    field. I think we need to distinguish time clock input from time clock
    analysis. If the issue is simply getting time clock entries into
    discrete input values then a simple lookup table for even a single hour
    can suffice. For a complicated analysis involving time periods along
    with further calculations, it may be faster to store calculated results
    in a table and look up min/max dates and numbers. I tend to prefer the
    analytic side and can usually come up with a good algorithm for solving
    even complicated problems but reserve the right to adopt techniques
    considered unorthodox. Also, in the past, especially in Access NG's,
    manipulating dates using their representation as Doubles seemed
    reasonable. It does not seem as reasonable to me now. Personally, I
    wouldn't use Joe's technique unless I was solving something so
    complicated that the algorithm took a very long time. I don't dispute
    that Joe's technique is effective. His queries are easier to define and
    maintain but I would rather not maintain an extra table unless it saves
    me a lot of computation time. Maybe we're getting into the realm of
    style and art. I'm still working on the problem. BTW, I appreciate
    hearing your views about this issue.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  9. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    James A. Fortune wrote:
    > in the past, especially in Access NG's,
    > manipulating dates using their representation as Doubles seemed
    > reasonable. It does not seem as reasonable to me now.


    Agreed. I'm getting increasingly bothered by the type of post where a
    newbie is told, "under the covers DATETIME values are stored as double
    float", the implication being that they can use arithmetic operations
    on the values. It may be possible to exploit knowledge of the storage
    for gain (e.g. better run time performance, sub-second values, etc) but
    there are likely to be associated drawbacks (e.g. code is less
    intuitive hence harder to maintain, millisecond accuracy degrades, etc)
    and would appear to be the territory of 'advanced level Access
    enthusiast' rather than 'SQL database newbie'.

    In SQL Server land, DATETIME values are also stored as double float but
    values in SQL are often operated on as text before being cast as
    DATETIME i.e. the nature of the storage is largely ignored.

    I would recommend that people get into the habit of using temporal
    functions for temporal data. The Access temporal functions are not
    standard SQL but they do map well e.g. Jet's DATEPART maps to the
    standard's EXTRACT, NOW() to CURRENT_TIMESTAMP, etc. One issue is that,
    for some strange reason, some of the 'date' functions were split from
    the 'time' functions so e.g. I must user DATESERIAL(year, month, day)
    *plus* TIMESERIAL(hour, minute, second) rather than a more intuitive
    DATESERIAL(year, month, day, [hour], [minute], [second]).

    Temporal data in SQL databases is one of those areas that warrants a
    whole book. Happily, the leading expert has written such a book,
    including discussion of the Access/Jet platform, and, being out of
    print, is available as a free download in PDF format:

    http://www.cs.arizona.edu/~rts/tdbbook.pdf

    Jamie.

    --
     
  10. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Jamie Collins wrote:
    > James A. Fortune wrote:
    >
    >>in the past, especially in Access NG's,
    >>manipulating dates using their representation as Doubles seemed
    >>reasonable. It does not seem as reasonable to me now.

    >
    >
    > Agreed. I'm getting increasingly bothered by the type of post where a
    > newbie is told, "under the covers DATETIME values are stored as double
    > float", the implication being that they can use arithmetic operations
    > on the values. It may be possible to exploit knowledge of the storage
    > for gain (e.g. better run time performance, sub-second values, etc) but
    > there are likely to be associated drawbacks (e.g. code is less
    > intuitive hence harder to maintain, millisecond accuracy degrades, etc)
    > and would appear to be the territory of 'advanced level Access
    > enthusiast' rather than 'SQL database newbie'.
    >
    > In SQL Server land, DATETIME values are also stored as double float but
    > values in SQL are often operated on as text before being cast as
    > DATETIME i.e. the nature of the storage is largely ignored.
    >
    > I would recommend that people get into the habit of using temporal
    > functions for temporal data. The Access temporal functions are not
    > standard SQL but they do map well e.g. Jet's DATEPART maps to the
    > standard's EXTRACT, NOW() to CURRENT_TIMESTAMP, etc. One issue is that,
    > for some strange reason, some of the 'date' functions were split from
    > the 'time' functions so e.g. I must user DATESERIAL(year, month, day)
    > *plus* TIMESERIAL(hour, minute, second) rather than a more intuitive
    > DATESERIAL(year, month, day, [hour], [minute], [second]).
    >
    > Temporal data in SQL databases is one of those areas that warrants a
    > whole book. Happily, the leading expert has written such a book,
    > including discussion of the Access/Jet platform, and, being out of
    > print, is available as a free download in PDF format:
    >
    > http://www.cs.arizona.edu/~rts/tdbbook.pdf
    >
    > Jamie.
    >
    > --
    >


    Jamie,

    Thanks for your comments and for the temporal SQL data link. I will
    read it in its entirety before continuing to search for a solution to
    this problem.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  11. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    James A. Fortune wrote:
    > Thanks for your comments and for the temporal SQL data link. I will
    > read it in its entirety before continuing to search for a solution to
    > this problem.


    Let us know what you think of the book when your done. If you intend to
    read it all there may be a delay in you replying <g>.

    Here's my assessment so far as to why I think it's a worthwhile read.

    Chapter 1 is an interesting introduction to temporal data and SQL-92;
    though I should point out that implementation issues are discussed
    throughout, including the Jet/Access platform. Chapters 2-4 are pretty
    much compulsory reading. Chapter 4 justifies the preference for the
    closed-open representation of time using start and end date pairs:

    "the <i>closed-open</i> representation, in which the second datetime of
    the pair represents the granule immediately following the last granule
    of the period" (4.0, P89, PDF P113).

    However, I prefer the variation made popular by Joe Celko (he doesn't
    claim to have invented it) where the second datetime of the pair models
    the last granule of the period, determined by the smallest granule
    supported by the implementation e.g. one second for Jet/Access.

    Contrast modelling of the current month:

    start_date = #2006-07-01 00:00:00#, end_date = #2006-08-01 00:00:00#
    (Snodgrass)
    start_date = #2006-07-01 00:00:00#, end_date = #2006-07-31 23:59:59#
    (Celko)

    The primary reason AFAIK for the variation is that it makes the dates
    unambiguous and facilitates BETWEEN clauses e.g.

    my_date BETWEEN start_date AND end_date

    The Snodgrass representation would erroneously return the row for the
    current month for the value #2006-08-01 00:00:00#. You can pairs of >=
    and < predicates but the single BETWEEN syntax makes the SQL more human
    readable (more "human programmable" to coin the Celkoism).

    Chapter 5 discusses state ('history') tables with emphasis on effective
    constraints. However, there is a huge lost opportunity in the Access
    implementation section of this chapter: "Neither version of Microsoft
    Access [97 and 2000] supports assertions" (5.8.2 P133 PDF P157). It
    seems to be a little known fact that Jet 4.0, and therefore Access2000
    and upwards, supports table level CHECK constraints and, assuming the
    CHECK is applied to each table used in the CHECK, you can effective
    simulate domain level constraints and get very close to the SQL-92
    ASSERTION syntax. Indeed, the examples used in the chapter can be
    implemented using a CHECK constraint against a single table.

    I'm basically working through chapters 6 and 7, querying and modifying
    state tables respectively. I really need to progress to the following
    three chapters about temporal logging at the row level (I think)
    because I currently support a product that has issues in this area.
    Chapter 10 looks particularly challenging...

    Jamie.

    --
     

Share This Page