How to convert hive date format

Yisu cloud 2022-06-23 18:12:32 阅读数:198


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 .


    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

    Hive How to convert date format

    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 .

    版权声明:本文为[Yisu cloud]所创,转载请带上原文链接,感谢。