How to shift dates timestamps in Pinot There is no DATE ADD Apache Pinot #general

How to shift dates/timestamps in Pinot? There is n...

Jan Soubusta

03/13/2024, 8:19 AM

How to shift dates/timestamps in Pinot? There is no DATE_ADD function, it is not possible to do something like this:

date_col + interval '1 month'

. I searched the DOC really thoroughly but cannot find anything relevant...

Jan Soubusta

03/13/2024, 1:28 PM

Interesting, this syntax is valid, but the result is the same like the original value, adding the interval does not do anything 😉

Copy code

select cast('2024-01-01' as timestamp) + INTERVAL '1' YEAR;

Jan Soubusta

03/13/2024, 1:29 PM

When I search for "INTERVAL" in the DOC, there is nothing

Jan Soubusta

03/13/2024, 1:44 PM

I went through all datetime functions, there is no function for shifting datetimes. If it is really missing completely, is there a chance to introduce it in mid-term future?

robert zych

03/13/2024, 5:05 PM

one option is to use FromDateTime to convert a string date into a long

Mayank

03/13/2024, 5:15 PM

Looking at the code I see

timestamp_add

function. Perhaps you can file a GH enhancement request for

date_add

Copy code

@ScalarFunction(names = {"timestampAdd", "timestamp_add", "dateAdd", "date_add"})
  public static long timestampAdd(String unit, long interval, long timestamp) {
    ISOChronology chronology = ISOChronology.getInstanceUTC();
    long millis = DateTimeUtils.getTimestampField(chronology, unit).add(timestamp, interval);
    return millis;
  }

Jan Soubusta

03/13/2024, 5:51 PM

Does not work for me:

Copy code

select id, 
       created_at_timestamp
       , cast(created_at_timestamp AS double) AS timeInMs
	   , timestampAdd('WEEK', 1, created_at_timestamp) as shiftedDate
from githubEvents
WHERE id = 7044874134

Fails with:

Copy code

SQLParsingError:
org.apache.pinot.sql.parsers.SqlCompilationException: Caught exception while parsing query: select id, 
       created_at_timestamp
       , cast(created_at_timestamp AS double) AS timeInMs
	   , timestampAdd('WEEK', 1, created_at_timestamp) as shiftedDate
from githubEvents
...
Caused by: org.apache.pinot.sql.parsers.parser.ParseException: Encountered "" at line 4, column 20.
	at org.apache.pinot.sql.parsers.parser.SqlParserImpl.generateParseException(SqlParserImpl.java:38633)
	at org.apache.pinot.sql.parsers.parser.SqlParserImpl.jj_consume_token(SqlParserImpl.java:38430)
	at org.apache.pinot.sql.parsers.parser.SqlParserImpl.TimestampInterval(SqlParserImpl.java:5196)
	at org.apache.pinot.sql.parsers.parser.SqlParserImpl.TimestampAddFunctionCall(SqlParserImpl.java:7001)

Jan Soubusta

03/13/2024, 5:53 PM

Eh, this works:

Copy code

select id, 
       created_at_timestamp
       , cast(created_at_timestamp AS double) AS timeInMs
	   , timestampAdd(WEEK, 1, created_at_timestamp) as shiftedDate
from githubEvents
WHERE id = 7044874134

Definitely it is not the first time I face to this issue. Why DB developers have to require sometimes

'WEEK'

and sometimes

WEEK

? 😉

👍 1

Jan Soubusta

03/13/2024, 5:54 PM

OK, thanks a lot, now I can continue developing more advanced date arithmetic 🎉

🙏 1

robert zych

03/13/2024, 6:08 PM

In your schema is the

created_at_timestamp

using the timestamp data type?

Jan Soubusta

03/13/2024, 6:39 PM

Yes, it is

Jan Soubusta

03/13/2024, 6:39 PM

Question: this works:

Copy code

select FROMDATETIME('2024', 'yyyy') as datetime;

This fails:

Copy code

select TIMESTAMPADD(YEAR, 1, FROMDATETIME('2024', 'yyyy'));

FROMDATETIME produces TIMESTAMP data type. What am I doing wrong?

Jan Soubusta

03/13/2024, 6:52 PM

Copy code

Caused by: org.apache.pinot.sql.parsers.SqlCompilationException: Caught exception while invoking method: public static long org.apache.pinot.common.function.scalar.DateTimeFunctions.timestampAdd(java.lang.String,long,long) with arguments: [YEAR, 1, 1704067200000]
	at org.apache.calcite.rel.rules.PinotEvaluateLiteralRule.evaluateLiteralOnlyFunction(PinotEvaluateLiteralRule.java:164)
	at org.apache.calcite.rel.rules.PinotEvaluateLiteralRule$EvaluateLiteralShuttle.visitCall(PinotEvaluateLiteralRule.java:131)
	at org.apache.calcite.rel.rules.PinotEvaluateLiteralRule$EvaluateLiteralShuttle.visitCall(PinotEvaluateLiteralRule.java:119)

Caused by: java.lang.IllegalArgumentException: Cannot convert value from class: class org.apache.calcite.avatica.util.TimeUnit to class: class java.lang.String
	at org.apache.pinot.shaded.com.google.common.base.Preconditions.checkArgument(Preconditions.java:445)
	at org.apache.pinot.common.function.FunctionInvoker.convertTypes(FunctionInvoker.java:113)
	at org.apache.calcite.rel.rules.PinotEvaluateLiteralRule.evaluateLiteralOnlyFunction(PinotEvaluateLiteralRule.java:160)

Jan Soubusta

03/13/2024, 8:05 PM

Our use case for context: datetime filters. Users pick e.g. last year. We calculate that it means ts >= 2024 and ts < 2025. To pass such filter to database, we need to convert the literal 2024 to datetime. We pass the value 2025 as 2024 + 1 year. That why we need the above syntax.

👍 1

Jan Soubusta

03/14/2024, 12:26 PM

So, any advice, please? I am blocked now...

robert zych

03/14/2024, 1:50 PM

Could you provide an example of the final query you need that reproduces the issue you found with

select TIMESTAMPADD(YEAR, 1, FROMDATETIME('2024', 'yyyy'));

Jan Soubusta

03/14/2024, 4:55 PM

Copy code

SELECT TODATETIME("a_label_ts_day_26d123c51b6740e1bacfd7c7e21d3928_28c4668072285e",
                  'yyyy-MM-dd')             AS "a_label_ts_day_26d123c51b6740e1bacfd7c7e21d3928_08783fee3a5f3d",
       "m_f17916e2335060e6b888d17749084758" AS "m_1"
FROM (SELECT DATETRUNC('DAY', "ts")                   AS "a_label_ts_day_26d123c51b6740e1bacfd7c7e21d3928_28c4668072285e",
             SUM(CAST("ActualElapsedTime" AS DOUBLE)) AS "m_f17916e2335060e6b888d17749084758",
             TRUE                                     AS "def_m_f17916e2335060e6b888d17749084758"
      FROM "airlineStats"
      WHERE "ts" >= FROMDATETIME('2024', 'yyyy')
        AND "ts" < TIMESTAMPADD(YEAR, 1, FROMDATETIME('2024', 'yyyy'))
      GROUP BY DATETRUNC('DAY', "ts")) AS "t2"
WHERE "def_m_f17916e2335060e6b888d17749084758"

Jan Soubusta

03/15/2024, 5:02 PM

So, does a solution exist or not? Simple NO is OK, I can workaround it on my side, I just need to know if I should invest into it, because it won't be trivial...

👀 1

robert zych

03/15/2024, 8:33 PM

I’m working to reproduce your query in my environment and found that replacing TIMESTAMPADD with DATEADD and wrapping YEAR in single quotes works. I’ll get back to as I learn more about the rest of your query

Jan Soubusta

03/15/2024, 8:40 PM

The rest is not important, IMO

Jan Soubusta

03/15/2024, 8:40 PM

Let me test your proposal

Jan Soubusta

03/15/2024, 8:44 PM

Works! I have dejavu...final syntax is getting very close to Apache Drill, which I already integrated ;-)

👍 1

robert zych

03/15/2024, 9:35 PM

It appears that aliasing the expression in the GROUP BY clause isn’t supported. But you can reuse the alias from the SELECT clause in your GROUP BY.

16 Views

Open in Slack

Previous Next