I have a working control file for sql loader where I can upload my timestamps in the various format I wish.
my_timestamp TIMESTAMP \"dd-MON-yy hh.mi.ss.ff6 PM\",
But I want to upload the time stamp from my .csv file in milliseconds into the NUMBER field in my db. Any suggestions if this is even possible?
I am assuming you want to strip off the milliseconds part and store it separately.
Untested:
...
my_timestamp TIMESTAMP "dd-MON-yy hh.mi.ss.ff6 PM"
my_milliseconds NUMBER "regexp_substr(:my_timestamp, '\.(\d+) ', 1,1,null,1)"
...
But this works so the above should work:
select regexp_substr('07-JAN-14 04.24.00.123456 PM', '\.(\d+) ', 1,1,null,1)
from dual;
Returns the first group (surrounded by parens) that matches the pattern of a string of one or more numbers surrounded by a period on the left and a space on the right.
EDIT: Oh, you need the entire timestamp converted to UNIX time, which is the number of seconds since 1/1/1970 0:0:0 (the epoch).
First you need the formula to convert a date to UNIX time. A little googling revealed this one:
SELECT (cast(to_timestamp('14.03.2010 08:16:22.123456 AM','dd.mm.yyyy hh:mi:ss.FF6 AM')as date) - TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS')) *
24 * 60 * 60
FROM DUAL;
Which results in '1268554582'. Converting this UNIX time back to a date using the epoch converter: http://www.epochconverter.com/ verified that the correct date was returned.
So, how to use it in the control file? If you can select it as above, it can be put in the control file like this:
...
my_timestamp TIMESTAMP "dd-MON-yy hh.mi.ss.ff6 PM"
my_unixtime NUMBER "(cast(:my_timestamp)as date) - TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS')) *
24 * 60 * 60"
...
You might need the EXPRESSION keyword, I'm not sure:
my_unixtime NUMBER EXPRESSION "(cast(:my_timestamp)as date) - TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS')) *
24 * 60 * 60"
Now that works for one column, but what if there were many, or you want to reuse this? For reuse and maintainability, put the code in one place using a function that accepts a timestamp and returns unixtime as mentioned above and call that from your control file instead:
...
my_timestamp TIMESTAMP "dd-MON-yy hh.mi.ss.ff6 PM"
my_unixtime NUMBER "my_timestamp_to_unix_converter(:my_timestamp)"
...
Caveats:
milliseconds are lost as UNIX time is seconds since 1/1/1970, not milliseconds. If milliseconds are important to you, you may need to save them separately as shown above.
Could be trouble when the year 2038 rolls around: http://en.wikipedia.org/wiki/Year_2038_problem
Resources:
http://www.epochconverter.com/
http://orcasoracle.squarespace.com/oracle-rdbms/2009/10/9/converting-timestamp-to-date.html