Hive How to convert date format

This article mainly introduces Hive How to convert the date format , The content is detailed and easy to understand , The operation is simple and fast , It has certain reference value , I believe that after reading this article Hive How to convert the date format to the article will be fruitful , Let's have a look .

background

Date calculation is often involved in business data retrieval , But there are often different date formats stored in the database , Some store time stamps 、 Some are strings and so on , At this time, it needs to be transformed to extract accurate data , What is introduced here is hive The functions inside , The following contents are frequently used by the data requirements of the business

Time stamp

unix The time stamp is from 1970 year 1 month 1 Japan （UTC/GMT At midnight ） The number of seconds to start , Leap seconds not considered , It's usually 10 An integer

Time stamp

String date Such as ：'2021-10-21 19:25:50','2021-10-21 20:25:50.0','2021-10-21 20:25'

Date format conversion

Time stamp ---> Normal date format

• Get the current timestamp

select unix_timestamp()

• Change the timestamp to the normal date

select from_unixtime(unix_timestamp(),'yyyy-MM-dd hh:mm:ss') as dt

• Sometimes an integer containing milliseconds is stored in the business , It needs to be converted to seconds first

select from_unixtime(cast(create_time/1000 as bigint),'yyyyMMdd') as dt

String date If the database is stored in the format of ："yyyy-MM-dd hh:mm:ss"

• Intercept the date part

select substr('2021-10-22 17:34:56',1,10)2021-10-22

• String cast , Get date

select to_date('2021-10-22 17:34:56')2021-10-22

• It can also be done through date_format Realization

select date_format('2021-10-22 17:34:56','yyyy-MM-dd')2021-10-22

Current system date

• The current date

select current_date();2021-10-22

• Compare the string date with the current system date , This is often useful in business

select substr('2021-10-22 17:34:56',1,10)>current_date()false

The day before / Yesterday,

select date_sub(current_date(),1);2021-10-21

The day before 12 spot / Yesterday, 12 spot It is used to compare with the intercepted string date in the business

select concat(date_format(date_sub(current_date(),1),'yyyy-MM-dd'),' ','12');2021-10-21 12

Last month /30 God

select date_sub(current_date(),30);2021-09-22

The first day of the month It is often used in business to calculate the daily performance data of the current month

select date_format(to_date(trunc(current_date(),'MM')),"yyyy-MM-dd");2021-10-01

Date format conversion yyyyMMdd--->yyyy-MM-dd

select from_unixtime(unix_timestamp('20211022','yyyyMMdd'),"yyyy-MM-dd");2021-10-22

Days between two dates

select datediff('2021-10-22', '2021-10-01');21

About “Hive How to convert date format ” That's all for this article , Thank you for reading ！ I'm sure you're right “Hive How to convert date format ” Knowledge has a certain understanding , If you want to learn more , Welcome to the Yisu cloud industry information channel .