Loading timestamp with timezone into a table using SQL Developer
Posted by John Hallas on November 22, 2014
I will start this post off stating that I know there are better and more efficient methods of doing this but this is the best method I could come up with.
I had a csv file with a number of columns, one of which was data in a timestamp with timezone format.
A few rows looked like this – note I have stripped the other columns out for ease of display.
7941 11/9/2014 12:46:49 AM +00:00
8533 11/9/2014 12:46:49 PM +00:00
3692 11/9/2014 1:10:36 PM +00:00
So data is in 12 hour format and the line length varies between 28 and 29 depending on whether the hour has 1 or 2 digits.
I decided to import the data using SQL Developer and that turned out to be surprisingly easy
Then go through the wizard until you get to the screen to format the column you are interested in. Believe me I tried every method known to man with no success.I tried date, timestamp, timestamp with timezone and every permutation of format I could try in the wizard but I could not get the data into the table in anything other than varchar2 format.
I do not doubt that there is a method but I could not find it. So I ended up taking a roundabout route which did work.
I brought the column in as a varchar2 column, added a new column, converted/copied the data into the new column using a date mask and then dropped the original column
alter table wireless add col2 timestamp with time zone; update wireless set col2=to_timestamp_tz(timestamp,'mm/dd/yyyy hh12:mi:ss AM TZR'); alter table wireless drop column timestamp; alter table wireless rename column col2 to timestamp;
Hey presto job done.
Not the most elegant solution but I used SQL Developer to import data in from a csv file for the first time and achieved the end result I wanted.
This entry was posted on November 22, 2014 at 8:59 pm and is filed under Oracle. Tagged: conver varchar2 to timestamp format, import csv file through SQL Developer, timestamp with timezone. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.