This Article 
 Bibliographic References 
 Add to: 
Efficiently Supporting Temporal Granularities
July/August 2000 (vol. 12 no. 4)
pp. 568-587

AbstractGranularity is an integral feature of temporal data. For instance, a person's age is commonly given to the granularity of years and the time of their next airline flight to the granularity of minutes. A granularity creates a discrete image, in terms of granules, of a (possibly continuous) time-line. We present a formal model for granularity in temporal operations that is integrated with temporal indeterminacy, or “don't know when” information. We also minimally extend the syntax and semantics of SQL-92 to support mixed granularities. This support rests on two operations, scale and cast, that move times between granularities, e.g., from days to months. We demonstrate that our solution is practical by showing how granularities can be specified in a modular fashion, and by outlining a time- and space-efficient implementation. The implementation uses several optimization strategies to mitigate the expense of accommodating multiple granularities.

[1] M. Adiba, N. Bui Quang, and J. Palazzo de Oliveira, “Time Concept in Generalized Data Bases,” Proc. ACM Ann. Conf., pp. 214–223, Oct. 1985.
[2] J.F. Allen, “Maintaining Knowledge about Temporal Intervals,” Comm. ACM, vol. 26, no. 11, pp. 832–843, 1983.
[3] T.L. Anderson, “Modeling Time at the Conceptual Level,” Proc. Int'l Conf. Databases: Improving Usability and Responsiveness, P. Scheuermann, ed., Jerusalem, Israel: Academic Press pp. 273–297, June 1982.
[4] F. Barbic and B. Pernici, “Time Modeling in Office Information Systems,” Proc. ACM SIGMOD Int'l Conf. Management of Data, S. Navathe, ed., pp. 51–62, May 1995.
[5] C. Bettini, C.E. Dyreson, W.S. Evans, R.T. Snodgrass, and X.S. Wang, “A Glossary of Time Granularity Concepts,” Temporal Databases: Research and Practice, O. Etzion, S. Jajodia and S. Sripada, eds., Springer-Verlag, 1998.
[6] R. Chandra, A. Segev, and M. Stonebraker, “Implementing Calendars and Temporal Rules in Next Generation Databases,” Proc. Int'l Conf. Data Eng., pp. 264-273, 1994.
[7] J. Clifford and A. Rao, “A Simple, General Structure for Temporal Domains,” Proc. Conf. Temporal Aspects in Information Systems (AFCET), pp. 23–30, May 1987.
[8] N. Dershowitz and E.M. Reingold, Calendrical Calculations. Cambridge Univ. Press, 1997.
[9] C.E. Dyreson and R. Snodgrass,“Timestamp semantics and representation,” Information Systems, vol. 18, no. 3, pp. 143-166, Sept. 1993.
[10] C.E. Dyreson and R.T. Snodgrass, “Efficient Timestamp Input/Output,” Software–Practice and Experience, vol. 24, no. 1, pp. 80–109, 1994.
[11] C.E. Dyreson and R.T. Snodgrass, “Supporting Valid-time Indeterminacy,” ACM Trans. Database Systems, vol. 23, no. 1, Mar. 1998.
[12] M. Gauthier, “The Avatars of a Package for Calendars in Ada,” Software–Practice and Experience, vol. 25, no. 4, pp. 403–427, Apr. 1995.
[13] I.A. Goralwalla, Y. Leontiev, M.T. Özsu, and D. Szafron, Modeling Time: Back to Basics, Technical Report TR 96-03, Dept. Computer Science, Univ. of Alberta, Feb. 1996.
[14] I.A. Goralwalla, Y. Leontiev, M.T. Özsu, and D. Szafron, “Modeling Temporal Primitives: Back to Basics,” Proc. Int'l Conf. Information and Knowledge Management (CIKM), pp. 24–31, 1997.
[15] C.S. Jensen, C.E. Dyreson, M. Böhlen, J. Clifford, R. Elmasri, S.K. Gadia, F. Grandi, P. Hayes, S. Jajodia, W. Käfer, N. Kline, N. Lorentzos, Y. Mitsopoulos, A. Montanari, D. Nonen, E. Peressi, B. Pernici, J.F. Roddick, N.L. Sarda, M.R. Scalas, A. Segev, R.T. Snodgrass, M.D. Soo, A. Tansel, R. Tiberio, and G. Wiederhold, “A Consensus Glossary of Temporal Database Concepts,” Temporal Databases: Research and Practice, O. Etzion, S. Jajodia, and S. Sripada, eds.,Springer-Verlag, pp. 39, 1998.
[16] W.H. Inmon, Building the Data Warehouse, second ed. John Wiley and Sons, 1996.
[17] N. Kline, J. Li, and R.T. Snodgrass, “Specifying Multiple Calendars, Calendric System, and Field Tables and Functions in TimeADT,” TimeCenterTechnical Report 41, May 1999.
[18] B. Leban, D.D. McDonald, and D.R. Forster, “A Representation for Collections of Temporal Intervals,” Proc. Nat'l Conf. Artificial Intelligence, pp. 360-366, Aug. 1986.
[19] H. Lin, “Efficient Conversion Between Temporal Granularities,” Master's thesis, Dept. Computer Science, Univ. of Arizona,TimeCenterTechnical Report TR-19, June 1997.
[20] N. Lorentzos, “DBMS Support for Nonmetric Measuring Systems,” IEEE Trans. Knowledge and Data Eng., 1992.
[21] J. Melton, ed. Database Language—SQL, ANSI X3.135, 1992.
[22] J. Melton and A.R. Simon, Understanding the New SQL: A Complete Guide, Morgan Kaufmann, San Francisco, 1993.
[23] A. Montanari, E. Maim, E. Ciapessoni, and E. Ratto, “Dealing with Time Granularity in the Event Calculus,” Proc. Int'l Conf. Fifth Generation Computer Systems (ICOT), pp. 702–712, June 1992.
[24] M. Niezette and J. Stevenne, “An Efficient Symbolic Representation of Periodic Time,” Proc. First Int'l Conf. Information and Knowledge Management (CIKM), Nov. 1992.
[25] N. Sarda, “HSQL: A Historical Query Language,” Temporal Databases: Theory, Design, and Implementation. Chap. 5, Benjamin/Cummings, pp. 110–140, 1993.
[26] The TSQL2 Temporal Query Language. R.T. Snodgrass, ed., Kluwer Academic Publishers, 1995.
[27] R.T. Snodgrass, M.H. Böhlen, C.S. Jensen, and A. Steiner, “Adding Valid Time to SQL/Temporal,” Change proposal, ANSI X3H2-96-501r2, ISO/IEC JTC1/SC21/ WG3 DBL MAD-146r2, Nov. 1996.
[28] A. Srivastava and A. Eustace, "ATOM: A System for Building Customized Program Analysis Tools," Proc. ACM SIGPLAN Conf. Programming Language Design and Implementation, ACM Press, New York, 1994.
[29] M.D. Soo, R.T. Snodgrass, C.E. Dyreson, C.S. Jensen, and N. Kline, “Architectural Extensions to Support Multiple Calendars,” TempIS Technical Report 32, Computer Science Dept., Univ. of Arizona, revised May 1992.
[30] P. Terenziani, “Integrating Calendar-Dates and Qualitative Temporal Constraints in the Treatment of Periodic Events,” IEEE Trans. Knowledge and Data Eng., vol. 9, no. 5, pp. 763-783, 1997.
[31] X. Wang, “Algebraic Query Languages on Temporal Databases with Multiple Time Granularities,” Proc. Int'l Conf. Information and Knowledge Management (CIKM), 1995.
[32] X. Wang, S. Jajodia, and V. Subrahmanian, “Temporal Modules: An Approach Toward Temporal Databases,” Information Sciences, vol. 82,no. 1/2, pp. 103–128, Jan. 1995.
[33] G. Wiederhold, S. Jajodia, and W. Litwin, "Dealing with Granularity of Time in Temporal Databases," Proc. Nordic Conf. Advanced Information Systems Eng., R. Anderson et al., eds., pp. 124-140. Springer, 1991,

Index Terms:
Calendar, granularity, indeterminacy, SQL-92, temporal database, TSQL2.
Curtis E. Dyreson, William S. Evans, Hong Lin, Richard T. Snodgrass, "Efficiently Supporting Temporal Granularities," IEEE Transactions on Knowledge and Data Engineering, vol. 12, no. 4, pp. 568-587, July-Aug. 2000, doi:10.1109/69.868908
Usage of this product signifies your acceptance of the Terms of Use.