Converting string to timetsamp in Hive

Question

I have a pipeline where im getting data from sqlserver and load it into Hive table.I have a timestamp column in the source which is like 'YYYY-MM-DD HH:MM:SS'

Sql table(datetime) ---> Hive stage table(string)---->Hive final table(timestamp)

The source table is in US/Pacific time zone. In the middle stage table, the format is like 'YYYY-MM-DD HH:MM:SS.0'.

How do i convert into a timestamp field for the final table? I want the final table column to look like 'YYYY-MM-DD HH:MM:SS'

I see from_unixtime being used, but when i try like below,it returns null.

FROM_UNIXTIME(UNIX_TIMESTAMP('date column','yyyy-mm-dd HH.mm.ss')) as ts

Im pretty new to using Hive and need some suggestion on what should i do here, Thanks.

Answer 1

If the timestamp string is in format 'yyyy-MM-dd HH:mm:ss.S' then you can cast it to timestamp type using timestamp() function.

timestamp(col)

Also you can insert string directly into timestamp column.

It works because 'yyyy-MM-dd HH:mm:ss.S' - is a default timestamp format.

You need conversion using FROM_UNIXTIME(UNIX_TIMESTAMP(col, format)) if the format is not 'yyyy-MM-dd HH:mm:ss.S'. This format you should convert to, not from. Specify correct FROM format, it is case-sensitive: MM is not the same as mm, delimiters do matter: dot is not the same as semicolon or space, etc.

See format manual here: SimpleDateFormat

Also see this post about timestamp with nanoseconds