cube-js / cube-js/cube

PG_SQL - Internal error: Unsupported CAST from Interval(DayTime) to Timestamp(Nanosecond, None) / Data type Interval(DayTime) not supported for binary operation 'multiply' on primitive arrays

Open
#6,332 0 comments 0 reactions 1 assignee Claimed by @ovr View on GitHub
api:sql
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
Cube PG SQL unable to add interval for datetime

**To Reproduce**
Steps to reproduce the behavior:
1. DBeaver connect Cube postgres SQL
2. run below query works fine
`select * from (
select cast('2023-03-03 00:00:00' as timestamp) - interval '1 day' startdate
) t`
Query Result:
![image](https://user-images.githubusercontent.com/38604290/226868728-2f350fbd-d2f1-4827-87c2-17498aab6d9f.png)

3. run below query cause error
`select *, startdate - interval '1 day' from (
select cast('2023-03-03 00:00:00' as timestamp) startdate
) t`
Query Result:
![image](https://user-images.githubusercontent.com/38604290/226868962-8b54c406-46da-4152-9e69-7c177e16cb78.png)

4. run below query works fine
`select cast('2023-03-22 00:00:00' as timestamp) + (interval '1 day') * 19`
Query Result:
![image](https://user-images.githubusercontent.com/38604290/226871877-98fe4769-37cb-40b3-9881-4d63c0e43879.png)

5. run below query cause error
`select cast('2023-03-22 00:00:00' as timestamp) + (interval '1 day') * datediff(day, cast('2023-03-03 00:00:00' as timestamp), cast('2023-03-22 00:00:00' as timestamp)) `
Query Result:
![image](https://user-images.githubusercontent.com/38604290/226872216-034fd5cf-812a-4dd2-af4a-30b385b5ceef.png)

**Expected behavior**
Should not cause errors and able to query

**Screenshots**
![image](https://user-images.githubusercontent.com/38604290/226868044-794fecce-b741-4393-b17d-4408ef7087ff.png)

![image](https://user-images.githubusercontent.com/38604290/226872374-97c4c70a-2132-4113-9cbc-742f3b9ef5dd.png)

**Version:**
v0.32.9

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.