How to choose cleverly DATE,TIME OR TIMESTAMP for Table Columns

This is absolutely nice thought. A column data type in DB2 table can be a DATE or TIME or it can be a TIMESTAMP. 

DATE takes 4 bytes and TIME takes 3 bytes. So total 7 bytes. Where as TIMESTAMP takes 10 bytes. So storage wise DATE and TIME are much better to select two separate columns.

DATE is dec(8,0) and TIME is dec(8,0), so you can do addition and subtraction in both date and time. This is an easy calculation.

DATE: YYYYMMDD

TIME: HHMMSS

In case of Timestamp it is Dec(20,0), do the format is YYYYYXXDDHHMMSSZZZZZZ , even you can do subtraction or addition on Timestamp. But, this is more complex calculation.

There is an another case that is choosing two columns DATE and TIME instead of single column of TIMESTAMP, which causes more I/o and it impacts on performance. 

The final judgement is select these columns based on project requirements.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.