Null values in "timestamp with time zone" data type field are interpretes as "empty" (string) in QS

Question & Answer
, ,
1

Hi all,

I produce two redshift tables that contain many fields of “timestamp with timezone” data type. Then I UNION ALL to make one table. When unloading the table to s3 and import it to QS, one of these fields appear as “string” data type and QS does not let me convert it to date. I noticed that all other date fields appear to have null values in QS. But the problematic one, instead of having Null’s, it has “empty” values and QS probably interprets them as strings. I can’t figure out what goes wrong with this field since I follow the same process for all other fields and Redshift interpretes Nulls as nulls and not varchar. Any ideas?
p.s: Note that one of the two tables I “UNION ALL” has only null values.

2

Hi @Fotis_flex

Here is the link for QuickSight supported datetime formats : Supported date formats - Amazon QuickSight
Convert the date field before unloading into one of the supported formats.

or try converting the string field into data using parse function : parseDate - Amazon QuickSight

If above steps doesn’t work, Please share the same set of timestamp field and we can try from our end.

3

@Ashok Thanks for your reply. I am afraid nothing works. An update from my side:
I create 2 tables in Redshift which I would like to later “UNION ALL”. The problematic field “fromtime” is defined as timestamp with timezone data type in both tables. I think the problem is that this field, in Table1, has only null values. In Table 2, it takes both null and correct formatted dates. When I import table 2 in QS, the field appears of Date data type as expected. When I import Table 1 (with only Null’s in this field), the field appears as string in QS without the option to convert it to date. The same happens when I 'UNION ALL" the two tables into one. After the union, importing the table to QS gives me string data type w/o being able to convert it. I tried the parseDate, and although it indeed converts it, the function excludes nulls and refresh fails (see screenshot)
Screenshot from 2023-10-13 14-10-40

4

Hello @Fotis_flex, my suggestion would be to use the SQL Cast function to change the empty string values from the field to NULL when you build your custom SQL query in QuickSight to ingest your dataset. That should ensure your field is either returning the datetime value or NULL so it doesn’t try to convert your field to a string.

I will mark this as a solution for now, but if you continue to experience this issue or found a different work-around please let me know!