Convert string to timestamp in Impala

Question

How can I convert a string representing a datetime in the format "YYYY-MM-DD;HH:MM:SS" (i.e. 2016-04-11;19:38:01) to a proper timestamp?

I thought this would work, but it doesn't.

select 
  from_unixtime(unix_timestamp(`date`, "YYYY-MM-DD;HH:MM:SS"))
from t1
limit 100;

Answer 1

The return type of from_unixtime() isn't a "proper timestamp" but a string. (And the return type of unix_timestamp() isn't... a timestamp, but a bigint).

Here is what I do to get a timestamp return type:

select
  cast(unix_timestamp(`date`, "yyyy-MM-dd;HH:mm:ss") as timestamp)
from t1
limit 100;

For the sake of completeness, here is how I deal with timezones, locally formatted dates, and storing them as UTC timestamps:

select
  to_utc_timestamp(cast(unix_timestamp(`date`, "yyyy-MM-dd;HH:mm:ss") as timestamp), "Europe/Paris")
from t1
limit 100;

This assumes the -use_local_tz_for_unix_timestamp_conversions startup flag is off (this is the default).

Answer 2

As suggested by @jbapple in a comment to my question, the issue is with the capitalization. As stated in the Impala documentation

Currently, the format string is case-sensitive, especially to distinguish m for minutes and M for months. In Impala 1.3 and later, you can switch the order of elements, use alternative separator characters, and use a different number of placeholders for each unit. Adding more instances of y, d, H, and so on produces output strings zero-padded to the requested number of characters. The exception is M for months, where M produces a non-padded value such as 3, MM produces a zero-padded value such as 03, MMM produces an abbreviated month name such as Mar, and sequences of 4 or more M are not allowed. A date string including all fields could be "yyyy-MM-dd HH:mm:ss.SSSSSS", "dd/MM/yyyy HH:mm:ss.SSSSSS", "MMM dd, yyyy HH.mm.ss (SSSSSS)" or other combinations of placeholders and separator characters.

The right way of writing it is:

select 
  from_unixtime(unix_timestamp(`date`, "yyyy-MM-dd;HH:mm:ss")) 
from t1 
limit 100;