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 result 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 pre-compiler 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.

This post was originally published on Craig Mullins’ blog at: http://db2portal.blogspot.com/2011/02/timestamp-versus-datetime.html