Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

convert date in string datatype to timestamp datatype

avatar
Explorer

Hello All,

convert date in string datatype to timestamp datatype

3 REPLIES 3

avatar
Expert Contributor

Hi @Rak ,

 

Could you please elaborate on what component you are specifically doing this (Hive, Impala, etc..) so we can properly label this question and answer accordingly?



Robert Justice, Technical Resolution Manager


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.

Learn more about the Cloudera Community:

Terms of Service

avatar
Explorer
Dear Sir,
We are trying to merge two tables and
In this date datatype timestamp when I use the date values are shown null
and when I use date string they are shown properly
And merge operation doesn't happen.

avatar
Expert Contributor

@Rak ,

 

I'm suspecting you are using Impala, because of the statement of the values returning null, but please confirm so I can properly label this question so others can see it.   Please see the following in the Impala documentation regarding the proper format for casting a string into a timestamp datatype:

 

https://docs.cloudera.com/documentation/enterprise/latest/topics/impala_timestamp.html

 

Restrictions:

If you cast a STRING with an unrecognized format to a TIMESTAMP, the result is NULL rather than an error. Make sure to test your data pipeline to be sure any textual date and time values are in a format that Impala TIMESTAMP can recognize.

Currently, Avro tables cannot contain TIMESTAMP columns. If you need to store date and time values in Avro tables, as a workaround you can use a STRING representation of the values, convert the values to BIGINT with the UNIX_TIMESTAMP() function, or create separate numeric columns for individual date and time fields using the EXTRACT() function.

Examples:

The following examples demonstrate using TIMESTAMP values with built-in functions:

select cast('1966-07-30' as timestamp);
select cast('1985-09-25 17:45:30.005' as timestamp);
select cast('08:30:00' as timestamp);
select hour('1970-01-01 15:30:00');         -- Succeeds, returns 15.
select hour('1970-01-01 15:30');            -- Returns NULL because seconds field required.
select hour('1970-01-01 27:30:00');         -- Returns NULL because hour value out of range.
select dayofweek('2004-06-13');             -- Returns 1, representing Sunday.
select dayname('2004-06-13');               -- Returns 'Sunday'.
select date_add('2004-06-13', 365);         -- Returns 2005-06-13 with zeros for hh:mm:ss fields.
select day('2004-06-13');                   -- Returns 13.
select datediff('1989-12-31','1984-09-01'); -- How many days between these 2 dates?
select now();                               -- Returns current date and time in local timezone.

 



Robert Justice, Technical Resolution Manager


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.

Learn more about the Cloudera Community:

Terms of Service