Data type for oracle datetime

Question

I am creating a database and i am a bit confused as to using date or timestamp for a field.

I need to create some triggers to check available dates and times so i thought that i could use the same field to store the two values.

I am more inclined into using TIMESTAMP and therefore;

TO_TIMESTAMP('18/03/2012 02:24', 'DD/MM/YYYY HH24:MI')

Will using timestamp work better than using date? or date and time in different fields.

Cheers

Answer 1

the DATE datatype also stores times, so you wouldn't need two fields. The difference between the two is that

1. timestamp goes down to fractional seconds
2. deducting two timestamps (timestamp-timestamp) results in an INTERVAL datatype answer. For dates, you get a NUMBER which is a figure in days.
3. if you need to store timezones, then TIMESTAMP WITH TIME ZONE is available, there is no way to do this in a date field.
4. dates take slightly less storage space (7 bytes vs timestamps 11 bytes) (when the precision of timestamp is > 0..otherwise timestamp(0) is also 7 bytes)

the timestamp data type is more modern (ASNI) whereas DATE has been around in Oracle longer.