Custom SQL drop time in datetime

[isabella]

Is there a function in the Quicksight Custom SQL query window that will allow me to drop the time portion from my datetime. My format is currently: yyyy-MM-dd HH:mm:ss.SSS and I would like my format to be * yyyy-MM-dd 00:00:00.000. I need the transformation to be done in my underlying table pulled from Timestream so that it can be joined with my other table whos source is Athena, with the date format of yyyy-MM-dd 00:00:00.000.

[andres007]

Hello @isabella,

Custom SQL uses the databse query language of your source. You should look for functions in this database that can format the date, e.g. if you are using Amazon TimeStream you can find the functions to parse dates here

Date / time functions - Amazon Timestream

Same thing with Athena

Functions in Amazon Athena - Amazon Athena

Hope this helps.

[isabella]

The data source im using is timestream, but the timestream date/time functions return errors. What can I do to fix this?

[andres007]

Hi,

I am not sure if the current_time syntax is wrong in your query, I have also not being able to import it on QuickSight, looks like the type “time” is not supported.

But I was able to format the field time like this.

Hope this helps!