3

I have created a table called 'Chicago crime data' I need to do some queries on it but the table wont load. The message i get is (timestamp format does not match data in the column 'UPDATEDON')

What is populated in the Date column is MM-DD-YYYY, I need to change it to DD-MM-YYYY HH:MM:SS. I have tried selecting the DD-MM-YYYY under the dropdown of Date Format and then the HH:MM:SS under Timestamp format, but when i complete and begin the load, it says the load succeeded but all 533 rows rejected and when i attempt to view the table (understandably) it says there is no data here yet.

To my understanding you should be able to do a custom change on the time format, but i cant see how? Any help would be much appreciated. Thanks.

timestamp, time format table loaded with errors

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Marls
  • 31
  • 1
  • 2
  • What is the datatype of column `updatedon`? – GMB Jun 22 '20 at 22:48
  • 2
    The timestamp format pattern must match _what's in the input file_, not what you want it to be. – mustaccio Jun 22 '20 at 23:48
  • Ah ok thanks guys, your comments has helped. Don’t why it didn’t occur to me before, but I’ll check the above info in the csv file. Let you know the outcome. – Marls Jun 23 '20 at 07:03
  • This may help someone else with the same problem. the solution in the end to the above was a simple one. First drop the table and re-create it, then switch off ' detect data types' button. Click the pencil icon under the DATE and UPDATEDON column and change to 'VARCHAR' problem solved. – Marls Jun 23 '20 at 22:04
  • It is generally a Bad Idea™ to store date-time values in character columns. – mustaccio Jun 24 '20 at 12:02
  • i know but its for an assignment. i didnt create the csv – Marls Jun 25 '20 at 14:45

4 Answers4

3

I too faced the same issue. I fixed it by as below:

  1. Paste some random text into TIMESTAMP dropdown field, then you edit option will be enabled for the field As in this pic

2. Then enter your desired timestapm(in your case: MM/DD/YYYY HH:MM:SS TT) 3. Ignore the warinigs and proceed further. You will be able to upload without any rejections.

Bharat Kammakatla
  • 31
  • 2
2

Simply click on the timestamp dropdown and start typing your custom timestamp which in this case is "MM/DD/YYYY HH:MM:SS TT"

user15012040
  • 21
  • 1
1

I had this same problem on this IBM course - I found that the way around it was to try to paste something into the Timestamp format box on db2. This worked even though I previously hadn't been able to type in that box, and once I had pasted something I was able to type in that box again.

Andy
  • 21
  • 1
0

I did it with pandas: First I have opened Jupyter-Notebook, and read the csv:

    df = pd.read_csv("Chicago_Crime_Data-v2.csv")

Then I used this function:

    df['DATE'] = pd.to_datetime(df.DATE)

And I save it back to the file:

    df.to_csv("Chicago_Crime_Data-v2.csv")

Now it would accept it. (Just see if there is more than one column with this problem)

Yosef Cohen
  • 91
  • 2
  • 8