Hey Steve,
I'm currently following this (extra column) approach. I had considered that the alter session approach doesn't give much control over the formatting. (Although it is worth noting that the date column type is already preformatted (and unchangeable) in getfeature and getfeatureinfo calls) I was just wondering if it would be worth it to make the time something that is natively displayed (similar to the postgres timestamp column...the oracle timestamp doesn't show up at all). Perhaps setting up control in the datastore setup page is a good idea?
Aaron
-----Original Message-----
From: Steve Way [mailto:Steve.Way@anonymised.com]
Sent: Wednesday, March 16, 2011 2:05 PM
To: Gundel, Aaron; geoserver-devel@lists.sourceforge.net
Subject: RE: Oracle question
Hi Aaron,
First of all, if you are going to go down the ALTER SESSION route, then I suggest creating an after logon trigger which would handle this. I don't think we should handle this in the Java as it is implementation specific, and whilst would suit us UK based users, others across the pond would be less than impressed. I do not think this is worth while.
What we really should do however, is make use of the TO_CHAR function, which converts dates from the date column into a char representation. The reverse of this from char to date is TO_DATE.
So if i understand your use case correctly, we would use TO_CHAR.
I think the best thing to do in this case, would be to add an extra column to your table of type VARCHAR2 called chardate for example, and run the simple query:
update <tablename> set chardate = TO_CHAR(datetimecolumn, 'DD/MM/YYYY HH24:MI:SS');
The second part of the function is the formatting you would like - if you look up the function you could specify which formatting option you like.
The reason I don't think this should be in the code as it would be different for every single user, so unless we have a datastore dropdown to select the format, then the easiest change for this is in the db itself.
Cheers,
Steve
________________________________________
From: Gundel, Aaron [Aaron_Gundel@anonymised.com]
Sent: 16 March 2011 17:42
To: geoserver-devel@lists.sourceforge.net
Subject: [Geoserver-devel] Oracle question
Hello all,
I'm emailing about an issue I posted on the users list a while ago, and I was wondering if this was something that I could fix (supposing I'm not running into designed behavior I'm just not understanding).
When using Oracle, if you have a Date field, this field also contains a time. This time is not being output when you call GetFeature/GetFeatureInfo. Miles suggested that the datastore could be updated to alter the session and correct the problem, using ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; This would at least get the time out there, although it doesn't give you much control of the formatting.
Does anyone have any feedback on whether or not something like this would be useful? Am I missing an obvious/easy fix? If it is something to change, I have some time to poke around and try to make it work. If someone could point me in the right direction in the source code, I'll make the change and try to test it out.
Also, in a related question, how does one debug geotools? I think this question has been asked before, but I couldn't find any documentation on the geotools site.
Thanks!
Aaron
DISCLAIMER:
This email may contain confidential information and is intended only for the use of the specific individual(s) to which it is addressed. If you are not the intended recipient of this email, you are hereby notified that any unauthorized use, dissemination or copying of this email or the information contained in it or attached to it is strictly prohibited. If you received this message in error, please immediately notify the sender at Infotech or Mail.Admin@anonymised.com and delete the original message.
