DATETIMECONVERT function not support pass time offset as parameter, e.g. tz(-09:00)
- 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
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