Persian calendar - Insertion and retrieval date in Persian format from Oracle DB

Question

My application requires the following.

In my application(in struts), we need to support Persian Calendar.

When we submit a form, date is coming as String in Action class. We need to save this date in Persian format in DB. We have configured DB for Persian Calendar. And while retrieving data from DB user should be able to see the date in Persian format only.

Also, the user can switch in 2 languages(English, Persian). So, application should support both type of calendars(Gregorian and Persian). If user logged-in in English, Gregorian calendar should be visible. If user logged-in in Persian language, then Persian Calendar should be visible.

For date conversion from Gregorian to Persian I am using below: http://www.dailyfreecode.com/forum/converter-gregorian-date-jalali-date-20288.aspx

In above requirement, I am facing 2 issues:

1. While submitting a form, how can we save date(which is in String format in Action class) in Persian format in DB?
2. While retrieving data from DB, it should come in Persian format. As of now, the JDBC client is retrieving the date in Gregorian Calender.

I am passing java.sql.Date(today's date) which is getting saved in persian format in DB. Using below code.

java.sql.Date sqlDate = null; 
java.util.Date utilDate = new Date(); 
sqlDate = new java.sql.Date(utilDate.getTime()); 
PreparedStatement psmtInsert = conn.prepareStatement(insertQuery); 
psmtInsert.setDate(1, sqlDate)); 
psmtInsert.executeUpdate();

For retrieving:

PreparedStatement psmtSelect = conn.prepareStatement("select dateOfJoining from EMPLOYEE");
ResultSet resultSet = psmtSelect.executeQuery();
while (resultSet.next()) {
    System.out.println(resultSet.getDate(1));
}

But it is returning date in Gregorian type.

Do we have any setting in Tomcat/JVM/JDBC client which converts date returned from DB into Persian Format itself(Like we have NLS_CALENDAR ,NLS_DATE_FORMAT in Oracle)?

For 1st issue, if I am passing date in Persian format then In DB it is saving incorrectly. PFB my code:

java.sql.Date sqlDate = null; 
java.util.Date utilDate = new Date("1397/02/04"); 
sqlDate = new java.sql.Date(utilDate.getTime()); 
PreparedStatement psmtInsert = conn.prepareStatement(insertQuery); 
psmtInsert.setDate(1, sqlDate)); 
psmtInsert.executeUpdate();

Above is inserting as 0777/09/13 in DB.

How can we overcome the above issues? Thanks in advance.

Answer 1

There is no calendar type in java.sql.Date so it is impossible to force it to Persian. All date/time values in JDBC are normalized in a neutral form and Oracle Database uses a neutral form as well for storage and processing. When NLS_CALENDAR is PERSIAN, Oracle converts the value to and from the neutral form and it is just "presenting" it in the Persian calendar convention while it is handling in the neutral form internally.

Usually it is optimal to use a neutral form consistently for all values in the backend. Typically a converter is used in the UI layer as part of the localization to tailor to the preferred locale for individual users. If localization in Java in the middle tier is needed, the java.time.chrono package that Douglas is suggesting above would be a clean solution.

Answer 2

While submitting a form, how can we save date(which is in String format in Action class) in Persian format in DB?

Store the date as a DATE data type and when you want to insert it into the table use TO_DATE with the NLS calendar parameter for Persian to convert it from a Persion formatted string to a date:

SQL Fiddle

Query 1:

SELECT TO_DATE(
         '1397/02/05',
         'yyyy/mm/dd',
         'nls_calendar=persian'
       )
FROM   DUAL

Results:

| TO_DATE('1397/02/05','YYYY/MM/DD','NLS_CALENDAR=PERSIAN') |
|-----------------------------------------------------------|
|                                      2018-04-25T00:00:00Z |

While retrieving data from DB, it should come in Persian format. As of now, the JDBC client is retrieving the date in Gregorian Calender.

When you output the value, just specify the calendar you want to use to format it. So for Persian, use TO_CHAR with the NLS calendar parameter for Persian:

Query 2:

SELECT TO_CHAR(
         DATE '2018-04-25',
         'yyyy/mm/dd',
         'nls_calendar=persian'
       )
FROM   DUAL

Results:

| TO_CHAR(DATE'2018-04-25','YYYY/MM/DD','NLS_CALENDAR=PERSIAN') |
|---------------------------------------------------------------|
|                                                    1397/02/05 |

Do we have any way using which JDBC client retrieve date in Persian format.

This is a common misconception. A DATE data type stored in Oracle tables as 7 bytes containing year (2 bytes), month, day, hours, minutes and seconds (1 byte each). It does not have any "format" (but it is effectively stored in the Gregorian calendar).

JDBC does not transfer a formatted date, it just transfers those 7 bytes and stores it in a java.sql.Date class (which also just stores those bytes).

If you want to format a DATE data type then you need to convert it to another data type; typically a string for which you want to use TO_CHAR in the Oracle database (or some other method to format Dates in Java).

Answer 3

Just to provide the inputs for those who still are looking how to implement the Persian calendar in the application.

My application should support Gregorian and Persian calendar, also Oracle and PostgreSQL DBs should be supported.

To implement the Persian calendar with minimum efforts performed the below steps:

1. Converting every date coming from UI into Gregorian format in validate() of form using the implementation suggested in below link http://www.dailyfreecode.com/forum/converter-gregorian-date-jalali-date-20288.aspx

2. Once the dates are converted to Gregorian the whole application will keep running as it was running earlier with Gregorian dates.

3. While saving the date in DB, I decided to store the date in Gregorian format only as I need to support PostgreSQL DB as well and it was not supporting the Persian Calendar.

4. While fetching the data to UI, the date will be retrieved from DB and again I am converting the date in Persian format.

To show the Persian calendar on UI, refer the below link. It has other calendars implementation as well http://keith-wood.name/calendars.html

The above approach can help implementing most of the different calendars.

Answer 4

java.sql.Date is defined to be in UTC which is Gregorian. Getting a DATE from the database as a java.sql.Date is not likely to do anything useful. The right answer would be to define a PersianChronology which extends java.time.chrono.AbstractChronology and PersianLocalDate which implements java.time.chrono.ChronoLocalDate. Then get the value from the database as a PersianLocalDate. That's a lot of work but it is in theory the right thing to do.

PersianLocalDate per = rs.getObject(col, PersianLocalDate.class);

I think this could be made to work. Not easy but possible. If your PersianLocalDate class defines the following method

public static PersianLocalDate of(oracle.sql.DATE date) { ... }

then Oracle Database JDBC would use that method to construct a PersianLocalDate in the getObject call above. The fun part would be implementating the of(DATE) method. All of the oracle.sql.DATE methods assume a Gregorian calendar. Your best bet is to call DATE.getBytes and interpret the bytes yourself. Bytes 0 and 1 are the year, 2 is the month and 3 is the day of the month. TIMESTAMP.getJavaYear will convert those two bytes into an int year. It doesn't know anything about calendars; it's just doing arithmetic. Depending on what the database does in sending a Persian DATE as a query result that should let you construct a PersianLocalDate. If the database is converting to Gregorian, you'll have to convert back to Persian. Your PersianChronology should help with that.

Going the other way, sending a PersianLocalDate to the database is going to be more interesting. The Oracle Database JDBC drivers have no capability of converting an unknown class to a database type like DATE, nothing equivalent to the of method hook described above. You could try making PersianLocalDate extend oracle.sql.ORAData. The toDatum method would have to return an oracle.sql.DATE with the same bytes that the database sent as a query result.

A simpler approach, maybe, would be to send Persian dates back and forth to the database as VARCHARs/Strings. The drivers would call a static of(String) method on PersianLocalDate so getting a PersianLocalDate would be easy. If the database does the right thing with PersianLocalDate.toString results then calling setString makes sending the values easy. If not then define PersianLocalDate.toDatabaseString and do the conversion yourself.

This is a use case that we talked about when we implemented support for java.time but we simply did not have the resources required to do anything. And we didn't know how common it would be so it was hard to justify doing the work. If you have a support contract I'd encourage you to file an SR and ask for an enhancement request. If you can provide a PersianChronology and PersianLocalDate it would be easier for me to get some resources allocated to do something. Maybe nothing more than a hook to make setObject work, but at least that. I wish I could be more help.