How to handle database's timestamp data type?

How-to(s), solutions to common problems, pearls of wisdom

avackova
Posts: 841
Joined: Fri Jul 20, 2007 9:28 am

How to handle database's timestamp data type?

Postby avackova » Wed Oct 01, 2008 10:18 am

Databases usually have three time types: DATE, TIME and TIMESTAMP, but Clover.ETL has only DATE data field. How can I assure that I send proper type to database?

This hint applies namely to Oracle, but can be used in general.
Agata Vackova
Javlin a.s.
agata.vackova@javlin.eu

avackova
Posts: 841
Joined: Fri Jul 20, 2007 9:28 am

Postby avackova » Wed Oct 01, 2008 10:33 am

Clover.ETL chooses proper database type depending on the format set on the data field. When format contains date symbols only (GyMwWDdFE), type is "translated" to DATE, when it contains time symbols only (aHhKkmsSzZ) , type is "translated" to TIME, in other cases (format contains both symbols or is not set at all) type is "translated" to TIMESTAMP. This is general rule for default jdbc configuration. In addition, for different jdbc configurations (jdbcSpecific attribute in db connection configuration), rules can be slightly changed, eg. for Oracle, only fields with format containing only date symbols are treated as DATE type, other fields are treated as TIMESTAMPs.
Agata Vackova

Javlin a.s.

agata.vackova@javlin.eu