CODEWITHSUNDEEP
sqlhadoophivehiveql
Praveena Gunasekera
Praveena Gunasekera

Reputation: 79

Retrieving the month from the timestamp format "25/Nov/2016:15:48:01 +0000'

I'm currently working with Hive SQL and I have the 'timestamp' column in a table with the format "25/Nov/2016:15:48:01 +0000".

How can I extract the month which is 'Nov' from the above format without using SUBSTRING() function.

I tried,

SELECT MONTH(timestamp) FROM table_name;

But it returned as null. What should be the correct way to retrieve the month which is 'Nov' from the above timestamp format.

Upvotes: 2

Views: 221

Answers (1)

Ram Ghadiyaram
Ram Ghadiyaram

Reputation: 29165

Please see the below example..

  • unix_timestamp(time,'dd/MMM/yyyy:HH:mm:ss') and then try your month function

    OR

  • First convert your timestamp to string to apply month function below like this example

string from_unixtime(bigint unixtime[, string format]) Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of "1970-01-01 00:00:00"

then you can apply month function which will return month int format which can be converted equivalent String later

int month(string date) Returns the month part of a date or a timestamp string: month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11.

Upvotes: 1

Related Questions