How to Change Date Format in Hive

How to change date format in hive?

To convert date string from one format to another you have to use two date function of hive

1. unix_timestamp(string date, string pattern) convert time string
   with given pattern to unix time stamp (in seconds), return 0 if
   fail.
2. 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.

Using above two function you can achieve your desired result.

The sample input and output can be seen from below image:

The final query is

select from_unixtime(unix_timestamp('2016/06/01','yyyy/MM/dd'),'yyyy-MM-dd') from table1; 

where table1 is the table name present in my hive database.

I hope this help you!!!

How to turn date format to YYYYMMDD format in Hive QL

In Hive, you would typically use an intermediate conversion to a unix timestamp:

from_unixtime(unix_timestamp() - 14 * 24 * 60 * 60, 'yyyyMMdd')

unix_timestamp() returns the current date/time as an epoch timestamp; you can then substract 14 days (expressed as seconds), then use from_unixtime() to format the result as a string in the target format.

Hive - the correct way to permanently change the date and type in the entire column

Try this:

UPDATE moviesnames SET prodate = to_date(from_unixtime(UNIX_TIMESTAMP(prod_date,'dd-MMM-yyyy')));

How to Change date format in hive

Can you try the unix_timestamp as below and see if this works.

unix_timestamp(dates, 'MM/dd/yyyy h:mm:ss a')

To know more about date formatting check this link : http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

PS: I have not tried this as I do not have a hive setup in front of me now.

Want to convert timestamp to date format in hive

The best methoid is to do without unix_timestamp/from_unixtime if possible and in your case it is possible. date() can be removed, string in yyyy-MM-dd format is compatible with date type:

select date(concat_ws('-',substr(ts,1,4),substr(ts,5,2),substr(ts,7,2)))
from
(
select '20210412070422' as ts
)s

Result:

2021-04-12

Another efficient method using regexp_replace:

select regexp_replace(ts,'^(\\d{4})(\\d{2})(\\d{2}).*','$1-$2-$3')

If you prefer using unix_timestamp/from_unixtime

select date(from_unixtime(unix_timestamp(ts, 'yyyyMMddHHmmss')))
from
(
select '20210412070422' as ts
)s

But it is more complex, slower (SimpleDateFormat class is involved) and error prone because will not work if data is not exactly in expected format, for example '202104120700'

Of course you can make it more reliable by taking substring of required length and using yyyyMMdd template:

select date(from_unixtime(unix_timestamp(substr(ts,1,8), 'yyyyMMdd')))
from
(
select '20210412070422' as ts
)s

It makes it even more complex.

Use unix_timestamp/from_unixtime only if simple substr or regexp_replace do not work for data format like '2021Apr12blabla'.

How to convert the date July 1, 2017 to dd-MM-yyyy using Hive SQL?

Use a combination of unix_timestamp and from_unixtime

select from_unixtime(unix_timestamp(week,'MMMM dd, yyyy'),'yyyy-MM-dd') from table_name;

unix_timestamp(string datetime, string pattern) converts datetime with given pattern to unix time stamp.

from_unixtime(bigint unixtime[, string format]) converts the number of seconds from unix epoch.

How to create hive table with date format 'dd-MMM-yyyy'?

Finally some help from @leftjoin, i solved the problem of converting string date with format (dd-MMM-yyyy) to (dd-MM-yyyy) by using select query. It would work fine.

select from_unixtime(unix_timestamp(columnname ,'dd-MMM-yyyy'), 'dd-MM-yyyy') from tablename;

---

Related Topics