DATEPART not applying in .TDD

[sablastik]

About You:
Name: Konstantin
Company: Citymobil

Your question:
I'm trying to customize dialect file and with usual functions like 'YEAR' it works, however when I try to modify date-functions like DATEPART it takes no effect. For example I'm trying to put datetime field to the Text section. Calculation looks like this: "DATEPART('year', [OrderedDate])" and DATEPART is underlined red with this error: "The function 'DATEPART' is not available on non-aggregates for this datasource".

Could you please help me to understand what am I doing wrong?

My TDD looks like this:

<?xml version="1.0" encoding="utf-8"?>
<dialect name='test_connector'
         class='test_connector'
         version='18.1'>


  <function-map>

    <function group='string' name='LEFT' return-type='str'>
      <formula>SUBSTR(%1, 1, %2)</formula>
      <argument type='str' />
      <argument type='real' />
    </function>

    <function group='string' name='SPLIT' return-type='str'>
      <formula>SplitByString(%2, %1)[%3]</formula>
      <argument type='str' />
      <argument type='localstr' />
      <argument type='localint' />
    </function>

    <function group='date' name='NOW' return-type='datetime'>
      <formula>NOW()</formula>
    </function>

    <function group='aggregate' name='MAX' return-type='datetime'>
      <formula>MAX(%1)</formula>
      <unagg-formula>%1</unagg-formula>
      <argument type='datetime' />
    </function>

    <function group='date' name='YEAR' return-type='int'>
      <formula>toYear(%1)</formula>
      <argument type='datetime' />
    </function>
    <function group='date' name='YEAR' return-type='int'>
      <formula>toYear(%1)</formula>
      <argument type='date' />
    </function>

    <function group='aggregate' name='MIN' return-type='int'>
      <formula>MIN(%1)</formula>
      <unagg-formula>%1</unagg-formula>
      <argument type='int' />
    </function>

    <date-function name='DATEPART' return-type='int'>  
      <formula part='year'>toYear(%2)</formula>  
      <argument type='localstr' />  
      <argument type='datetime' />  
      <argument type='localstr' />  
    </date-function>  
  </function-map>
  <supported-aggregations>
    <aggregation value='AGG_COUNT'/>
    <aggregation value='AGG_COUNTD'/>
    <aggregation value='AGG_SUM'/>
    <aggregation value='AGG_AVG'/>
    <aggregation value='AGG_MIN'/>
    <aggregation value='AGG_MAX'/>
    <aggregation value='AGG_STDEV'/>
    <aggregation value='AGG_STDEVP'/>
    <aggregation value='AGG_VAR'/>
    <aggregation value='AGG_VARP'/>
    <aggregation value='AGG_COVAR'/>
    <aggregation value='AGG_COVARP'/>
    <aggregation value='AGG_CORR'/>
    <aggregation value='AGG_MEDIAN'/>
    <aggregation value='AGG_YEAR'/>
    <aggregation value='AGG_QTR'/>
    <aggregation value='AGG_MONTH'/>
    <aggregation value='AGG_DAY'/>
    <aggregation value='AGG_WEEK'/>
    <aggregation value='AGG_WEEKDAY'/>
    <aggregation value='AGG_MONTHYEAR'/>
    <aggregation value='AGG_MDY'/>
    <aggregation value='AGG_HOUR'/>
    <aggregation value='AGG_MINUTE'/>
    <aggregation value='AGG_SECOND'/>
    <aggregation value='TRUNC_YEAR'/>
    <aggregation value='TRUNC_QTR'/>
    <aggregation value='TRUNC_MONTH'/>
    <aggregation value='TRUNC_DAY'/>
    <aggregation value='TRUNC_WEEK'/>
    <aggregation value='TRUNC_HOUR'/>
    <aggregation value='TRUNC_MINUTE'/>
    <aggregation value='TRUNC_SECOND'/>
  </supported-aggregations>

  <sql-format>
    <base-types>
      <local-type name='real' value='Float64' />
      <local-type name='int' value='UInt64' />
      <local-type name='str' value='String' />
      <local-type name='bool' value='UInt8' />
      <local-type name='date' value='Date' />
      <local-type name='datetime' value='DateTime' />
    </base-types>
  </sql-format>
</dialect>

[pvanderknyff]

Hi Konstantin,

It looks like your dialect does not have a base class. You either need to choose a valid base dialect from the list here, or implement every function of the dialect manually, like in our full dialect example.

[sablastik]

@pvanderknyff Thanks for reply! Yes I don't use base class. Could you please tell me the minimal set of functions from full dialect example to make DATEPART work as I need? I just need to understand how it should work)))

[sablastik]

I've tried to use full dialect changing only functions containing YEAR() without any effect =(

However I've found that DATEPART in Calculation field started to work, but if I drop datetime field to the Text pane it still shows an Error

[rosswbrown]

For DATEPART specifically it ends up getting defined twice in the TDD, one with 2 arguments and the other with 3. This is to accommodate a custom start of week (sunday vs monday for example).

Here is an example of DATEPART from the Postgres90Dialect:

2 argument DATEPART, common case:

    <date-function name='DATEPART' return-type='int'>
      <formula>CAST(TRUNC(EXTRACT(%1 FROM %2)) AS INTEGER)</formula>
      <formula part='weekday'>(1 + CAST(EXTRACT(DOW FROM %2) AS INTEGER))</formula>
      <formula part='week'>CAST(FLOOR((7 + EXTRACT(DOY FROM %2) - 1 + EXTRACT(DOW FROM DATE_TRUNC(&apos;YEAR&apos;, %2))) / 7) AS INTEGER)</formula>
      <argument type='localstr' />
      <argument type='datetime' />
    </date-function>

The string values of %1 above are defined by

      <date-part-group>
        <date-function name='DATEPART' />
        <part name='year' value='YEAR' />
        <part name='quarter' value='QUARTER' />
        <part name='month' value='MONTH' />
        <part name='week' value='WEEK' />
        <part name='weekday' value='DOW' />
        <part name='dayofyear' value='DOY' />
        <part name='day' value='DAY' />
        <part name='hour' value='HOUR' />
        <part name='minute' value='MINUTE' />
        <part name='second' value='SECOND' />
      </date-part-group>

3 argument DATEPART, used is custom start of week cases:

    <date-function name='DATEPART' return-type='int'>
      <formula part='week'>CAST(FLOOR((7 + EXTRACT(DOY FROM %2) - 1 + (CAST(7 + EXTRACT(DOW FROM DATE_TRUNC(&apos;YEAR&apos;, %2)) - %3 AS BIGINT) % 7)) / 7) AS INTEGER)</formula>
      <argument type='localstr' />
      <argument type='datetime' />
      <argument type='localstr' />
    </date-function>

Above it appears you only have the 3 argument version defined, which may be part of the issue.

With our next release there will be a full dialect (without base class) included with our postgres_odbc and postgres_jdbc examples to provide more concrete examples like this.

Thanks,
Ross