apache / apache/pinot

DATETIMECONVERT function not support pass time offset as parameter, e.g. tz(-09:00)

Open
#10,457 4 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
6.1k
Forks
1.5k
Avg merge
1d 21h
Merged PRs (30d)
189

Description

When querying the following psql, it comes out two different results:

```
select
cst,
cstdays,
cstms,
DATETIMECONVERT(cstdays, '1:DAYS:EPOCH', '1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(America/Adak)', '1:DAYS') as dateconv1,
DATETIMECONVERT(cstdays, '1:DAYS:EPOCH', '1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(-09:00)', '1:DAYS') as dateconv2
from t1
where cstms >= fromDateTime('2023-02-20 09:00:00.000', 'yyyy-MM-dd HH:mm:ss.SSS')
AND cstms <= fromDateTime('2023-03-22 08:59:59.000', 'yyyy-MM-dd HH:mm:ss.SSS')
ORDER BY cst DESC
limit 1000
```

**Data Format Example:**
cst: 2023-03-22T05:50:03.437Z
cstdays: 19438
cstms: 1679464203437

**Query Result:**

cst | cstdays | cstms | dateconv1 | dateconv2
2023-03-22T05:50:03.437Z | 19438 | 1679464203437 | 2023-03-21 | 2023-03-22
2023-03-22T05:50:03.408Z | 19438 | 1679464203408 | 2023-03-21 | 2023-03-22

My question is, "America/Adak" has the same meaning with "-09:00", but in the DATETIMECONVERT function, it returned two different results. So can you please help point out how to use time offset with this function? Thanks in advance.

Contributor guide

Open the contributing guide

Research direction

Reproduce the DATETIMECONVERT query and compare the America/Adak and -09:00 results using the data shown in the issue. Start by tracing DATETIMECONVERT's timezone and offset handling, then determine whether the differing dates are expected or indicate a bug; done means the behavior is explained or corrected with consistent offset handling.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.