Presto - where 子句中的静态日期和时间戳 [英] Presto - static date and timestamp in where clause

问题描述

我很确定以下查询曾经在 Presto 上对我有用:

I'm pretty sure the following query used to work for me on Presto:

select segment, sum(count)
from modeling_trends
where segment='2557172' and date = '2016-06-23' and count_time between '2016-06-23 14:00:00.000' and '2016-06-23 14:59:59.000';
group by 1;

现在当我运行它时(在 EMR 上的 Presto 0.147 上)，我收到一个错误，试图将 varchar 分配给日期/时间戳..

now when I run it (on Presto 0.147 on EMR) I get an error of trying to assigning varchar to date/timestamp..

我可以使用:

select segment, sum(count)
from modeling_trends
where segment='2557172' and date = cast('2016-06-23' as date) and count_time between cast('2016-06-23 14:00:00.000' as TIMESTAMP) and cast('2016-06-23 14:59:59.000' as TIMESTAMP)
group by segment;

但是感觉很脏...有没有更好的方法来做到这一点?

but it feels dirty... is there a better way to do this?

推荐答案

与其他一些数据库不同，Presto 不会在 varchar 和其他类型之间自动转换，即使对于常量也是如此.强制转换有效，但更简单的方法是使用类型构造函数:

Unlike some other databases, Presto doesn't automatically convert between varchar and other types, even for constants. The cast works, but a simpler way is to use the type constructors:

WHERE segment = '2557172'
  AND date = date '2016-06-23'
  AND count_time BETWEEN timestamp '2016-06-23 14:00:00.000' AND timestamp '2016-06-23 14:59:59.000'

您可以在此处查看各种类型的示例:https://prestosql.io/docs/current/language/types.html

You can see examples for various types here: https://prestosql.io/docs/current/language/types.html

这篇关于Presto - where 子句中的静态日期和时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！