Date Time Article

50 %
50 %
Information about Date Time Article

Published on October 8, 2009

Author: peddi_praveen



Questions about the " Relational" claim from almost all db products...

It’s not just another article on Date-Time, But, RDBMS VIOLATIONS BY So-Called RDBMS Products!!! Praveen Kumar Peddi Objective: The document intends to cover the short comings of existing data storage of date time data type and provides a solution to them in truly relational data model and benefits of the model and most benefited areas of the systems. Current data storage model for Date-time datatype: At Present, Data storage of “date-time” includes both date and time as well in a single column. Almost across all database products, storage and data retrieval of “date-time” data type is implemented as given in the below Table: Product Data Type Description SQL Server Datetime Date and Time Values from January 1, 1753, through December, 31, 9999 accurate to 3 milliseconds SQL Server Smalldatetime Date and Time Values from January 1, 1900 through June 6, 2079, accurate to one minute. Oracle Date Oracle Format that includes not just the Month, day, And year , but also the hour , minute and second. Oracle TimeStamp TimeStamp data type Stores the date to the billionth of a Second. DB2 Date stores a date value (month, day, and year) in Proprietary character data types in internal format and its external format is MM-DD-YYYY or MM/DD/YYYY DB2 Time stores the time value (hour, minute, and second) in Proprietary character data types in internal format and Its external format is HH.MM.SS or HH:MM:SS DB2 TimeStamp Type combines the date and time but stores the time Down to the nanosecond. Its External Format is MM- DD-YYYY-HH.MM.SS.NNNNNN Summary: Put Together, Almost All Products includes [month, day, and year] in their respective Primary “date” data types. ©2009 -9999 SaiPrabhu Solutions. HELP US TO SAVE THE HUMANITY Strictly Private, Proprietary & Confidential

Short-Comings of Current data storage model for Date-time datatype: a) Date-t ime Stores the mult ip le values in single column, (month, day and year at least) , which means date-t ime data type is not atomic b) I t Primari l y vio lates the Codd’s Rule Of Guaranteed Access: What is Codd’s Rule of Guaranteed Access i t? Each and Every Datum (atomic value) in a relat ional database is guaranteed to be logical l y accessible by resort ing to a combination of table name, primary key value, and column name. c) Unless t ime part is expl ic i t l y specif ied, i t wil l only return the results for the exact matching of “date” values. Date t ime does not store day of the week, as part of i t . d) EX: October, 7th 2009, does not specify , whether i t is Sunday or Monday. E) Date t ime data-type does not have the capabil i t y to store and display the data for dates beyond 9999-Dec-31. F) In addit ion, we need to re-cal l the rule of thumb that date t ime column cannot be used a primary key or Unique key. [Those who want to dir t their hands on their own, can move to current model scripts, cut/copy, paste and run!] . [Those who are not techies, but interested to have a look and feel , may see the images for outcome of demonstrat ions]. Scripts: datetime_currentmo DateTime_ShortComi DateTime_ShortComi DateTime_ShortComi del.sql ngs_001.sql ngs_002.sql ngs_004.sql DateTime_ShortComi ngs_005.sql Images: datetime_currenntm datetime_short_comi datetime_short_comi datetime_short_comi datetime_short_comi odel.jpg ngs_001.jpg ngs_002.jpg ngs_004.jpg ngs_005.jpg Page 2 Strictly Private, Proprietary & Confidential HELP US TO SAVE THE HUMANITY October 6, 2009

Yes! Feedback is appreciated. Page 3 Strictly Private, Proprietary & Confidential HELP US TO SAVE THE HUMANITY October 6, 2009

Add a comment

Related pages

Time and Date

Time and Date gives information about the dates and times from your local region to any area in the world. Research times across the globe, review the time ...
Read more

TIME | Current & Breaking News | National & World Updates

Breaking news and analysis from Politics, world news, photos, video, tech reviews, health, science and entertainment news.
Read more

The World Clock — Worldwide - Time and Date

Current time in tables of places in the world. Can be sorted and otherwise customized. Includes meeting planner and other time-related tools.
Read more

Standard Date and Time Format Strings -

A standard date and time format string uses a single format specifier to define the text representation of a date and time value. Any date and time format ...
Read more

How to use dates and times in Excel -

This step-by-step article describes how to use the built-in date and time functions in Microsoft Excel to perform complex date and time calculations.
Read more

Oracle Dates and Times - Stanford University

Oracle Dates and Times Overview; DATE Format; The Current Time; Operations on DATE; Further Information; Overview Oracle supports both date and time, ...
Read more

In, At, On + Time or Date - 5 Minute English

In, At, On + Time or Date. When is your birthday? ... on + date (with the year or without it) or day of the week- On April 2, On March 3, 1999, On ...
Read more

Java Data and Time API in Java 8 - Tutorial - vogella

The Java language provides direct support for time-based objects. While early versions of Java already had support for Date and Time, the Java 8 release ...
Read more

Format the date and time field in Access - Access

When you use the Date/Time data type in Microsoft Access, you can keep the default formats, apply a predefined format, or create a custom format.
Read more

Java SE 8 Date and Time - Oracle

Java SE 8 Date and Time. by Ben Evans and Richard Warburton. Why do we need a new date and time library? A long-standing bugbear of Java developers has ...
Read more