Convert numeric field to date

[Jesus]

Hello guys, can someone help me how to transform in quicksight, a numeric field in date format, I want to do the same as the TIME function in Excel.
I have a dataset with an hour, a minute and a second column, but it is in numerical format, as in the image, what function should I use to get a date? (time field)

[Naveed]

Hi,

only time filed will never covert into date-time. You can concat the date and time and later convert into timestamp.

Regards,

[Jesus]

yes, this formula in the link, converts it to the format I want, but I can not do calculations

Convert time duration to HH:MM:SS

Sharing a calculated field you can use to convert a numeric field (measured in seconds) into HH:MM:SS format (will become a string). You cannot do any math or date calculations while in this form as the result is a string, but it will work for display purposes (and will sort correctly if you place on an X-axis, table, etc). concat( ifelse({Average Talk Time} / 3600 < 10,concat('0',tostring(floor({Average Talk Time} / 3600))),tostring(floor({Average Talk Time} / 3600))) ,':', ifelse(({Averag…

the result is in string format
Is there a way to do the calculation?

[Jesse]

Hi Jesus - no you will need to do calculations in the integer format and then convert it to a string for display.

Alternatively you can convert it to a date and just put a generic date as part of it like 1/1/22 HH:MM:SS. Then use the date formatting to remove the date part altogether and only show the time. This way you can still do dateDiff, min, max and other calculations with it.

Would be something like this:
parseDate(concat("1/1/22 ", toString(Hour), “:”, toString(Min), “:”, toString(Sec)))

If you wind up doing this I would recommend moving this calc to the data prep layer so it gets materialized in SPICE - will help with performance if you have a lot of data.

[Jesus]

thanks a lot for the clear answer