Visual SQL - Date selection code different depending on operators

Jason Lunghusen

Not sure if this would be considered a bug, thought I'd post it as a question first. I noticed the below when trying to count the number of issues created after a set date, and am wondering if I'm misunderstanding what VisualSQL is doing.

When using Visual SQL to filter a query, using "created later than date" would include Issues created on that date.  It appears as though the SQL being built is saying > date on a datetime field, which means you get all issues created after midnight on that date.  The inserted picture below hopefully clearly demonstrates what I'm talking about.

At the moment, I'm manually working around this by doing the following, similar to what Visual SQL does if you select the "is date" option:

WHERE ((`Issue`.`created_at` > '2023-07-31'))

to

WHERE ((DATE(`Issue`.`created_at`) > '2023-07-31'))

Manually changing it does mean that I have to stop using Visual SQL for the query completely, because if I go back to using it then it will override my manual workaround.  I've also noticed that if I use the "between and including" options then I get different SQL code again:

WHERE ((`Issue`.`created_at` >= TIMESTAMP('2023-07-31')
AND `Issue`.`created_at` < (TIMESTAMP('2023-07-31') + INTERVAL 1 DAY)))

It'd be great if there could be an update to make what code is generated more consistent, making the results more reliable.   The options do say "later than date" then provide a datepicker, not "later than datetime" and then offer a date & time picker like ServiceNow does.  Unless I'm misunderstanding something?

Skyler Ataide

Hi Jason, 

 

Your understanding of the SQL code generated here by Visual SQL mode is correct. Since the "Created at" column is a Datetime data type, using the 'Later than date' operator will generate SQL that includes values from the "Later than" date selected, or as you describe in this example, later than 2023-07-31 00:00:00. The SQL mode workaround that you explain, WHERE ((DATE(`Issue`.`created_at`) > '2023-07-31')), would have the Visual SQL mode equivalent of selecting a visual mode filter of "Later than 2023-08-01". 

 

You'll notice, however, that for Date data type columns this is not the case. Take the "Due date" column from the Jira Issue table for example, which is a Date data type and not a Datetime data type. Using the 'Later than date' Visual SQL filter operator in this case will exclude values from the date selected in the filter. See the screenshots below for an example: 

 

From my experience, I would say that knowing whether or not the date that you select in the "Later than date" filter will be included in the final results table comes down to recognizing whether the column that you are working with is a Date or a Datetime data type. At this time, Visual SQL mode filters do not display different date/datetime operators based on if you are working with a Date or Datetime column, though I can certainly see the value that this would have. If you would like, I can submit a feature request on your behalf to see if our team is able to incorporate a "later than datetime" operator in Visual SQL mode when filtering on a Datetime column rather than a Date data type column.