hadoop - HIVE datetime format with timezone

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

I have a unix timestamp that is getting converted using a view in HIVE before it gets inserted into a sql server database. The problem I am having is I need the right format to insert it into a datetimeoffset(2) field in sql server. Here is an example from hive:

Query: select from_unixtime(1413587962, 'yyyy.MM.dd hh:mm:ss z');

Result: 2014.10.17 11:19:22 UTC

SQL server is not able to convert that format implicitly. If I run this is SQL server:

Query: SELECT CONVERT(DATETIMEOFFSET(2), '2014.10.17 11:19:22 UTC')

Result: Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

If I change the UTC to +00:00 SQL server converts to DATETIMEOFFSET(2) just fine.

What is the format that I need to use in HIVE to render the hours and min in the offset instead of the text time zone? I have tired [+|-]hh:mm, hh:mm, and z.

May be does this help, Captial Z select from_unixtime(1413587962, 'yyyy.MM.dd hh:mm:ss Z') 2014.10.17 11:19:22 +0000

 If that does not work direct +00:00 works? 
select from_unixtime(1413587962, 'yyyy.MM.dd hh:mm:ss +00:00')
                That gets me closer, SQL server is still not able to implicitly convert +0000.  It needs to have +00:00.  Thank you.
– carbon
                Nov 15, 2016 at 16:05
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.