Static date and timestamp in where clause
In this post we’ll look at the static date and timestamp in where clause when it comes to Presto. Many databases automatically convert between CHAR or VARCHAR and other types like DATE and TIMESTAMP as a convenience feature. Using constants in a query are also often auto-converted. Take these example queries which count rows where the table’s DATE column is equal to a specific date, or range of times:
Select count(*) from myTable where myDateCol = '2020-09-28'; Select count(*) from myTable where myTimeCol between '2020-09-28 14:00:00.000' and '2020-09-28 14:59:59.000';
The underlying table has columns defined as DATE and TIMESTAMP types. But the query is actually providing date and timestamp literals in the form of strings! Some databases will tolerate this, but Presto is stricter in this respect and will give you an error like this:
presto:sf1 > select count(*) from orders where orderdate < '2020-09-01'; Query failed: line 1:45: '<' cannot be applied to date, varchar(10)
With Presto you must either cast your data types or a slightly simpler way is to use the date or timestamp type constructors:
$ presto --schema sf1 --catalog tpch presto:sf1> select count(*) from orders where orderdate < date '2020-09-01'; _col0 --------- 1500000 (1 row) > select count(*) from transactions where myTimestampCol between timestamp '2020-09-01 22:00:00.000' and timestamp '2020-09-01 22:59:59.000'; _col0 --------- 42 (1 row) --Using CAST() also works: presto:sf1> select count(*) from orders where orderdate < CAST('2020-09-01' as DATE); _col0 --------- 1500000 (1 row)
In summary, by using CAST() or the DATE/TIMESTAMP type constructors you will be able to specify a static date and timestamp in where clause.