I've been working on modifying some Java code that accesses a SQLAnywhere 12 database. I've just updated the JDBC drivers to use the sajdbc4.jar file, and so far it's working fine.

One of the columns in the database is a timestamp, but it's been causing problems because it holds data from multiple timezones. I would like to convert the column to a "timestamp with time zone". But my test harness is now throwing the following error:

java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
    at java.sql.Timestamp.valueOf(Timestamp.java:246)
    at sybase.jdbc4.sqlanywhere.IConnection.parseTimestamp(IConnection.java:751)
    at sybase.jdbc4.sqlanywhere.IIResultSet.getTimestamp(Native Method)
    at sybase.jdbc4.sqlanywhere.IResultSet.getTimestamp(IResultSet.java:541)

Does the latest iAnywhere JDBC driver support timezone with time stamp? Or is there a specific method that must be called on the driver to get this data?

asked 19 Jun '12, 21:37

Jonathan%20Baker's gravatar image

Jonathan Baker
1961211
accept rate: 22%

Just another very wild guess: May you use the Resultset.getTimestamp(String columnLabel, Calendar cal) method, i.e. additionally specifying a Calender object to hold the time zone information? Or use a generic getObject() method?

Cf. the following doc for IBM's JDBC driver - I do not know at all if this applies to the SQL Anywhere driver or not. - Just wild guesses...

(20 Jun '12, 03:51) Volker Barth
Replies hidden

Is your field a normal timestamp field, or a new one with timezone information ?

http://dcx.sybase.com/1200/en/dbreference/timestamp-with-timezone.html

When you use the simple timestamp database type, then there is no way to store/retrieve the timezone....

(20 Jun '12, 06:25) ASchild
Replies hidden

It's a timestamp with time zone column.

(20 Jun '12, 12:43) Jonathan Baker

The call getTimeStamp(columnLabel, calendar) is used to translate a "time zone missing" data in to a specific time zone. And that's one possible way to solve the problem (decide on a time zone for the database, and have the software convert everything in to that time zone).

The problem, however, is that reporting software doesn't have any idea about that "we picked it" time zone. So I would like to store the time zone in the field, to prevent ambiguity.

IBM, FYI, handles this cleanly. Their driver translates the time while creating a Timezone object. It appears (from the stack trace above) that the iAnywhere driver doesn't do this. It just hands the data off, and assumes it's correct.

(20 Jun '12, 12:49) Jonathan Baker

I'm not sure the IBM way is better. If the drivers are running on the end users time zone, then OK

But when accessing via web services or web pages the driver is the "located" on the server time zone. And it's then still up to you to do the conversions..

(22 Jun '12, 15:46) ASchild

Sorry, but we haven't used them currently. Still too many installations with asa 9-11 out there

(22 Jun '12, 15:47) ASchild

I think you are missing the bigger picture. The iAnywhere driver won't even LOAD a time with time zone field. It just throws an exception.

(25 Jun '12, 15:21) Jonathan Baker
showing 3 of 7 show all flat view

After an internal discussion with iAnywhere engineering, it turns out this is a bug in the driver software. A case has been opened to fix it, so a patch should be out sometime soon.

In the meantime, this is what happened: The driver is handing a string to java.sql.Timestamp constructor. But Timestamp doesn't understand a format with a time zone, so it kicks out this error.

The following code will perform a conversion for you. This code is server time zone independent. The time is translated in to a date object on the client, and can be converted using standard java Calendar functions.

public static Timestamp attemptConversionOfTimeZoneColumn( String rawData ) throws ParseException, SQLException
{
    // SQLAnywhere sends us the time zone in +XX:XX format.
    // DateFormat wants this in +XXXX format.
    // Pull the last colon character out of the string, then send the converted string to parse()

    String parsedString;
    int colonPosition = rawData.trim().indexOf( ':', rawData.length() - 5 );
    if ( colonPosition > 0 )
        parsedString = rawData.substring( 0, colonPosition ) + rawData.substring( colonPosition + 1 );
    else
        parsedString = rawData;

    final DateFormat df = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss.SSSSSS Z" );
    Date testDate = df.parse( parsedString );

    if ( testDate != null )
        return new Timestamp( testDate.getTime() );
    else
        throw new SQLException("Unable to convert '" + rawData + "' in to a Timestamp.");
}
permanent link

answered 08 Jul '12, 15:30

Jonathan%20Baker's gravatar image

Jonathan Baker
1961211
accept rate: 22%

edited 11 Jul '12, 15:20

As with the SQL Anywhere ODBC Driver, the SA JDBC driver also supports timestamp with time zone but only for display purposes (i.e. it treats these values as strings not timestamps). Calling getTimeStamp() will therefore cause the above error. Instead, your best bet is to prepare a statement that casts the timestamp with time zone value as a timestamp. That will allow the server to "do the right thing" with the timestamp with time zone value. You can then use getTimestamp() on the result of the cast.

permanent link

answered 25 Jun '12, 15:35

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

Comment Text Removed
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×66
×21
×5

question asked: 19 Jun '12, 21:37

question was seen: 3,921 times

last updated: 11 Jul '12, 15:20