[SEARCH-2304] Support for CAST AS TIMESTAMP function for Date and Datetime fields Created: 16-Jun-20  Updated: 24-Aug-20  Resolved: 29-Jul-20

Status: Done
Project: Search and Discovery
Component/s: None
Affects Version/s: Search Services 1.4
Fix Version/s: Search Services 2.0

Type: Story
Reporter: Angel Borroy Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Dependency
Depends on SEARCH-2255 SQL avg(field) function is broken wit... Done
Depended on by SEARCH-2316 Error is produced in Tableau when try... Review
Related
relates to SEARCH-2296 Support for QUARTER function Done
Epic Link: BI Tool integration via CData - Must Have
Sprint: Team Ninja-King - S&I 38, Team Ninja-King - S&I 39, Team Ninja-King - S&I 40
Release Train: Southall
Delivery Team: Search
Story Points: 13

 Description   

When using Tableau, date fields are drilled down using CAST functions when gathering hour, minutes and seconds. This produces queries including the following pattern:

SELECT HOUR(CAST(`cm_created` AS TIMESTAMP))
FROM alfresco
GROUP BY HOUR(CAST(`cm_created` AS TIMESTAMP))

We can simplify the case testing following query:

SELECT CAST(`cm_created` AS TIMESTAMP)
FROM alfresco

That produces this exception when executed:

java.lang.ClassCastException: class java.lang.String cannot be cast to class java.lang.Number

Acceptance criteria:

  • SELECT CAST in SELECT, WHERE and GROUP BY
  • Support CAST as an inner operator


 Comments   
Comment by Angel Borroy [ 22-Jun-20 ]

Andrea Gazzarini that approach only can be applied to "SELECT" clause. What about "WHERE", "GROUP BY" and so on?

Comment by Alessandro Benedetti [ 23-Jul-20 ]

Bamboo build running, as soon as it is green, I open the Pull Request

Comment by Angel Borroy [ 23-Jul-20 ]

Added my review:

  • General issues with formatting
  • Some specific comments related to the scope of the implementation
Generated at Sun Mar 07 00:33:49 GMT 2021 using Jira 7.13.15#713015-sha1:7c5ddd2c3e1709974ae9c48c17df8edd3919fe2c.