配置单元日期格式匹配

ljo96ir5 于 2021-05-29 发布在 Hadoop

关注(0)|答案(1)|浏览(449)

如何在配置单元查询中匹配特定的日期格式，因为我必须获得那些具有除最大行数以外的日期格式的行。
例如，我的最大行数的日期格式为mm/dd/yyyy，我必须列出除上述格式以外的所有行

+----------------------------+--------------------------+--------------------+-----------+
| AllocationActBankAccountID | GiftCardActBankAccountID | UpdateTimeStampUtc |   Date    |
+----------------------------+--------------------------+--------------------+-----------+
|                         14 |                       14 | 41:39.8            | 4/19/2016 |
|                         14 |                       14 | 37:16.4            | 4/20/2016 |
|                         14 |                       14 | 52:15.2            | 4/21/2016 |
|                         14 |                       14 | 52:15.2            | 2/11/2019 |
|                         14 |                       14 | 52:15.2            | 12-Feb-19 |*
|                         14 |                       14 | 41:39.8            | 2/13/2019 |
+----------------------------+--------------------------+--------------------+-----------+

我想得到*标记的数据（日期=19年2月12日）

hadoop Hive apache-hive

来源：https://stackoverflow.com/questions/43448432/hive-date-format-matching

1条答案

按热度按时间

ltqd579y1#

select  *
from    mytable
Where   date not rlike '^([1-9]|1[0-2])/([1-9]|[1-2][0-9]|3[0-1])/(19|20)\\d{2}$'

或

select  *
from    mytable
Where   not
        (
            date rlike '^\\d{1,2}/\\d{1,2}/\\d{4}$'
        and cast(split (date,'/')[0] as int) between 1 and 12
        and cast(split (date,'/')[1] as int) between 1 and 31 
        and cast(split (date,'/')[2] as int) between 1900 and 2099 
        )

或

select  date
from    mytable
Where   coalesce(from_unixtime(to_unix_timestamp(date,'M/d/y')),'0000-01-01') 
            not between date '1900-01-01' and date '2099-12-31'

赞(0）回复(0）举报 2021-05-29

我来回答

配置单元日期格式匹配

1条答案

相关问题

热门标签

最新问答