EDT and EST timestamp sqlldr data load in oracle

Question

I have issue while loading data from flat file to Oracle database with Timestamp column via sqlloader. The problem is I have a data for both EST and EDT like below mentioned format

test_data.dat

Thu Mar 07 14:27:14 EST 2013
Thu Mar 07 14:27:27 EST 2013
Tue Apr 09 18:20:54 EDT 2013
Tue Apr 09 18:24:26 EDT 2013

My sqldrfile.ctl code is below

LOAD DATA
INFILE 'test_data.dat'
TRUNCATE
INTO TABLE MY_TABLE    FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
        "DOC_DATE_ADDED" TIMESTAMP WITH TIME ZONE "DY MON DD HH24:MI:SS TZR YYYY",
)

Rejected_data.bad the below two records not recognized and went to bad file

Tue Apr 09 18:20:54 EDT 2013
Tue Apr 09 18:24:26 EDT 2013

Other two EST records loaded correctly into oracle table.. I need a sqlldr code to load for both the timestamp EST and EDT. Please guide me on this.. :(

Answer 1

For those developers from other countries, I would like to quote askanydifference website here:

The key difference between Eastern Standard Time and Eastern Daylight Time lies in the fact that although they are used for the same time zones, they are used at different times of the year.

The Eastern Standard Time is used during the fall-winter season. It is used from the first Sunday of November.

Eastern Daylight Time is also an hour ahead of the Eastern Standard Time.

The basic understanding of EDT and EST goes a long way in dealing with times during development in any kind of programming language.

Answer 2

If you can't change the format of the data in the file, and can't manipulate the file before loading it, you could replace a specific EDT value with the region value US/Eastern (or any suitable value like America/New_York) with an SQL operator:

"DOC_DATE_ADDED" TIMESTAMP WITH TIME ZONE "DY MON DD HH24:MI:SS TZR YYYY"
  "REPLACE(:DOC_DATE_ADDED, 'EDT', 'US/Eastern')"  

(split into two lines for readability, but you can do that in the control file too).

When your sample data file is loaded the table contains:

select to_char(doc_date_added, 'YYYY-MM-DD HH24:MI:SS TZD') as TZD,
   to_char(doc_date_added, 'YYYY-MM-DD HH24:MI:SS TZR') as TZR
from my_table;

TZD                     TZR                          
----------------------- ------------------------------
2013-03-07 14:27:14 EST 2013-03-07 14:27:14 EST        
2013-03-07 14:27:27 EST 2013-03-07 14:27:27 EST        
2013-04-09 18:20:54 EDT 2013-04-09 18:20:54 US/EASTERN 
2013-04-09 18:24:26 EDT 2013-04-09 18:24:26 US/EASTERN 

... so you preserve the EST/EDT split; though the TZR shows US/EASTERN and EST - so it might be better to change the EST value as well, with a nested REPLACE or with:

"DOC_DATE_ADDED" TIMESTAMP WITH TIME ZONE "DY MON DD HH24:MI:SS TZR YYYY"
  "REGEXP_REPLACE(:DOC_DATE_ADDED, 'E[SD]T', 'US/Eastern')"

Or if all your values are always EST/EDT, you could do the timestamp conversion explicitly and just cut out the actual string you're given:

"DOC_DATE_ADDED" CHAR "FROM_TZ(TO_TIMESTAMP(SUBSTR(:DOC_DATE_ADDED, 1, 19)
  || SUBSTR(:DOC_DATE_ADDED, 25, 29), 'DY MON DD HH24:MI:SS YYYY'), 'US/Eastern')"

Which loads your data as:

TZD                     TZR                          
----------------------- ------------------------------
2013-03-07 14:27:14 EST 2013-03-07 14:27:14 US/EASTERN 
2013-03-07 14:27:27 EST 2013-03-07 14:27:27 US/EASTERN 
2013-04-09 18:20:54 EDT 2013-04-09 18:20:54 US/EASTERN 
2013-04-09 18:24:26 EDT 2013-04-09 18:24:26 US/EASTERN 

The danger with that is that if you ever do get a value in a different time zone it'll silently be recorded against the wrong region, whereas the first version will either process it successfully or reject it, depending on whether it's recognised (i.e. in Wernfried's first list).

Answer 3

EDT ist not a valid timezone region, this is a time zone abbreviation.

Valid regions are these ones:

SELECT * 
FROM V$TIMEZONE_NAMES
WHERE TZNAME = TZABBREV;

TZNAME  TZABBREV
======================
CET     CET
CST     CST
EET     EET
EST     EST
GMT     GMT
HST     HST
MET     MET
MST     MST
PST     PST
WET     WET

The reason for that you see by this query. EDT may represent several different time zone regions from UTC-04:00 to UTC-06:00.

SELECT TZNAME, TZ_OFFSET(TZNAME)
FROM V$TIMEZONE_NAMES
WHERE TZABBREV = 'EDT'
ORDER BY 2;

TZNAME                          TZ_OFFSET(TZNAME)
------------------------------- -----------------
America/Santo_Domingo                    -04:00           
America/Fort_Wayne                       -05:00           
America/Grand_Turk                       -05:00           
America/Indiana/Indianapolis             -05:00           
America/Indiana/Marengo                  -05:00           
America/Indiana/Petersburg               -05:00           
US/Michigan                              -05:00           
America/Detroit                          -05:00           
US/Eastern                               -05:00           
America/Indiana/Vevay                    -05:00           
America/Indiana/Vincennes                -05:00           
America/Indiana/Winamac                  -05:00           
America/Indianapolis                     -05:00           
America/Iqaluit                          -05:00           
America/Jamaica                          -05:00           
America/Kentucky/Louisville              -05:00           
America/Kentucky/Monticello              -05:00           
America/Louisville                       -05:00           
America/Montreal                         -05:00           
America/Nassau                           -05:00           
America/New_York                         -05:00           
America/Nipigon                          -05:00           
America/Pangnirtung                      -05:00           
America/Port-au-Prince                   -05:00           
America/Thunder_Bay                      -05:00           
America/Toronto                          -05:00           
Canada/Eastern                           -05:00           
EST5EDT                                  -05:00           
Jamaica                                  -05:00           
US/East-Indiana                          -05:00           
America/Cancun                           -06:00           
America/Indiana/Tell_City                -06:00           

32 rows selected.

I assume you have to modify your text file. I don't know if sqlloader supports any in-line transaltions, e.g. from EST to EST5EDT