0

I have some web logs that I'm trying to import into Oracle SQL table. The table has timestamp and varchar for when request was made and url. I formatted the logs to looks like this:

"Nov 1 2021 2:12:54.856 CDT","/webfolder1/file.html"
"Dec 11 2021 5:32:13.34 CDT","/webfolder1/file2.html"

I used SQL developer tool to import this file to Oracle table, however it did not like the date in the first column.

VALUES (to_timestamp('Nov 1 2021 2:12:54.856 CDT'), '/webfolder1/file.html')
...

ERROR:

ORA-01858: a non-numeric character was found where a numeric was expected

Do I need to use sed/awk or some other utility to change the date into some other format that Oracle would accept?

dkgcb
  • 81
  • 1
  • 2
  • 9
  • Is CDT appropriate for dates in December - I think DST ended Nov 7 last year? Or is that a fixed string and doesn't reflect DST properly; in which case what do you actually want to store in your column - just the date/time shown (completely ignoring the CDT string) as a plain timestamp, or that time as it is but stated as US/Central or America Chicago, or declared as CDT but adjusted by an hour to CST (either plain or with time zone region), or... something else? – Alex Poole Mar 28 '22 at 16:24
  • You are correct. The data above is just modified sample, the actual log files have CST and CDT. – dkgcb Mar 29 '22 at 18:30

1 Answers1

1

Use TO_TIMESTAMP_TZ and specify a format model and language:

CREATE TABLE table_name (
  ts       TIMESTAMP WITH TIME ZONE,
  filename VARCHAR2(50)
);

Then:

INSERT INTO table_name (ts, filename) VALUES (
  to_timestamp_tz(
    'Nov 1 2021 2:12:54.856 CDT',
    'Mon DD YYYY HH24:MI:SS.FF3 TZD',
    'NLS_DATE_LANGUAGE=English'
  ),
  '/webfolder1/file.html'
);

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
  • 1
    TZD is ignoring the CDT abbreviation (and TZR doesn't recognise it); it can be any value, or missing. It seems to be picking up the current offset for the system - which is +1:00 as UK just moved from GMT to BST. [Modified db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=4d118cefa96d1bdf9ca3e321a8dd7c94). The OP probably needs to convert CDT (and CST) to a recognised region name, and then use TZR? – Alex Poole Mar 28 '22 at 15:21