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

[Betternan]

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.

[Jackie-Jiang]

We are using the joda library to handle the time conversion, and per the joda doc, America/Adak is equivalent to -10:00. In your query, seems the expected result is 2023-03-21? is -09:00 a valid time zone?

[jpg-130]

But can we still offset time using hours, minutes, etc using any way in pinot?

[jpg-130]

I am answering my own question here, but I found the way using UDF with groovy script.

I have just subtracted the offset in milliseconds for 30 minutes from timestampEpoch, assuming timestampEpoch is a long value in epoch millisecond for my use case. You can handle any custom requirement that you want to in this manner.

DATETIMECONVERT(groovy('{"returnType":"LONG","isSingleValue":true}','arg0-1800000', timestampEpoch),'1:MILLISECONDS:EPOCH','1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss','1:HOURS')

[Jackie-Jiang]

@jpg-130 You don't really need to use groovy. DateTimeConvert(timestampEpoch - 1800000, '1:MILLISECONDS:EPOCH','1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss','1:HOURS') should work