Click here to monitor SSC
  • Av rating:
  • Total votes: 39
  • Total comments: 44
Joe Celko

Temporal Datatypes in SQL Server

16 December 2008

In the first of a series of articles on the tricks of tackling temporal data in SQL, Joe Celko discusses SQL's temporal data types and agonizes over the fact that, although there are ANSI/ISO Standards for temporal operations in SQL, every vendor has something different. He explains the mysteries of such things as time-zones, lawful time, UTC, CUT, GMT, CE, DST, and EST.

Of the major programming languages, SQL is the only one with built-in temporal data types. In fact, I cannot think of anything else other than spreadsheet languages which have temporal data types! Most SQL Products had some kind of temporal data types before the first official versions in the SQL-92 Standards were defined. Considering how important dates are for commercial work, you would have thought that COBOL would have something by now. We even had the "Y2K Crisis" at the start of this decade, with tourist groups of books and articles about the problems of keeping only two-digit years in dates in computer data. I was one of the first people to write about it back in 1981 (INFORMATION SYSTEMS NEWS, Feb 09, "Father Time Software Secrets Allows Updating of Dates") and even got quoted in VANITY FAIR.

The Common Era

The Common Era calendar is irregular and the only standard unit of time is the second; years, months, weeks, hours, minutes and so forth are not part of the metric system, but are mentioned in the ISO standards as conventions.

Note:  the old abbreviations A.D. (Anno Domini - Latin for "in the year of Our Lord") and B.C. ("Before Christ") have been replaced by CE for "Common Era" and BCE for "Before Common Era" in ISO Standard to avoid religious references. Strangely, the Church of Jesus Christ of Latter-day Saints ("Mormons") are the only religious group I know who use CE and BCE in their literature.

Today, you want to set up a program to convert your data to conform to ISO-8601 "Data Elements and Interchange Formats - Information Interchange - Representation of Dates and Times" as a corporate standard and EDIFACT for EDI messages. This is the "yyyy-mm-dd" format that is part of Standard SQL and will become part of other standard programming languages as they add temporal data types.

The full ISO-8601 timestamp can be either a local time or UTC time. UTC is the code for "Universal Coordinated Time," which replaced GMT (which has not been a Standard for decades), which was the abbreviation for "Greenwich Mean Time" (if you listen to CNN, you are used to hearing the term UTC, but if you listen to BBC radio, you are used to the term GMT).

In 1970 the Coordinated Universal Time system was devised by an international advisory group of technical experts within the International Telecommunication Union (ITU). The ITU felt it was best to designate a single abbreviation for use in all languages in order to minimize confusion. The two alternative original abbreviation proposals for the "Universal Coordinated Time" were CUT (English: Coordinated Universal Time) and TUC (French: temps universel Coordinne). UTC was selected both as a compromise between the French and English proposals and because the C at the end looks more like an index in UT0, UT1, UT2 and a mathematical-style notation is always the most international approach.

Technically, Universal Coordinated Time is not quite the same thing as the old Greenwich Mean Time. GMT is a 24-hour astronomical time system based on the local time at Greenwich, England. GMT can be considered equivalent to Universal Coordinated Time when fractions of a second are not important. However, by international agreement, the term UTC is recommended for all general time keeping applications and use of the term GMT is discouraged.

A date without a time
zone is ambiguous in
a distributed system.
A transaction created
1995-12-17 in London
may be younger than a
transaction created
1995-12-16 in Boston

.Another problem is that besides having time zones, we also have "lawful time" to worry about. This is the technical term for time required by law for commerce. Usually, this means whether or not you use daylight saving time. On 2007-12-09 President Hugo Chavez of Venezuela decided to create his own unique time zone, putting the clock back half-an-hour on a permanent basis. This unnecessary and done simply to put his country in a different time zone from the United States. Similar political crap has been done before, but it usually involves sets Daylight Saving Time (DST) changes in a year.

The need for UTC time in the database and lawful time for display and input has not been generally handled yet. EDI and replicated databases must use UTC time to compare timestamps. A date without a time zone is ambiguous in a distributed system. A transaction created 1995-12-17 in London may be younger than a transaction created 1995-12-16 in Boston.

Standard SQL Temporal Data Types

Standard SQL has a very complete description of its temporal data types. There are rules for converting from numeric and character strings into these data types, and there is a schema table for global time-zone information that is used to make sure that temporal data types are synchronized. It is very complete and elaborate, so SQL Server has not implemented it yet, while DB2 is on the mark and Oracle has its own weirdnesses. As an international standard, Standard SQL has to handle time for the whole world and most of us work with only local time.

The common terms and conventions related to time are also confusing. We talk about "an hour" and use the term to mean a particular point within the cycle of a day ("The train arrives at 13:00 Hrs") or to mean an interval of time not connected to another unit of measurement ("The train takes three hours to get there"); the number of days in a month is not uniform; the number of days in a year is not uniform; weeks are not related to months; and so on.

We need to have a DATE data type for commercial work. Having TIME and a TIMESTAMP data types is very handy, too. Standard SQL has a set of date and time (DATE, TIME and TIMESTAMP) and INTERVALs (DAY, HOUR, MINUTE and SECOND with decimal fraction) data types. Both of these groups are temporal data types, but datetimes represent points in the time line, while the interval data types are durations of time. Standard SQL also has a full set of operators for these data types.

Before SQL Server 2008, handling these things was awful. SQL Server was based on the first UNIX system clock, where a floating point number kept "date stuff" in the front of the number and the "clock stuff" in the back. All of this was based on counting physical hardware clock ticks in one machine. Today, you have wristwatches and alarm clocks that adjust themselves with the NIST radio time signal. As a general statement, there are two ways of representing temporal data internally. The "Unix representation" is based on keeping a single long integer or a word of 64 or more bits that counts the computer clock ticks from a base starting date and time. The other representation I will call the "COBOL method", since it uses separate fields for the year, month, day, hours, minutes and seconds.

The Unix method is very good for calculations, but the engine must convert from the external formats and the internal format and vice versa. The COBOL format is the opposite; good for display purposes, but weaker on calculations.

For example, to reduce a TIMESTAMP to just a date with the clock set to 00:00 Hrs in SQL Server, you can take advantage of their internal representation and write:

CAST (FLOOR (CAST (mydate AS FLOAT)) AS datetime)

Likewise, the following day can be found with this expression:

CAST (CEILING (CAST (mydate AS FLOAT)) AS datetime)

Display and Internal Date Formats

The ISO ordinal date formats are described in ISO-2711-1973. Their format is a four-digit year, followed by a digit day within the year (001-366). The year can be truncated to the year within the century. The ANSI date formats are described in ANSI X3.30-1971. Their formats include the ISO standard, but add a four-digit year, followed by the two-digit month (01-12), followed by the two-digit day within month (01-31). This option is called the calendar date format. Standard SQL only uses the "yyyy-mm-dd" format, period. The ordinal format is better for date arithmetic; the calendar format is better for display purposes. We will get back to that when we discuss the Calendar table.

Many programs still use a year-in-century date format of some kind. This was supposed to save space in the old days when that sort of thing mattered (i.e., when punch cards had only 80 columns). Programmers assumed that they would not need to tell the difference between the years 1900 and 2000 because they were too far apart. Old COBOL programs that did date arithmetic on these formats returned erroneous negative results. If COBOL had a DATE data type, instead of making the programmers write their own routines, this would not have happened. Relational database users and 4GL programmers can gloat over this, since they have DATE data types built into their products.

Handling Times

Most small databases live and work in one time zone. If you have a database that covers more than one time zone, you might consider storing time in UTC and adding a numeric column to hold the local time-zone offset. The time zones start at UTC, which has an offset of zero. This is how the system-level time-zone table in Standard SQL is defined. There are also ISO standard three-letter codes for the time zones of the world, such as EST, for Eastern Standard Time, in the United States. The offset is usually a positive or negative number of hours, but there are some odd zones that differed by 15 minutes from the expected pattern, which were removed in 1998.

Now you have to factor in Daylight Saving Time on top of that to get what is call "lawful time" which it is the basis for legal agreements. The US government uses DST on federal lands located inside of states that do not use DST. If the hardware clock in the computer in which the database resides is the source of the timestamps, you can get a mix of gaps and duplicate times over a year. This is why Standard SQL uses UTC internally.

You should use a "24-hour" time format, which is less prone to errors than 12-hour (AM/PM) time, since it is less likely to be misread or miswritten. This format can be manually sorted more easily and is less prone to computational errors. Americans use a colon as a field separator between hours, minutes and seconds; some Europeans use a period (this is not a problem for them, since they also use a comma for a decimal point).

One of the major problems with time is that there are three kinds:

  • fixed events ("He arrives at 13:00 Hrs"),
  • durations ("The trip takes three hours")
  • Intervals ("The train leaves at 10:00 Hrs and arrives at 13:00 Hrs")-

....which are all interrelated.

Standard SQL introduces an INTERVAL data type that does not explicitly exist in most current implementations (Rdb, from DEC And now Oracle Corporation, is an exception). An INTERVAL is a unit of duration of time rather than a fixed point in time-days, hours, minutes, and seconds (FIPS-127 requires five decimal places, much beyond SQL Server's original ability).

There are two classes of intervals. One class, called year-month intervals, has an express or implied precision that includes no fields other than YEAR and MONTH, though it is not necessary to use both. The other class, called day-time intervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH -- that is, DAY, HOUR, MINUTE and SECOND (with decimal places).

Where to Learn About Temporal SQL

Next Installment: Calendar Table tricks

Joe Celko

Author profile:

Joe Celko is one of the most widely read of all writers about SQL, and was the winner of the DBMS Magazine Reader's Choice Award four consecutive years. He is an independent consultant living in Austin, TX. He has taught SQL in the US, UK, the Nordic countries, South America and Africa.
He served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards.
He has written over 800 columns in the computer trade and academic press, mostly dealing with data and databases. He is the author of eight books on SQL for Morgan-Kaufmann, including the best selling SQL FOR SMARTIES.
Joe is a well-known figure on Newsgroups and Forums, and he is famous for his his dry wit. He is also interested in Science Fiction.

Search for other articles by Joe Celko

Rate this article:   Avg rating: from a total of 39 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Nice try
Posted by: Anonymous (not signed in)
Posted on: Friday, December 19, 2008 at 9:39 AM
Message: Joe,

I'm quite suprised to read that datetime values are stored as floats internally.

For a long time I have been under the impression that "Values with the datetime data type are stored internally by the SQL Server xxxx Database Engine as two 4-byte integers". I guess someone should mention to Microsoft that their SQL Server Books Online are giving wrong information. Or could it be that you missed out this brilliant and freely available source of information?

Furthermore your FLOOR(), CEILING() thingy does not work 100% accurate all the time.

SET NOCOUNT ON;
DECLARE @MyDate datetime;
SELECT @MyDate = '2008-12-19 00:00:00.000';

SELECT
CAST (FLOOR (CAST (@MyDate AS FLOAT)) AS datetime);
SELECT
CAST (CEILING (CAST (@MyDate AS FLOAT)) AS datetime);

results in

2008-12-19 00:00:00.000

2008-12-19 00:00:00.000

According to your claim it should return 2008-12-20 00:00:00.000 in the second case, right?

However, when you apply integer arithmetics as you probably should you get correct results:

SET NOCOUNT ON;
DECLARE @MyDate datetime;
SELECT @MyDate = '2008-12-19 00:00:00.000';

SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, @MyDate), 0);

SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, @MyDate), 0) + 1;

2008-12-19 00:00:00.000

2008-12-20 00:00:00.000


Subject: Conversion to float to find a day??
Posted by: Adam Machanic (view profile)
Posted on: Friday, December 19, 2008 at 11:09 AM
Message: Joe, I'm a bit disappointed in you relying on an internal representation to show date truncation. We can do it just as easily -- and without relying on undocumented internals -- using the documented date/time functions:

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

Subject: previous comments.
Posted by: WBrewer (view profile)
Posted on: Monday, December 22, 2008 at 8:45 AM
Message: I'm struggling slightly with these first two comments. Joe is correct about the internal representation of the DateTime when he says 'The "Unix representation" is based on keeping a single long integer or a word of 64 or more bits that counts the computer clock ticks from a base starting date and time.' To make it possible to do arithmetic on numbers, This is then returned as one four-byte integer for the number of days before or since January 1 1900, and a four-byte integer for the number of milliseconds since midnight on the fractional side of the number. Because of this, you can do date arithmetic directly without converting between formats. Joe's example illustrates the point.

The internals are documented, and there is so much code around that uses diect arithmetic that it would be difficult to change now.

Funnily enough, I find the process of using DateDiff to add zero days to a date, as a trick to scuff off the time part, as unintuitive as any other method that requires a knowledge of the underlying data types. At the moment I can't even find the DATEDIFF(dd, 0, GETDATE()) trick in Books on Line. It is all over the forums, but is it really the documented way of doing it?

Well done Joe. A great, thought-provoking article.

Subject: SQL Server MVP Tibor Karaszi has also written about it
Posted by: Alex_Kuznetsov (view profile)
Posted on: Monday, December 22, 2008 at 3:09 PM
Message: SQL Server MVP Tibor Karaszi has also written an article about temporal types in SQL Server:

http://www.karaszi.com/SQLServer/info_datetime.asp

Subject: Documented??
Posted by: Adam Machanic (view profile)
Posted on: Tuesday, December 23, 2008 at 9:58 AM
Message: WBrewer:

"The internals are documented"

Where, exactly? I see no reference to the internal storage format anywhere in the Books Online topic for DATETIME. Do you?

http://msdn.microsoft.com/en-us/library/ms187819.aspx


"At the moment I can't even find the DATEDIFF(dd, 0, GETDATE()) trick in Books on Line."

And where in the documentation are you seeing the method of relying on the internal format? Using the date functions is the correct way to do this kind of work; relying on the internal format is dangerous at best.

Subject: Re:Nice TRY
Posted by: PK (not signed in)
Posted on: Tuesday, December 23, 2008 at 10:23 AM
Message: Following will work as per the article. i.e.. our friend in the post Above Nice Try had used the exact start point of the day which has lead to his conclusion of FLOOR and CEILING will not work but it actually works but he has helped us getting an issue with using FLOOR and CEILING, i.e..it could potentially mislead us when we use FLOOR and CEILING at the exact start point of the day which could be potentially an issue and a very rare to see.

SET NOCOUNT ON;
DECLARE @MyDate datetime;
SELECT @MyDate = '2008-12-19 00:00:01.000';

SELECT
CAST (FLOOR (CAST (@MyDate AS FLOAT)) AS datetime);
SELECT
CAST (CEILING (CAST (@MyDate AS FLOAT)) AS datetime);

Subject: Re: Documented??
Posted by: WBrewer (view profile)
Posted on: Tuesday, December 23, 2008 at 10:50 AM
Message: to answer your questions
1/ All over the place. e.g. http://msdn.microsoft.com/en-us/library/aa258277(SQL.80).aspx says 'Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.'

2/ I didn't even realize that it had to be on that page.

3/ Neither I nor Joe are saying that direct arithmetic is the best approach. I'm just saying that DATEDIFF(dd, 0, GETDATE()) is no better than direct arithmetic simply because it relies on an undocumented quirk of the DateDiff function. Are you really suggesting that 0 is a valid date?

Subject: Hmm...
Posted by: Adam Machanic (view profile)
Posted on: Tuesday, December 23, 2008 at 11:13 AM
Message: 1) "All over the place?" Well, you found one page, and I found a similar one in the SQL Server 2005 documentation. But I can't find any reference in the SQL Server 2008 documentation. Can you? Is it still safe to use going forward? Is there a reason it has been removed from the documentation? It certainly doesn't give me a warm and fuzzy feeling about.

3) If you don't like 0, use some other date. You can plug any date you want in where the 0 is. See the chapter from my book on the topic; most of it is available on Google Books, here:

http://books.google.com/books?id=157RTel4eaQC&pg=PA315&lpg=PA315

Subject: using FLOOR and CEILING for date truncation is unreliable
Posted by: Alex_Kuznetsov (view profile)
Posted on: Tuesday, December 23, 2008 at 1:30 PM
Message: As such, it should never be used in production code. Let me cut and paste the right way from Tibor Karaszi's article:

SELECT DATEADD(day, DATEDIFF(day, '20040101', CURRENT_TIMESTAMP), '20040101')

http://www.karaszi.com/SQLServer/info_datetime.asp

Subject: BASICs of time
Posted by: Hercules Gunter (view profile)
Posted on: Tuesday, December 23, 2008 at 4:19 PM
Message: UTC is the abbreviation for Universal Co-ordinated Time? I actually thought it was an American attempt to get references to Britain out of things. I still use GMT, since it has the virtue of long use, and stipulates the base datum: the meridian on which our system of time is based (just as most people still use Centigrade rather than Celsius, since the name is almost self-defining, embodying the base datum of the scale's range).

Languages other than SQL don't have a temporal data type? Microsoft's Visual Basic has had a Date type since version 1 (precise to a second), Javascript has a Date type precise to a millisecond, .NET provides a Date type for all languages (let's disregard the silliness of recording dates with a base of 1st Jan, 0001, which is obviously wrong for dates before the adoption of the Gregorian calendar).

I was part of a team which in the late 70s who had the arrogant conviction that their software would still be in use at the turn of the century, and accordingly adopted 4-digit years in representing their dates (using COBOL, as it happens). I have never, since that time, used anything but 4-digit years in date representation, and date/time data types when I had the option. The Y2K fuss astounded me, since I assumed everyone would have adopted a longer view by the 90s. But in 1998 I encountered a system fresh off the presses which used two-digit dates, which dumbfounded me.

Subject: Correction
Posted by: Hercules Gunter (view profile)
Posted on: Tuesday, December 23, 2008 at 4:22 PM
Message: I added a note in the wrong place. Javascript's time is, I think, accurate only to a second. It's .NET which discriminates milliseconds.

Subject: javascript
Posted by: Phil Factor (view profile)
Posted on: Wednesday, December 24, 2008 at 4:55 AM
Message: Joe's right about Javascript. there is no fundamental date type in Javascript. Dates and times are supported by a type (class) of object that represents dates and times. Proper support for four-digit years only came with the ECMA-262 standard!
(see David Flanagan's excellent book)
The granularity of the date/time is theoretically to the millisecond but, in reality, it depends on the implementation.




Subject: COBOL versus SQL
Posted by: Roger Sinclair-Clarke (not signed in)
Posted on: Monday, December 29, 2008 at 5:30 AM
Message: to me Ted Codd is a genius and has been since I discovered him in 1985.
and here we are 23 years later ... with so called experts arguing about how to store DateTime values ... truly astonishing and a perfect example of why theory is everything and SQL is comprehensive rubbish

Subject: DateTime Datatype
Posted by: qlClarke (view profile)
Posted on: Monday, December 29, 2008 at 10:02 AM
Message: Much as I have appreciated Joe's work over the years and his ability to separate the wheat from the chaff ... I am afraid that Joe is completely missing the boat here.
There is only one way to regard DateTime ... it is the unique point in time when something happened.
And as such it does not exist.
In the same way that 1 divided by 3 does not exist.
And is the reason why Midnight and Midday do not exist and thus why time is so completely miss-understood in the world at large.
And this is why it has to be represented by a floating point number which encompasses all the technology for dealing with approximations to reality.
All you need now are functions (better still a methods) to enable integer arithmetic on Dates.

Do I think that this will ever happen ?
... I leave that as an exercise for you dear reader

Subject: Already there.
Posted by: Jeff Moden (view profile)
Posted on: Wednesday, December 31, 2008 at 10:40 AM
Message: >>All you need now are functions (better still a methods) to enable integer arithmetic on Dates.

Ummm... what would you call DATEADD, +, and -?

Subject: Already there.
Posted by: Jeff Moden (view profile)
Posted on: Wednesday, December 31, 2008 at 3:02 PM
Message: >>All you need now are functions (better still a methods) to enable integer arithmetic on Dates.

Ummm... what would you call DATEADD, +, and -?

Subject: Date Methods
Posted by: Roger Sinclair-Clarke (not signed in)
Posted on: Wednesday, December 31, 2008 at 3:08 PM
Message: Well we could start with Age()

Subject: Age()
Posted by: Jeff Moden (view profile)
Posted on: Wednesday, December 31, 2008 at 3:28 PM
Message: Age() ... what would be nice is a DateDiff that really worked correctly.

Subject: Date Methods
Posted by: Roger Sinclair-Clarke (not signed in)
Posted on: Thursday, January 01, 2009 at 8:35 AM
Message: I obviously don't mean to imply that Age() cannot be calculated correctly by existing T-SQL. However ... it is pretty obvious that a lot of Newbies are in fact calculating it incorrectly (eg dividing by 365.25 etc etc).
That is what is scary ... and is but one reason why Age() is needed desperately ... along with a raft of interval related functions which accept DateTime and instantly convert it to Floor(Float()) (ie DateTimeInteger) before proceeding

Subject: Valid operators involving datetimes and intervals
Posted by: Phil Factor (view profile)
Posted on: Friday, January 02, 2009 at 9:26 AM
Message: The ISO/IEC 9075 SQL2008 standard allows Arithmetic operations involving comparable values of type DATETIME or INTERVAL. these should obey the natural rules associated with dates and times and yield valid DATETIME or INTERVAL results according to the Gregorian calendar. If you are working with Intervals, even * or / are perfectly valid. If you subtract a DATETIME, from a DATETIME, a valid operation in SQL, you will get an interval, even in SQL Server which doesn't actually have an INTERVAL type! I haven't checked SQL 2003 recently, but I believe that the same is specified there.

It seems unkind to blame Joe for this.

Subject: ??
Posted by: Adam Machanic (view profile)
Posted on: Friday, January 02, 2009 at 12:37 PM
Message: Who is blaming Joe for arithmetic operators on DATETIME instances? The issue I had was that he was applying arithmetic operators to FLOATs, then converting those over to DATETIME. That's not at all the same thing as overloads on the native type...

Subject: So is this OK?
Posted by: Phil Factor (view profile)
Posted on: Saturday, January 03, 2009 at 4:37 AM
Message: Select convert(datetime,'30 mar 1990 00:00:00')+1.5

Subject: Sure, why not?
Posted by: Adam Machanic (view profile)
Posted on: Saturday, January 03, 2009 at 2:15 PM
Message: Yes.

+ is a documented overload for the DATETIME datatype, with well-defined behavior.

Subject: Ted Codd?
Posted by: MIke Gorman (not signed in)
Posted on: Saturday, January 03, 2009 at 9:00 PM
Message: The majority of the DateTime work in SQL standards was done by Jim Melton. Trust me, he never worked for IBM. He's out of DEC and then Sybase and now Oracle. All this work was done way after Ted Codd left the scene.

As to relational theory being "discovered" by Ted Codd, almost 100% of the relational operations were in production class DBMSs 3 years BEFORE Ted Codd "discovered" them. Only IBM can get away something like that...

Regards,
Mike Gorman
Secretary ANSI INCITS H2 Technical Committee on Database Languages

Subject: Ted Codd
Posted by: Roger Sinclair-Clarke (not signed in)
Posted on: Sunday, January 04, 2009 at 6:50 AM
Message: >>As to relational theory being "discovered" by Ted Codd, almost 100% of the relational operations were in production class DBMSs 3 years BEFORE Ted Codd "discovered" them. Only IBM can get away something like that..
>>
And which production class DBMS's would that be ?

And Chris Date or Fabian Pascal would agree with that statement or are they IBM toadies too?

Subject: No one said Codd "discovered" anything...
Posted by: Anonymous (not signed in)
Posted on: Sunday, January 04, 2009 at 7:01 AM
Message: > As to relational theory being "discovered" by
> Ted Codd, almost 100% of the relational
> operations were in production class DBMSs

Saying that Codd discovered relational theory is as fraught with issues as saying that Columbus "discovered" the new world. But what we can say is that Codd's paper made a solid connection between FOL and data management.

Whether or not the operations were possible in other DBMSs before that time, no one else had published a paper solidly connecting the two--and that is the basis for relational theory. Furthermore, these operations, while at the core, are not the entire picture, and "production class" DBMS then or now can be said to truly conform to the relational model. Especially not any that conform to the ANSI Standard query language. So I'm really not sure what your point is, Mike.

Subject: Typo in the previous comment...
Posted by: Anonymous (not signed in)
Posted on: Sunday, January 04, 2009 at 7:02 AM
Message: That should have read "no 'production class' DBMS then or now can be said to truly conform to the relational model."

Subject: Ted Codd
Posted by: Roger Sinclair-Clarke (not signed in)
Posted on: Sunday, January 04, 2009 at 2:33 PM
Message: >>As to relational theory being "discovered" by Ted Codd, almost 100% of the relational operations were in production class DBMSs 3 years BEFORE Ted Codd "discovered" them. Only IBM can get away something like that..
>>
And which production class DBMS's would that be ?

And Chris Date or Fabian Pascal would agree with that statement or are they IBM toadies too?

Subject: Ted Codd's conrtribution
Posted by: Phil Factor (view profile)
Posted on: Sunday, January 04, 2009 at 4:24 PM
Message: The fundamental principles of Relational data as an information system was being used way back to the Napoleonic era, probably before then (I'm looking!). They were used in computerized databases before Ted Codd's publications. what he did, as I understand it, was to provide a theoretical framework for existing practice and a formal groundwork for the subsequent RDBMSs.

Subject: Which Production Class DBMSs?
Posted by: Mike Gorman (not signed in)
Posted on: Sunday, January 04, 2009 at 9:06 PM
Message: Ramis by Mathematica. They were out of Princeton, NJ. Knowing the authors, I sort of expect that there was much thought and a very elegant correct framework that went into all the set operations supported by Ramis.

Given that RAMIS existed in the late 1960s, I can only presume that design, papers, concepts of operations, syntax creation, programming, testing, et al preceeded that by several years. It's my understanding that almost all of RAMIS was written in Fortran.

Regards,
Mike Gorman

Subject: Who's the real author of the first draft of ANSI SQL?
Posted by: Mike Gorman (not signed in)
Posted on: Sunday, January 04, 2009 at 9:08 PM
Message: Phil Shaw. Not Ted nor Chris.

Regards,
Mike Gorman

Subject: Nice try but no ceegar
Posted by: jarhed (view profile)
Posted on: Monday, January 05, 2009 at 11:04 AM
Message: Everyone argues about this subject, and many people think they have the answers, including me. Here are a couple of date handling rules that I find useful:

1. Any date data must be stored in the appropriate datatype--no exceptions.

2. Mathematical calculations on dates are verboten. I.e., no casts to float. Date is a datatype implemented by the language, with functionality opaque to the programmer, *despite* the fact that you might know it is really a float or some such. Always use the date calc functions provided by the language to manipulate dates.

3. There is no such thing as a date without a time or a time without a date. I note that SQL Server 2008 provides a time datatype, and I think this is a big mistake. Exceptions to this rule lead to an entire class of entirely avoidable bugs.

Have a great day!

Subject: Re: Nice try but no ceegar
Posted by: Robyn Page (view profile)
Posted on: Tuesday, January 06, 2009 at 6:36 AM
Message: Arithmetic calculations involving dates are not verboten in SQL. One date may be subtracted from another to get an interval. Intervals may be expressed as numbers with a decimal point.

Subject: Date Methods
Posted by: Roger Sinclair-Clarke (not signed in)
Posted on: Tuesday, January 06, 2009 at 7:42 AM
Message: Ted Codd may have pre-dated the Object Oriented revolution in software ... but my take on what he was trying to demonstrate with his 12 rules was very much in the O-O spirit.
And the mess that SQL has got itself into is precisely because it is not O-O in any shape or form.
Relational Databases and SQL are not one and the same (c.f Quell)

Subject: Re: Nice try but no ceegar
Posted by: Derek Dongray (not signed in)
Posted on: Tuesday, January 06, 2009 at 10:55 AM
Message: The DATE type in SQL server 2008 is simply a DATETIME with a precision of 1 day. Since comparing dates for equality (disregarding times) it makes sense to have a special data type for this this avoid continually having to check that the time is being ignored. Having a TIME data type is harder to justify since uses for it are much more limited. Perhaps it was meant to be an INTERVAL type?

Subject: Convert from UTC (web log) to Local time
Posted by: Amadeus C (not signed in)
Posted on: Tuesday, January 06, 2009 at 1:37 PM
Message: Is there an open-source lookup table to convert from UTC to local time? Has anyone translated Olson timezone files to a SQL table(s)?

For example I need to load a web-log from last year (stored as UTC, no timezone, no Daylight Savings) to local time. The difference between GetDate() and GetUTCDate() is ONLY valid for the current time.


Subject: Intervals as a single number?
Posted by: Adam Machanic (view profile)
Posted on: Tuesday, January 06, 2009 at 1:37 PM
Message: Robyn: An "interval" is a period of time. A period, by definition, has a start and an end. How can you possibly describe this using a single number with or without a decimal point?

Subject: Convert from UTC (web log) to Local time
Posted by: Amadeus C (not signed in)
Posted on: Tuesday, January 06, 2009 at 4:35 PM
Message: Is there an open-source lookup table to convert from UTC to local time? Has anyone translated Olson timezone files to a SQL table(s)?

For example I need to load a web-log from last year (stored as UTC, no timezone, no Daylight Savings) to local time. The difference between GetDate() and GetUTCDate() is ONLY valid for the current time.


Subject: Re: Intervals as a single number?
Posted by: Robyn Page (view profile)
Posted on: Wednesday, January 07, 2009 at 3:11 AM
Message: I'm puzzled. Which SQL Standard are you referring to here? Surely, an INTERVAL can be cast as an exact numeric where it is in the correct syntax?

Subject: SQL Intervals
Posted by: Roger Sinclair-Clarke (not signed in)
Posted on: Wednesday, January 07, 2009 at 8:30 AM
Message: Well ... we are starting to get to the heart of the matter.
An Interval is always a calculated field.
It represents the period of time between to DateTime Events.
As such its precision can be defined (Mathematically),in ClockTicks,Seconds,Minutes,Hours and finally ... a SingleDay. That is it ... anything coarser has to be done by means of an Algorithm (e.g. Age() ).

Subject: Which Production Class DBMSs?
Posted by: Anonymous (not signed in)
Posted on: Wednesday, January 07, 2009 at 9:24 AM
Message: The product RAMIS, which was developed by Mathematica, Inc., a Princeton-based consulting firm headed by Oskar Morgenstern and Tibor Fabian, was the first 4GL. RAMIS was the direct ancestor of FOCUS, having been principally developed by Gerald Cohen and Peter Mittleman while working at Mathematica in 1970.

From Wikipaedia

Subject: "most recent copy of the SQL Standard"
Posted by: Mike Gorman (view profile)
Posted on: Friday, January 09, 2009 at 8:03 AM
Message: There are a large quanity of SQL standards materials on pages from a link on the Whitemarsh website. www.wiscorp.com. The link is at the bottom of the home page, SQL Standards.

If anybody's interested, and I'd be happy to post proposed SQL Standards related papers to the website and give notice here. I'd then be more than happy to bring your comments to the "SQL" committee. Actually, it's the ANSI INCITS H2 Technical Committee on Database Languages. I've been its secretary since Meeting Zero in April 1978.

Regards,
Mike Gorman,
Secretary of H2

Subject: SQL Server 2008 new DateTime2
Posted by: san_kan1gb (view profile)
Posted on: Monday, February 09, 2009 at 4:44 AM
Message: it would be very nice if u made an article continuing this one but on the SQL Server 2008 new DateTime2 datatype.

Subject: Temoral era has begin again
Posted by: Ayyappan (view profile)
Posted on: Monday, September 12, 2011 at 6:45 PM
Message: It is nice article. So of the vendors are slowly inculding bitemportal feature in to their database management system.


 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.