58

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
  • 10,723
  • 6
  • 41
  • 54
Tal Joffe
  • 5,347
  • 4
  • 25
  • 31

2 Answers2

114

Unlike some other databases, Trino 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://trino.io/docs/current/language/types.html

David Phillips
  • 10,723
  • 6
  • 41
  • 54
  • 2
    Thank you, adding the keyword `date` upfront the string date worked. My query `SELECT * FROM db.table_1 WHERE date_col > date '2018-01-01' LIMIT 100` – Ricardo Mutti Oct 20 '18 at 21:52
  • Hi - I have a column of type `date`, yet when I run ```SELECT COUNT(*) FROM where dt >= DATE '2019-01-01'``` I see `Mismatched Domain types: date vs integer`; are there other workarounds that can help here?
     – Scott Borden Mar 02 '19 at 00:29
-9

Just a quick thought.. have you tried omitting the dashes in your date? try 20160623 instead of 2016-06-23.

I encountered something similar with SQL server, but not used Presto.

PGC
  • 1
  • 3