Presto - static date and timestamp in where clause

presto
Tal Joffe picture Tal Joffe · Jun 26, 2016 · Viewed 64.3k times · Source

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?

Answer

David Phillips picture David Phillips · Jun 26, 2016

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

Related questions

Presto SQL - Converting a date string to date format

I'm on presto and have a date formatted as varchar that looks like - 7/14/2015 8:22:39 AM I've looked the presto docs and tried various things(cast, date_format, using split_part to parse and then cast) and am not getting this …

Read More
Presto check if NULL and return default (NVL analog)

Is there any analog of NVL in Presto DB? I need to check if a field is NULL and return a default value. I solve this somehow like this: SELECT CASE WHEN my_field is null THEN 0 ELSE my_field …

Read More
Athena greater than condition in date column

I have the following query that I am trying to run on Athena. SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date However it is producing this …

Read More