db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Localisation problem
Date Wed, 17 Oct 2012 14:00:58 GMT
I have a web application which includes a method which displays tables 
(or more often views) which can be sorted and filtered in various ways 
by the user. In the case of dates, I provide two columns (typically 
called "usr_time" and "time") where "time" is the underlying timestatmp 
and "usr_time" is formatted by an external function (TimeFormat) to a 
format like "17-Oct-2012 at 15:25".

For example, I have a view defined like this:
   CREATE VIEW user_history_log AS
       SELECT  TimeFormat(time) AS usr_time,
               facility,
               event,
               details,
               time,
               username
       FROM    system_log;

The user can filter the output so that only dates containing a substring 
like "Oct" will be displayed (using the usr_time column) or sorted (in 
which case the "usr_" prefix is noted and stripped off to give the name 
of the underlying column so that the date column will be sorted 
chronologically rather than alphabetically). The underlying query used 
to display the table might be something like

   SELECT usr_time,facility,event,details
   FROM   user_history_log
   WHERE  username='FOO' AND usr_time LIKE '%oct%'
   ORDER BY time DESC;

A problem has arisen in connection with internationalisation. The user 
can now select a preferred language, which is fine except for the dates 
displayed in the tables. The external TimeFormat function that does the 
formatting is of course declared static, so it has no idea who the user 
is or what the preferred language is. So I either have to use English 
for displaying dates regardless of the chosen language, or not use 
TimeFormat so that the user can't filter the output as before, or find 
some way of passing a locale code to TimeFormat by some devious trick.

Only the last of these three seems acceptable, but I haven't been able 
to come up with the devious trick I need. Can anyone help?

TIA,
-- 
John English

Mime
View raw message