Oracle DBA – A lifelong learning experience

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.

DeviceId    Timestamp
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

Log on – open up the Tables filter and right click and select import data

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.


One Response to “Loading timestamp with timezone into a table using SQL Developer”

  1. Sriram said

    Thanks for the post…i checked your site and got quick alternative solution

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: