[Mondrian] DATE datatype support

[Julian Hyde]

The Oracle DATE datatype is misnamed; it predates the ANSI standard DATE,
TIME and TIMESTAMP types, and maps more closely to ANSI's TIMESTAMP. It
contains hours, minutes, and seconds. Conventionally people who just want to
store date values truncate to midnight. (Which works as long as everyone
agrees which timezone midnight occurs in!)
 
So, it makes sense for Oracle's JDBC driver to return Timestamp values. We
just need to deal with them. If we can deal with them sensibly, we should be
able to handle schemas where people have chosen to store date values in a
timestamp field.
 
Does Oracle's driver allow you to call ResultSet.getDate on a column whose
type is ANSI TIMESTAMP (i.e. Oracle DATE)? If so, that would be the simplest
solution. If the column is defined in Mondrian as a date, that would tell
Mondrian to treat the value as a ANSI DATE field and use the corresponding
JDBC method to get it.
 
At the root of this problem is the fact that we have never adequately
specified or tested datetime behavior. The FoodMart schema doesn't even
contain a date field. It would be useful to add a table to the FoodMart
schema with say 100 rows and a column of each possible data type (including
decimals, date, time, timestamp, blob and clob). Then we could start writing
tests.
 
Julian


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Matt Campbell
Sent: Thursday, October 01, 2009 11:12 AM
To: Mondrian developer mailing list
Cc: geoffrey.pettengill at thomsonreuters.com
Subject: [Mondrian] DATE datatype support


The 1.4 Oracle drivers treat DATE fields as a java.sql.Date (makes sense).
WIth the 5 and 6 versions they have started treating both date and timestamp
as java.sql.Timestamp.  This can cause an error with Mondrian if any levels
are defined as having type='Date', since the JdbcDialect will assume that
date literals match the yyyy-mm-dd format and will throw an exception if the
string includes the time (e.g. '2001-01-19 00:00:00.0').  I've entered
MONDRIAN-626 <http://jira.pentaho.com/browse/MONDRIAN-626>  for this issue.

I'm not sure if any other jdbc drivers besides Oracle have a similar quirk
about treating Date and Timestamp both as java.sql.Timestamp.

One possible solution would be to update the dialect to drop the time
information if the datatype is DATE.  Another option would be to strip the
timestamp when the member is loaded.  The second option has the advantage of
dropping the unwanted time information from the member name.  Thoughts?  


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20091001/b95950a4/attachment.html