背景

hive/mysql下使用sql中的between * and * 在不同數(shù)據(jù)類(lèi)型下竟然不一樣。
1、int類(lèi)型、float類(lèi)型、string類(lèi)型是包含頭尾的。
2、timestamp包含頭，但不包含尾?。?！此外，當(dāng)between a and a 時(shí)， 是不包含a的。
具體原因，有待進(jìn)一步調(diào)研。

避坑辦法

使用大于等于和小于等于替代between * and * ，避免這個(gè)問(wèn)題。

驗(yàn)證過(guò)程

1、int、float類(lèi)型數(shù)據(jù)——包含頭尾

select
    7 as time
    , case when 7  between 7 and 8 then "1" else "0" end as judge
union all
select
    8 as time
    , case when 8  between 7 and 8 then "1" else "0" end as judge

圖片.png

select
    7.0  as time
    , case when 7.0   between 7.0  and 8.0  then "1" else "0" end as judge
union all
select
    8.0  as time
    , case when 8.0   between 7.0  and 8.0  then "1" else "0" end as judge

圖片.png

2、string類(lèi)型——包含頭尾

select
    to_date(now())  as time
    , case when to_date(now())              between "2020-11-11" and "2020-11-12" then "1" else "0" end as judge
union all
select
    to_date(date_add(now(),1)) as time
    , case when to_date(date_add(now(),1))  between "2020-11-11" and "2020-11-12" then "1" else "0" end as judge

圖片.png

3、timestamp類(lèi)型——包含頭，但不包含結(jié)尾

select
    now() as time
    , case when now()               between "2020-11-11" and "2020-11-12" then "1" else "0" end as judge
union all
select
    date_add(now(),1) as time
    , case when date_add(now(),1)  between "2020-11-11" and "2020-11-12" then "1" else "0" end as judge

圖片.png