I have data as “2023-03-08T08:59:47Z” and the data type of the field is string. I want to change it to timestamp which is required for my analysis.
Please provide a solution.
Hi @Rajdipch yes we can do this on the dataset level.
I have created a dummy dataset for this example so please follow the steps.
We can change the datatype of the timestamp field name from String type to Date type in the following ways.
Step -1
Click on the String data type and select the Date data type from the list menu.
Step -2
Once you select the Date type then you can see a dialog box where QuickSight ask to you for putting the Date format
As per data source the Date format is - yyyy-MM-dd’T’HH:mm:ssZZ
So we need to put the same date format and click on the Validate Menu and then Update menu.
Once you did that then you can see the timestamp column what was previously String Type now it is converted into Date Type.
Now you can see in the dataset page area the timestamp column is Date Type.
This is the one way we can convert the datatype of a column from String to Date type.
Another way is by creating one more calculated field and use the function as parseDate
but I think the timezone Z format is not supported in QuickSight.
1 Like
@Rajdipch - One of the quickest and easy solution is to create a calculate field with below formula
parseDate(concat(substring({Source_Date},1,4),‘-’,substring({Source_Date},6,2),‘-’,substring({Source_Date},9,2),’ ',substring({Source_Date},12,8)),‘yyyy-MM-dd HH:mm:ss’)
See the result
This way you do not need to change the format manually every time, by saying that @Biswajit_1993 approach is correct as well. It is just a different way of doing the same thing.
By calculated field, if your input is coming with right way, substring is one of the best way of extracting the details and change to date format. The same approach is always implemented in ETL and ELT as well.
Regards - San
2 Likes