Thursday, February 03, 2011

TIMESTAMP versus DATE/TIME

Consider a database design decision point where you need to store both date and time information on a single row in DB2. Is it better to use a single TIMESTAMP column or two columns, one DATE and the other TIME?


Well, of course, the answer is "it depends!" The correct solution will depend on several factors specific to your situation. Consider the following points before making your decision:

  • With DATE and TIME you must use two columns. TIMESTAMP uses one column, thereby simplifying data access and modification.
  • The combination of DATE and TIME columns requires 7 bytes of storage, while a TIMESTAMP column always requires 10 bytes of storage. Using the combination of DATE and TIME columns will save space.
  • TIMESTAMP provides greater time accuracy, down to the microsecond level. TIME provides accuracy only to the second level. If precision is important, use TIMESTAMP. Use TIME if you want to ensure that the actual time is NOT stored down to the microsecond level.
  • A TIMESTAMP can always be broken down into a DATE and a TIME component, after which you can treat the data just like DATE and TIME data.
  • Date and time arithmetic probably will be easier to implement using TIMESTAMP data instead of a combination of DATE and TIME. Subtracting one TIMESTAMP from another results in a TIMESTAMP duration. To calculate a duration using DATE and TIME columns, two subtraction operations must occur: one for the DATE column and one for the TIME column.
  • Formatting may be easier with DATE and TIME data. DB2 provides for the formatting of DATE and TIME columns via local DATE and TIME exits, the CHAR function, and the DATE and TIME precompiler options. If the date and time information is to be extracted and displayed on a report or by an online application, the availability of these DB2-provided facilities for DATE and TIME columns should be considered when making your decision.
  • Prior to DB2 V9, not much help was available for the formatting of TIMESTAMP columns.But DB2 9 for z/OS adds the TIMESTAMP_FORMAT function, which offers three different formats for displaying timestamp data.
Upon reviewing all of these details, and factoring in your usage requirements, you can then make an informed decision about whether to use one TIMESTAMP column, or two columns, one DATE and one TIME.

3 comments:

Anonymous said...

Hi Craig,

This question might not be the desired one on this post but let me ask this.

-904 SQLCODE: Unavailable resource.
-911 SQLCODE: Deadlock or timeout.But i am talking about timeout here.So we will get timeout since the resource is not available so the application will go in suspention mode and wait for some time( equal to IRLMRWT DSNZPARM paramater)and then timeout.


SO WHAT EXACTLY THE DIFFRANCE BETWEEN -911 WITH TIMEOUT AND -904.

Craig S. Mullins said...

A -911 is a lock timeout and occurs only when trying to access data that is locked by another process.

A -904 is a resource unavailable, and can be caused by any number of resources that are not available. The -904 will have an associated reason code and type code. The reason tells you the "why" and the type code will tell you the "what." Reason codes and type codes are documented in the DB2 for z/OS Codes manual which is GC19-2971 for Version 10.

Nitin said...

Hi Craig,

Thanks for the clarification.

So it mean that in case of -904 the process doesn't wait for resource to get free.