Cannot parse a date in another time zone
Hi team -
I have a partner in Egypt who is doing a File Upload of Credit Card transactions from our Fraud Analytics App on our App Market. When I do the same File Upload, I get no errors or records dropped. However, when they do the upload, they get the error below. Has anyone seen this and any ideas what maybe going wrong?
By doing the File Upload as specified , it gives me the below counters :
When I check the errors :
ValidationException: Failed to convert value 2008-04-25 12:00:00 AM to type DATE or invalid date pattern yyyy-MM-dd hh:mm:ss a
Error Repeated: >100 times
Source Record: "4814","3774","WWW PPOUTLET COM","2008-04-25 12:00:00 AM","215.88","#### #### #### ####",
Caused by: java.lang.IllegalArgumentException: Cannot parse "2008-04-25 12:00:00 AM": Illegal instant due to time zone offset transition (Africa/Cairo)
-
Official comment
Hi Nikhil, this is occurring due to Daylight Saving Time. Specifically, in the customer's default timezone of Africa/Cairo, this instant corresponds to the moment when clocks were shifted forward by one hour: https://www.timeanddate.com/time/change/egypt/cairo?year=2008
You can set the timezone for Datameer in the <INSTALLDIR>/conf/default.properties file by updating the following property:
system.property.das.default-timezone=default
The value of "default" means that Datameer will use the host system's timezone. If you change this value to "UTC" then there is no Daylight Saving Time validation that could trigger the illegal instance exception.
Alternatively, if you do not want to change the timezone. You could import this column as a String instead of a Date. This will skip this Java type validation altogether.
Comment actions -
Hello Joel,
Thank you for your reply on this.
Is this some kind of bug in handling the Daylight Saving Time ?
Does this mean if a customer has Datameer in Egypt, they need to change its timezone to something different from the local time ?
On another note, I have already tried importing that column as a Date and used ASDATE function , but it converted the date/time combination incorrectly - it converted all the AMs to PMs
Thanks
Menatallah
-
This is not a bug, the Date parser from Java is working as designed. Java attempts to validate dates when they are entered into the system so that it can apply other date related functions. Not all dates are possible however.
For example, "February 30, 2016" is not a valid date. This never actually happens on a calendar. That is an example of a whole day that does not occur, but "2008-04-25 12:00:00 AM" is an invalid time in Africa/Cairo timezone. This time never occurs due to Daylight Saving Time. In Cairo, the time changed directly from "2008-04-24 11:59:59 PM" to "2008-04-25 01:00:00 AM" (in one second). The time for the input is totally skipped on the real life calendar in that timezone -- this is why the input is considered invalid.
Certain timezones do not have Daylight Saving Time and often reduce the complexity with time based calculations. One example of this is the UTC timezone. This is a good option to set the system.property.das.default-timezone if there are issues with importing dates due to Daylight Saving Time adjustments.
If you want to skip the date validation altogether, you can import this column as a String. With String types, both "2016-02-30 12:00:00 AM" and "2008-04-25 12:00:00 AM" are valid.
Can you share an example of the input and function syntax you used which ASDATE returned unexpected results from AM to PM?
Please sign in to leave a comment.
Comments
3 comments