英文:
snowflake string column vs date column comparison
问题
-
第一个查询返回正确结果，它比较日期列（eff_bgn_dt）与字符串，我认为其他数据库不允许字符串与日期列进行比较，应该是字符串与字符串或日期与日期之间的比较，那么在Snowflake中进行这种比较会发生什么情况？是比较哈希值吗？
-
第二个查询是将字符串与日期列（eff_bgn_dt）进行比较，然后按日期列进行分组，并统计每个分组的记录数。
英文:
I have 2 queries below , both return results , the first one returns correct results, the second query compares string against date column(eff_bgn_dt), I think other databases wont allow string to date comparison, either should be string vs string or date vs date, what exactly happens with such comparison in snowflake?, compares hash value ?
1.select count(*)
from TableA
where TO_DATE('20230605', 'YYYYMMDD') >eff_bgn_dt ;
2.select eff_bgn_dt,count(*)
from TableA
where '20230605' >eff_bgn_dt group by eff_bgn_dt;
答案1
得分: 0
I think other databases won't allow string to date comparison,
Depending on RDBMS they allow such comparison by performing "implicit conversion" - 数据类型优先级
Snowflake 隐式转换（"强制"）:
> 
警告 - 这种隐式转换可能会有点误导，因为值 20230605 不被视为 'YYYYMMDD':
select '20230605'::DATE;
-- 1970-08-23
select '2023-06-05'::DATE;
-- 2023-06-05
日期
>
>如果输入参数的格式是包含整数的字符串:
>
> - 将字符串转换为整数后，该整数将被视为从Unix纪元（1970-01-01 00:00:00.000000000 UTC）开始的秒数、毫秒数、微秒数或纳秒数。
实际查询:
select eff_bgn_dt,count(*)
from TableA
where '20230605' > eff_bgn_dt
group by eff_bgn_dt;
<=>
select eff_bgn_dt,count(*)
from TableA
where '20230605'::DATE > eff_bgn_dt
group by eff_bgn_dt;
<=>
select eff_bgn_dt,count(*)
from TableA
where TO_DATE('1970-08-23', 'YYYY-MM-DD') > eff_bgn_dt
group by eff_bgn_dt;
英文:
> I think other databases wont allow string to date comparison,
Depending on RDBMS they allow such comparison by performing "implicit conversion" - Data type precedence
Snowflake Implicit Casting (“Coercion”):
>
Warning - such implicit conversion may be a bit misleading because value 20230605 is not treated as 'YYYYMMDD':
select '20230605'::DATE;
-- 1970-08-23
select '2023-06-05'::DATE;
-- 2023-06-05
DATE
>
>If the format of the input parameter is a string that contains an integer:
>
> - After the string is converted to an integer, the integer is treated as a number of seconds, milliseconds, microseconds, or nanoseconds after the start of the Unix epoch (1970-01-01 00:00:00.000000000 UTC).
The actual query:
select eff_bgn_dt,count(*)
from TableA
where '20230605' > eff_bgn_dt
group by eff_bgn_dt;
<=>
select eff_bgn_dt,count(*)
from TableA
where '20230605'::DATE > eff_bgn_dt
group by eff_bgn_dt;
<=>
select eff_bgn_dt,count(*)
from TableA
where TO_DATE('1970-08-23', 'YYYY-MM-DD') > eff_bgn_dt
group by eff_bgn_dt;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论