CODEWITHSUNDEEP
oracle-databasesql-loader
shorif2000
shorif2000

Reputation: 2654

sqlloader loading unix timestamp

I cannot seem to load a unix timestamp using SQLLDR.

load data infile 'AST_BusinessService_export.csv' 
append into table ccp.services_temp 
 fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS    
 (NAME, SHORT_DESCRIPTION, CREATE_DATE EXPRESSION "(cast(:CREATE_DATE)as date) - TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS')) *
24 * 60 * 60")

I am trying to pass 

TEST-CI-GO,TIER2_H1_2 FW,1233817523

Error I receive is SQL*Loader-291: Invalid bind variable CREATE_DATE in SQL string for column CREATE_DATE.

UPDATE

load data infile 'AST_BusinessService_export.csv'
append into table ccp.services_temp
 fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS
 (NAME, SHORT_DESCRIPTION, CREATE_DATE EXPRESSION to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 ) * CREATE_DATE

error is Expecting SQL string, found "to_date". (NAME, SHORT_DESCRIPTION, CREATE_DATE EXPRESSION to_date('19700101',

Upvotes: 0

Views: 939

Answers (1)

tbone
tbone

Reputation: 15483

If your input looks like:

TEST-CI-GO,TIER2_H1_2 FW,1233817523

I believe you want to convert to date by doing something like:

select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 ) * 1233817523
from dual;

Output:

2/5/2009 7:05:23 AM

So start at the epoch and add that many seconds (converted to days since thats what Oracle wants for adding to dates)

EDIT:

For a table like this:

create table test_unixdate
(
id number,
secs_since_epoch number,
converted_date date
);

And data like this:

1,1233817521
2,1243817522
3,1253817523
4,
5,1263817525

Your sqlldr might look like:

LOAD DATA
infile 'test_unixdate.csv'
badfile 'test_unixdate.bad'
discardfile 'test_unixdate.dis'
APPEND
INTO TABLE test_unixdate
Fields terminated by ","
Optionally enclosed by '"'
trailing nullcols
(
id nullif id=blanks,
secs_since_epoch nullif secs_since_epoch=blanks,
converted_date "to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 ) * :secs_since_epoch"
)

Output (select * from test_unixdate):

1   1233817521  2/5/2009 7:05:21 AM
2   1243817522  6/1/2009 12:52:02 AM
3   1253817523  9/24/2009 6:38:43 PM
4       
5   1263817525  1/18/2010 12:25:25 PM

Upvotes: 1

Related Questions