[jira] [Commented] (CALCITE-1798) Jdbc dialect specific datetime floor operators

["Chris Baynes (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1798?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16018852#comment-16018852 ] 

Chris Baynes commented on CALCITE-1798:
---------------------------------------

I've made a first pass at this: [https://github.com/apache/calcite/pull/453]
Have a couple of things I'd like to improve & get feedback on though:

1. The convertToHsqlDb method isn't at all specific to flooring, it could also be used in other timestamp functions. Where would be a good place to create hsql dialect specifics?

2. Would like to write some dialect specific tests to check the generated sql output, perhaps a JdbcDialectTest class. Is this possible without having the actual db available?

I'll fill out the switch with the other dialects as soon as there's agreement that this looks like it's going in the right direction.

> Jdbc dialect specific datetime floor operators
> ----------------------------------------------
>
>                 Key: CALCITE-1798
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1798
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Chris Baynes
>            Assignee: Julian Hyde
>
> The FLOOR operator (on dates) is currently broken for all jdbc dialects.
> The syntax allowed by the parser looks like: "FLOOR(datetime to timeUnit)".
> However no jdbc dialect (as far as I'm aware) actually name the function FLOOR:
> In postgres: DATE_TRUNC('year', my_datetime)
> In hsqldb: TRUNC ( my_datetime, 'YYYY' )
> In oracle: TRUNC(my_datetime, 'YEAR')
> In mysql: There's no direct equivalent in mysql (though it could be emulated with some nasty timestamp diffing)
> The other issue is that the timeUnits are sometimes also named differently by each dialect (e.g. 'YYYY' in hsqldb).



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)