Hi RamKrishna,
As you said:
> So, basically I need to add 2 Hours to CurrentTime.
I think the generated function call "TIMESTAMPADD(HOUR, 2,
CURRENT_TIMESTAMP)" already did that.
If what you want is to compare it's value with a MySQL's Date (with
format xxxx-xx-xx), based on Christopher's suggestion I think you can
write:
```
builder.call(SqlStdOperatorTable.FLOOR, ImmutableList.of(timeAdd,
builder.getRexBuilder().makeFlag(TimeUnitRange.DAY)))
```
Calcite will generate MySQL-dialect SQL like
"DATE_FORMAT(TIMESTAMPADD(HOUR, 2, CURRENT_TIMESTAMP), '%Y-%m-%d')"
which produces String result with Date precision. Or you can simply use
operator CAST:
```
builder.cast(timeAdd, SqlTypeName.DATE))
```
to extract Date part from the variable "timeAdd". Both of the two ways
should give you result that is comparable with coulmn "HIRE_TIME".
Best,
Hongze
------ Original Message ------
From: "Rakesh Nair" <ramkrs...@gmail.com>
To: dev@calcite.apache.org
Sent: 2019/3/8 16:34:47
Subject: Re: Calcite-RelNode clarifications
Hello Mr. Baynes,
Thanks for responding.
1. Luckily I was able to solve it on my own, thanks anyway. Moving on..
2. Let me explain my scenario in detail, you see we have an application
that queries multiple dbs like Mysql, Oracle, etc..with some static queries
which we are maintaining in their respective dialects for all these dbs.
Now we're trying to maintain a Generic RelNode class for different queries
and then convert it to respective Db dialect as and when required. For eg:
If I need to run a *select * from `emp`* query in Mysql. I will create a
relNode like so,
*RelNode node = builder.scan("emp").build();* and then convert it to its
corresponding Mysql query using,
*SqlDialect dialect = SqlDialect.DatabaseProduct.MYSQL.getDialect();*
*RelToSqlConverter converter = new RelToSqlConverter(dialect);*
*SqlNode sqlNode = converter.visitChild(0, node).asStatement();*
*String query = sqlNode.toSqlString(dialect).getSql();*
If I need to convert the same query to its Oracle counterpart i just need
to initialise dialect as Oracle dialect. Now for most of the queries, I've
been able to create its corresponding RelNode. But in some queries I'm
getting stuck which I've mentioned as below:
Now I have another MySql query like so,
*SELECT * FROM `emp` WHERE HIRE_TIME = DATE_ADD( NOW(), INTERVAL 2 HOUR);*
Now I woud like to create the corresponding RelNode for this query. So far
this is what I've been able to do:
*builder.scan("emp");*
*ImmutableList<RexNode> constExps =
ImmutableList.of(builder.getRexBuilder().makeFlag(TimeUnitRange.HOUR),
builder.literal(2), builder.call(SqlStdOperatorTable.CURRENT_TIMESTAMP));*
*RelDataType any = builder.getTypeFactory().createSqlType(SqlTypeName.ANY);*
*RexNode timeAdd = builder.getRexBuilder().makeCall(any,
SqlStdOperatorTable.TIMESTAMP_ADD, constExps);*
*RelNode node = builder.filter(builder.equals(builder.field("HIRE_TIME"),
timeAdd)).build();*
And the corresponding MySql is coming out to be like this:
*SELECT * FROM `db`.`emp` WHERE `HIRE_TIME` = TIMESTAMPADD(HOUR, 2,
CURRENT_TIMESTAMP) *which is clearly not correct.
So, basically I need to add 2 Hours to CurrentTime. Can you explain how to
accomplish this using RelBuilder(with a RelNode example if possible).
Thanks and Regards,
RamKrishna.
On Thu, Mar 7, 2019 at 9:34 PM Chris Baynes <ch...@contiamo.com> wrote:
1. Could you give an example of what your expected output/query is here?
I'm not sure what you're trying to achieve.
2. There are mappings in MysqlSqlDialect.java from Calcite FLOOR to Mysql
DATE_FORMAT. So what you want in your RelNode is something like
FLOOR(HIRE_DATE TO day)
On Tue, Mar 5, 2019 at 6:41 PM Rakesh Nair <ramkrs...@gmail.com> wrote:
> Hi,
> Sorry to be a bother, but I've been sitting on this for quite a while now
> and would really like to clear it. So I've been trying to use the
> RelBuilder for building relational expressions.
> 1. I'm trying to build the relational expressions for using Trim()
> function. This si what I've done so far:
> RelNode node =
> builder.scan("emp").project(builder.call(SqlStdOperatorTable.TRIM,
> builder.getRexBuilder().makeFlag(Flag.BOTH),builder.literal(" "),
> builder.field("EMPNAME"))).build();
> Query Explain:
> LogicalProject($f0=[TRIM(FLAG(BOTH), ' ', $2)])
> LogicalTableScan(table=[[hr, emp]])
> Corresponding Mysql Query:
> SELECT TRIM(BOTH ' ' FROM `EMPNAME`) AS `$f0`
> FROM `hr`.`emp`
> Could somebody tell me what I'm doing wrong here?
> 2. How can I convert the following Mysql Query:
> SELECT DATE_FORMAT(HIRE_DATE,'%Y/%m/%d'), EMPNAME FROM `emp` WHERE
> DATE_FORMAT(HIRE_DATE,'%Y/%m/%d') = DATE_FORMAT(NOW(),'%Y/%m/%d');
> to its corresponding Relational Algebra using RelBuilder? Simply put how
> can i convert the date formats in RelBuilder?
>
> Thanks and Regards,
> RamKrishna.
>
--
*Christopher Baynes*
Lead Developer
*Contiamo – all your data in one place*
Stresemannstraße 123 (c/o WeWork) | 10963 Berlin | Germany
E-mail: ch...@contiamo.com
Web: www.contiamo.com
<
http://t.sidekickopen65.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJW7t5XZs4X9YtjW8q-fZW65jv3RW2zhrDH56dLV8f5DKhvM02?t=http%3A%2F%2Fwww.contiamo.com%2F&si=5165279625740288&pi=bff9f6a3-d8a4-4bf6-87d5-a5464041547d
>
Contiamo GmbH, Sitz der Gesellschaft: Berlin
HR Berlin-Charlottenburg, HRB Nr. 156569
Geschäftsführer: Lucia Hegenbartova, Michael Franzkowiak
