advanced rounding (time)

[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.

 

[James A. Fortune]

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
(e-mail address removed)

 

[Jamie Collins]

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.

--

 

[James A. Fortune]

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
(e-mail address removed)

 

[Jamie Collins]

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.

--

 

[Jamie Collins]

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.

--

 

[James A. Fortune]

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
(e-mail address removed)

 

[Jamie Collins]

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.

--

 

[James A. Fortune]

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
(e-mail address removed)

 

[Jamie Collins]

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.

--

 

[CDMAPoster]

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>.

I'm done reading the entire book.

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).

I like the notation for the closed/open representation. It's the same
notation used in all the calculus courses I took (about nine). I don't
let people with the initials J.C. plug Celko too hard though .

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.

I'm just now getting to problems that benefit from the sophistication
required by the proposed techniques. In calculating actual profit for
a particular manufacturing job, I have used average pay levels to
determine the hourly rate applied to the job by a particular employee.
To get greater accuracy, it will soon be necessary to have a table that
includes a new record each time someone's hourly rate changes. I
suppose I'll get to try out the techniques in the book versus rolling
my own SQL. In order to give those techniques a fair shot I should put
some data in SQL Server Express and use the SQL:1999 (SLQ3) versions of
the SQL since I don't like the prospect of writing 20 to 30 lines of
SQL code for a single query. Nearly all of the queries using the older
standard are challenging. How much of the new standard is supported in
SQL Server Express with, say, Access 2007? It looks like the new SQL
constructs are extremely flexible and powerful. I just haven't had to
solve problems of that level of complexity before.

Thanks again,

James A. Fortune
(e-mail address removed)

Note: I had to use Google to respond to this message because it didn't
show up in microsoft.public.access in my reader. I'm not sure why it
didn't show up. That's why I'm signed in to Google as CDMAPoster.

 

[Jamie Collins]

I'm done reading the entire book.

I like the notation for the closed/open representation. It's the same
notation used in all the calculus courses I took (about nine).

I think I got it wrong in my last post: my preferred representation
where the second datetime of the pair models the last granule of the
period is the 'closed-closed' representation. I don't get the author's
argument that having to use '<=' instead of '<' is annoying nor is
having to use the occasional DATEADD('s', 1, end_date), and the author
admits that the 'closed-open' prevents the use of BETWEEN constructs. I

I'm just now getting to problems that benefit from the sophistication
required by the proposed techniques. In calculating actual profit for
a particular manufacturing job, I have used average pay levels to
determine the hourly rate applied to the job by a particular employee.
To get greater accuracy, it will soon be necessary to have a table that
includes a new record each time someone's hourly rate changes. I
suppose I'll get to try out the techniques in the book versus rolling
my own SQL. In order to give those techniques a fair shot I should put
some data in SQL Server Express

Much of the ASSERTION syntax (chapter 5) can be modified to use in Jet
4.0 CHECK constraints to create current/sequenced/nonsequenced
constraints and primary/foreign keys. The problem I ran into was the
syntax was written to be, in standard SQL terms, DERRABLE INITIALLY
DEFERRED (5.7 P132, PDF P156). Of course, Jet does not have this
syntax.

I reworked some of the examples to make them more accommodating of
Jet's limitation, without jeopardising date integrity, in the
restricted case (current modifications only). However, sometimes in the
restricted case and always(?) in the general case (modifications can
additionally be to past/future periods), without the ability to defer,
the constraints must be dropped while the data is being modified then
recreated, all within a transaction. I've no real life experience of
mixing DDL and DML in transaction in Jet in this way so I've no idea
whether this is practical.

Note that you may not fare much better in SQL Server land, where AFAIK
constraints aren't deferrable either. ASSERTIONs would need to be
implemented as triggers; however, at least a trigger can perform
several actions. In the restricted case, where an update involves two
SQL UPDATES and an INSERT and a deletion is implemented by an INSERT,
two UPDATEs and a DELETE, Jet simply cannot perform the necessary
actions in a single SQL procedure.

My conclusions is that I would only try to implement the restricted
case in Jet and view the general case as a DBS job with help procedures
provided (to be called in a prescribed order).

I still haven't read chapter 10 (bitemporal tables)!

I hope this discussion encourages others to read the first few chapters
of this (free download) book, even if it is to get the message that
temporal data is quite complex but that taking the time to write
effective constraints (using Jet 4.0 CHECK constraints) can make
queries easier and more robust.

Jamie.

--