Presto - static date and timestamp in where clause

Question

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;

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

I can make it work using:

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?

David Phillips David Phillips · Accepted Answer · 2016-06-26T18:20:19

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'

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

Presto - static date and timestamp in where clause

2 Answers