Hi
I'm working with dates and times in APEX and trying to get them to work for users across time zones. I've raised a separate thread (Local Date/Time Problems) asking how people currently handle this, but I've hit a couple of issues in APEX that are preventing me from progressing.
As mentioned in the other thread there are issues in APEX with using the automatic time zone function, so I am trying to save timestamps that explicitly state the time zone. I can use a different date picker that will provide the date in my own format which includes the zone. However, to test this out in APEX I'm just using a text field.
The issues I've found are:
1 - When selecting the format mask for the text field, I've selected a value from the list. This displays as "12-JAN-2004 14:30 +00:00" in the list but returns a format mask of "DD-MON-YYYY HH24:MI TZR". I thought TZR was the time zone region i.e Europe/London and the format mask should have been TZH:TZM. I wouldn't normally mention this but as there are issues with this format, I don't know what is correct. The output shown is what I want, but the field doesn't work. What should the format be for the output I want?
2 - I've set the format mask as above, but when I try to save a value in it I get the error "ORA-01821: date format not recognized". I've set this up on apex.oracle.com (https://apex.oracle.com/pls/apex/f?p=42467:1:0::NO:1:P1_ID:1) If you try to change the value of either column you'll get this error. This is only when I have a field which includes a time zone.
3 - If you set the format mask to include TZH:TZM then you get the same ORA-01821 error when the page loads. I can set this up on apex.oracle.com, but I'd need a separate page or you can't see point 2 above! As far as I can tell TZH:TZM is the correct format so I'm not sure why there is this error.
As mentioned I'll be using a different date picker plugin to handle the input of dates, just for testing I'm using the text field. I did get the error when using the plugin, so simplified it to just be text. I will be using a plugin as I need the zone handled by the picker, which isn't included in the built-in one. This means that if a date is stored in zone +2 and the user is in a different zone then the date is converted by the date picker to their zone taking into account DST, something I believe only JavaScript can do as youcan't get the locale (only the zone which is of no use) from the browser.
Many thanks
Sara