question Importing Timestamp values returning nulls in Archives of Support Questions (Read Only)

Importing Timestamp values returning nulls

Stroop — Tue, 21 Apr 2026 13:45:14 GMT

I'm attemtping to import a csv file into a table into Hadoop.

The file has imported but the fields with a timestamp field have returned as null for all rows (no nulls in original file).

I've currently formatted the columns in the csv file as custom format yyyy-mm-dd hh:dd:ss

and set the field in the Hadoop table as a timestamp. Presumably the excel format isn't being recognised.

How can I resolve this?

Thanks

Re: Importing Timestamp values returning nulls

tianyiwang — Mon, 27 Nov 2017 22:39:39 GMT

The standard timestamp format is"yyyy-MM-dd HH:mm:ss[.SSSSSS]". Is there any warning printed when selecting from this table? Could you share a sample row in the text file?

Re: Importing Timestamp values returning nulls

Stroop — Tue, 28 Nov 2017 14:04:15 GMT

Example of the field is

27/01/2016 11:25:00

None of the fields have any second values. They're all 00.

Within the file it's formatted as dd/mm/yyyy hh:mm.

I've tried changing it to yyyy-mm-dd hh:mm:ss but when I do that, save and re-open it's reverted to the original format.

I can upload into a text field (and it appears as 27/01/2016 11:25) but then if I cast the field as a timestamp it then returns null values.

Re: Importing Timestamp values returning nulls

tianyiwang — Tue, 28 Nov 2017 19:29:32 GMT

You can create a table with a text column first, and then use to_timestamp to convert it into a table with a timestamp column.

create table timestamp_table as select column_1, column_2, to_timestamp(column_3, "dd/MM/yyyy HH:mm") from text_table;

For documentations on timestamp-related functions, please visit https://impala.apache.org/docs/build/html/topics/impala_datetime_functions.html

Re: Importing Timestamp values returning nulls

Stroop — Fri, 01 Dec 2017 11:52:40 GMT

Thanks, this function worked