Oracle Database Discussions

I have a flat file with (among other things) a timestamp which happens to always be in UTC. The format is this:

2008-03-23 16:23:45.389

This data I would like to import into a table column of type TIMESTAMP(3), i.e. a column that use the database time zone.

The question is how do I tell the SQL*Loader that my flat file data is in UTC time ? As far as I know "UTC" is not really a time zone rather you can say it means "no time zone". This means all tricks you can come up with that involves time zone conversion will not work.

I have the feeling that I'm making this too complex.

What I currently do is that I import as-is and then after the import I do :

UPDATE mytable
SET tstamp = tstamp + (tstamp - sys_extract_utc(tstamp)) ;


As we are talking a lot of data here this is of course highly inefficient, would be a lot better to do it as part of the import.

Please further note: While the above UPDATE handles the conversion from UTC to db time it is not fully resilient to Daylight Savings changes as it will be an incorrect UPDATE if some of the data in the table are before Dayligh Savings change date and some are after.


Thanks.