I had a tricky issue with importing data containing dates in a csv file. When I enter or paste the date value into the csv file it formats it as a date data type but gives it the default formatting, in this case like this: 10/3/2020. But when importing into TG this format is not recognized as valid data and defaults to 1970-01-01.
OK, so I converted that column in the csv file to text and input it as 2020-10-03. Saved the file, reopened it later to change a column header, resaved, closed and imported it. Same problem occurred - 1970-01-01. Not believing my eyes\process I repeated several times until I discovered that if I carefully save the file with the proper format and NEVER reopen it, in most cases the date values imported correctly. Reopening the csv file automatically converts dates back to m\d\yyyy format.
So I think this will be problematic but I also understand that you have a problem taking different date formats as 10/3/2020 could be Oct. 3, 2020 in one culture and Mar. 10 2020 in another culture. I don’t know what the answer is for this other than adding big red warning signs in the docs and Graph Studio regarding the dangers of csv files and dates. Good luck on this one.
@gpadvorac List of acceptable formats:
Loading a DATETIME Attribute
When loading data into a DATETIME attribute, the GSQL loader will automatically read a string representation of datetime information and convert it to internal datetime representation. The loader accepts any of the following string formats:
-
%Y-%m-%d %H:%M:%S (e.g., 2011-02-03 01:02:03)
-
%Y/%m/%d %H:%M:%S (e.g., 2011/02/03 01:02:03)
-
%Y-%m-%dT%H:%M:%S.000z (e.g., 2011-02-03T01:02:03.123z, 123 will be ignored)
-
%Y-%m-%d (only date, no time, e.g., 2011-02-03 )
-
%Y/%m/%d (only date, no time, e.g., 2011/02/03)
-
Any integer value (Unix Epoch time, where Jan 1, 1970 at 00:00:00 is integer 0)
Format notation:
%Y is a 4-digit year. A 2-digit year is not a valid value.
%m and %s are a month (1 to 12) and a day (1 to 31), respectively. Leading zeroes are optional.
%H, %M, %S are hours (0 to 23), minutes (0 to 59) and seconds (0 to 59), respectively. Leading zeroes are optional.
When loading data, the loader checks whether the values of year, month, day, hour, minute, second are out of the valid range. If any invalid value is present, e.g. ‘2010-13-05’ or ‘2004-04-31 00:00:00’, the attribute is invalid and the object (vertex or edge) is not created.
Doucmentation Refernce:
https://docs.tigergraph.com/dev/gsql-ref/ddl-and-loading/creating-a-loading-job#loading-a-datetime-attribute
Thanks @Jon_Herke. I think the real problem here is MS Excel that will reformat datetime data without the user being aware of the change and it will use a default localized format that’s not valid. Thanks for the info and link and I’ll look into finding an alternative to Excel for working with csv files.
