Add interval to date does not work anymore

CrateDB SQL
1

Hi,

After upgrading from 5.1.x to 5.2.4 adding an interval to a date does not work anymore. I really think this has been working with 5.1.x.

UnsupportedFeatureException[Unknown function: (curdate() + (e.ts - date_trunc(‘day’, e.ts))), no overload found for matching argument types: (date, interval).

e.ts is a timestamp with timezone.

The full query…

SELECT to_char(e.ts, 'DD.MM.YYYY') as day, 
  CURRENT_DATE + (e.ts - date_trunc('day', e.ts)) AS time, 
  COUNT(e.value) OVER (PARTITION BY DATE_TRUNC('day', e.ts) ORDER BY e.ts) AS acc
FROM events AS e
WHERE e.ts > current_date - 20  * 1000*60*60*24
AND e.tag = 'Gas.Signal'
AND e.value = 0
2

sorry, I was wrong, it also does not work with 5.1.x in that way.

solution is to convert the date to a timestamp: CURRENT_DATE::TIMESTAMP.

SELECT to_char(e.ts, 'DD.MM.YYYY') as day, current_date::timestamp + (e.ts - date_trunc('day', e.ts)) AS time, COUNT(e.value) OVER (PARTITION BY DATE_TRUNC('day', e.ts) ORDER BY e.ts) AS acc
FROM events AS e
WHERE e.ts > current_date::timestamp - interval '20 days'
AND e.tag = 'Gas.Signal'
AND e.value = 0

seems I have copied the query from PostgresSQL…

3

There was a change in 5.2 related to the subtraction of timestamps

In 5.1 and earlier timestamp - timestamp also returned a timestamp
From 5.2 timestamp - timestamp returns an interval (following PostgreSQL)

So some behaviours might change.