java.sql.Timestamp: Bug or...

[Hole]

Hi there,

I've noticed some strange problems in my app...after few hours of
debugging I found out that problems are in java.sql.Timestamp...

Please, if you have spare time, could you look at the following code
and test it with some dates?:

public static Timestamp toSqlTimestamp(String date, String fmt) throws
ParseException, Exception {
Timestamp res = null;
try {
SimpleDateFormat f = new SimpleDateFormat(fmt);
res = new Timestamp(f.parse(date).getTime());
} catch(ParseException pexc) {
throw pexc;
} catch(Exception exc) {
throw exc;
}
return res;
}

This code strangely returns wrong hours, but only whit particular
dates. For example, time from 2009-03-29 02:00:00 to 2009-03-29
03:00:00 (this excluded) returns a timestamp of one hour more...

This is the output of a test program I made:

Original: 2009-03-29 01:45:00
toSqlTimestamp: 2009-03-29 01:45:00

Original: 2009-03-29 02:00:00
toSqlTimestamp: 2009-03-29 03:00:00

Original: 2009-03-29 02:15:00
toSqlTimestamp: 2009-03-29 03:15:00

Original: 2009-03-29 02:30:00
toSqlTimestamp: 2009-03-29 03:30:00

Original: 2009-03-29 02:45:00
toSqlTimestamp: 2009-03-29 03:45:00

Original: 2009-03-29 03:00:00
toSqlTimestamp: 2009-03-29 03:00:00

 

[Lew]

I've noticed some strange problems in my app...after few hours of
debugging I found out that problems are in java.sql.Timestamp...

Please, if you have spare time, could you look at the following code
and test it with some dates?:

public static Timestamp toSqlTimestamp(String date, String fmt) throws
ParseException, Exception {

Declaring 'throws Exception' is an antipattern.

Timestamp res = null;
try {
SimpleDateFormat f = new SimpleDateFormat(fmt);
res = new Timestamp(f.parse(date).getTime());
} catch(ParseException pexc) {
throw pexc;

This catch clause is redundant since the method already rethrows the exception
without it.

} catch(Exception exc) {

Catching 'Exception' is usually an antipattern.

Failing to log or handle exceptions is an antipattern.

throw exc;
}
return res;
}

This code strangely returns wrong hours, but only whit particular
dates. For example, time from 2009-03-29 02:00:00 to 2009-03-29
03:00:00 (this excluded) returns a timestamp of one hour more...

This is the output of a test program I made:

Original: 2009-03-29 01:45:00
toSqlTimestamp: 2009-03-29 01:45:00

Original: 2009-03-29 02:00:00
toSqlTimestamp: 2009-03-29 03:00:00

Original: 2009-03-29 02:15:00
toSqlTimestamp: 2009-03-29 03:15:00

Original: 2009-03-29 02:30:00
toSqlTimestamp: 2009-03-29 03:30:00

Original: 2009-03-29 02:45:00
toSqlTimestamp: 2009-03-29 03:45:00

Original: 2009-03-29 03:00:00
toSqlTimestamp: 2009-03-29 03:00:00

What time zone is the 'DateFormat' under? I'm guessing that its Standard Time
is one hour after GMT, say in Europe somewhere.

You would get similar results if you used the DateFormat to produce a
java.util.Date instead of a java.sql.Timestamp.

 

[markspace]

Hi there,

I've noticed some strange problems in my app...after few hours of
debugging I found out that problems are in java.sql.Timestamp...

Like Lew and Patricia, I'm thinking "daylight savings time" is messing
you up. However, I can't duplicate your issue here. A complete SSCCE
would be appreciated. Also, what is your time zone? And lastly please
look up some downloads from Sun for correcting time zone information,
it's a pain to keep all of it straight and you might have a buggy
install if you haven't updated recently. (You'll have to Google for the
updates, I don't have links handy.)

Here's my full example and output.

package test;

import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

/**
*
* @author Brenden
*/
public class TimeStampTest
{

static String testStrings[] =
{
"2009-03-29 01:45:00",
"2009-03-29 02:00:00",
"2009-03-29 02:15:00",
"2009-03-29 02:30:00",
"2009-03-29 02:45:00",
"2009-03-29 03:00:00",
};

public static void main( String[] args ) throws Exception
{
for( String test : testStrings ) {
System.out.println( "Original: "+test );
System.out.println( "Timestamp: "+ toSqlTimestamp( test,
"yyyy-MM-dd HH:mm:ss" ) );
}
}

public static Timestamp toSqlTimestamp( String date, String fmt )
throws
ParseException, Exception
{
Timestamp res = null;
try
{
SimpleDateFormat f = new SimpleDateFormat( fmt );
res = new Timestamp( f.parse( date ).getTime() );
} catch( ParseException pexc )
{
throw pexc;
} catch( Exception exc )
{
throw exc;
}
return res;
}
}



run:
Original: 2009-03-29 01:45:00
Timestamp: 2009-03-29 01:45:00.0
Original: 2009-03-29 02:00:00
Timestamp: 2009-03-29 02:00:00.0
Original: 2009-03-29 02:15:00
Timestamp: 2009-03-29 02:15:00.0
Original: 2009-03-29 02:30:00
Timestamp: 2009-03-29 02:30:00.0
Original: 2009-03-29 02:45:00
Timestamp: 2009-03-29 02:45:00.0
Original: 2009-03-29 03:00:00
Timestamp: 2009-03-29 03:00:00.0
BUILD SUCCESSFUL (total time: 1 second)

 

[Hole]

Like Lew and Patricia, I'm thinking "daylight savings time" is messing
you up.  

Hi,

Thanks to anyone for every input and for your help.

Yes, indeed I've just came out with the same thing...it's the DST
problem.
My TZ is GMT+1...
Now, how to set a TZ in order to not taking into account the DST? I
would deal with a TZ free of DST issue...

 

[Lew]

Yes, indeed I've just came out with the same thing...it's the DST
problem.

The problem is not DST, it's the failure to account for it.

OK, DST is a problem, but a social one. Our job as programmers is to
accept and deal with it.

My TZ is GMT+1...

The EU adjusts to DST on the last Sunday in March (the 29th in 2009)
at 1 a.m. GMT, which was 2 a.m. in your time zone.

Now, how to set a TZ in order to not taking into account the DST? I
would deal with a TZ free of DST issue...

Set the TZ to GMT!

Again, it is our job as programmers to be educated about time zones.
Time for you to do some reading!

 

[markspace]

The problem is not DST, it's the failure to account for it.

OK, DST is a problem, but a social one. Our job as programmers is to
accept and deal with it.

Instead of accept it, why not engineer it away? I'm starting to think
that a VerySimpleDate class, which has no DST or time zone component,
would be a very useful thing. It should assume a 24 hour clock, and
include basic leap year calculations, and that's it.

To many surprises and gotchas show up if you don't do time zone
information just right, and it's hard to verify that you've done this
correctly for all time zones. If I want to test that my entire app uses
time zones correctly, how do I do that? Set my test harness to each
time zone and run a full app test? That's kind of ridiculous, but it's
need to catch any error in converting from one time zone to another
anywhere in the app. One missed "GMT" anywhere in the app and it'll
fail, but I'm not sure that I've got every "GMT" where I need it until I
test all possible date, time, and TZ combinations!

 

[Lew]

Instead of accept it, why not engineer it away?  I'm starting to think
that a VerySimpleDate class, which has no DST or time zone component,
would be a very useful thing.  It should assume a 24 hour clock, and
include basic leap year calculations, and that's it.

If the app has to deal with humans, and it involves date or time input
or outputs, then you must deal with time zones.

For example, I worked on a large application where the business rules
had to verify that a transaction was submitted within seven calendar
days of its deadline. There was no way to avoid dealing with Daylight
Saving Time because that was part of the business requirement.

It is our job as programmers to accept and deal with it.

 

[Lew]

It occurs to me belatedly that Java already has such a type:
<http://java.sun.com/javase/6/docs/api/java/util/Date.html>

 

[Jean-Baptiste Nizet]

Lew a écrit :

It occurs to me belatedly that Java already has such a type:
<http://java.sun.com/javase/6/docs/api/java/util/Date.html>

Exactly my thought : a Date is a number of millis since 1st January
1970. While you don't try to display it in a human readable format, you
don't have problems with time zones.

JB.

 

[Arne Vajhøj]

Instead of accept it, why not engineer it away? I'm starting to think
that a VerySimpleDate class, which has no DST or time zone component,
would be a very useful thing. It should assume a 24 hour clock, and
include basic leap year calculations, and that's it.

To many surprises and gotchas show up if you don't do time zone
information just right, and it's hard to verify that you've done this
correctly for all time zones. If I want to test that my entire app uses
time zones correctly, how do I do that? Set my test harness to each time
zone and run a full app test? That's kind of ridiculous, but it's need
to catch any error in converting from one time zone to another anywhere
in the app. One missed "GMT" anywhere in the app and it'll fail, but I'm
not sure that I've got every "GMT" where I need it until I test all
possible date, time, and TZ combinations!

Is it that much harder to check if TimeZone is provided everywhere
than to check if VerySimpleDate is used instead of Date/Calendar
everywhere?

Arne

 

[Lew]

To[o] many surprises and gotchas show up if you don't do time zone
information just right, and it's hard to verify that you've done this
correctly for all time zones. If I want to test that my entire app uses
time zones correctly, how do I do that? Set my test harness to each time
zone and run a full app test? That's kind of ridiculous, but it's need
to catch any error in converting from one time zone to another anywhere
in the app. One missed "GMT" anywhere in the app and it'll fail, but I'm
not sure that I've got every "GMT" where I need it until I test all
possible date, time, and TZ combinations!

Is it that much harder to check if TimeZone is provided everywhere
than to check if VerySimpleDate is used instead of Date/Calendar
everywhere?

If you layer and encapsulate your design properly, then it isn't necessary to
look the "entire" app for time zone but just those places that bring the data
in to GMT and the ones that express it out to a time zone. The suggestion
that you need the string "GMT" in a zillion places is ill founded.

For example, in a system with a database you store all dates and times in the
DB as Zulu time, and call out the local time at the local client and only
there, and in specific, well-documented spots at that.

Since the so-called "VerySimpleDate" is nothing more nor less than the
familiar 'java.util.Date', which by design holds values in Zulu time, the
problem is nearly completely solved for us already for Earth-based
applications. (Galactic time zones will require some adjustment.)

 

[Hole]

To[o] many surprises and gotchas show up if you don't do time zone
information just right, and it's hard to verify that you've done this
correctly for all time zones. If I want to test that my entire app uses
time zones correctly, how do I do that? Set my test harness to each time
zone and run a full app test? That's kind of ridiculous, but it's need
to catch any error in converting from one time zone to another anywhere
in the app. One missed "GMT" anywhere in the app and it'll fail, but I'm
not sure that I've got every "GMT" where I need it until I test all
possible date, time, and TZ combinations!

Is it that much harder to check if TimeZone is provided everywhere
than to check if VerySimpleDate is used instead of Date/Calendar
everywhere?

If you layer and encapsulate your design properly, then it isn't necessary to
look the "entire" app for time zone but just those places that bring the data
in to GMT and the ones that express it out to a time zone.  The suggestion
that you need the string "GMT" in a zillion places is ill founded.

For example, in a system with a database you store all dates and times in the
DB as Zulu time, and call out the local time at the local client and only
there, and in specific, well-documented spots at that.

Since the so-called "VerySimpleDate" is nothing more nor less than the
familiar 'java.util.Date', which by design holds values in Zulu time, the
problem is nearly completely solved for us already for Earth-based
applications.  (Galactic time zones will require some adjustment.)

Hi there,

Since the DST is both a political and an output format issue, it's got
some sense to set the timezone in a DateFormatter, but I'd have some
concerns.

However, I read that util.Date is ever an UTC date . But if you use
the DateFormatter.parse to parse a string representing an UTC date and
your formatter has a Timezone!=GMT, then your java.util.Date object
isn't a UTC date.
Is it right? If it is, what does tha statement "java.util.Date is
always an UTC date" mean?

 

[Lew]

Since the DST is both a political and an output format issue, it's got
some sense to set the timezone in a DateFormatter, but I'd have some
concerns.

However, I read that util.Date [sic] is ever an UTC date . But if you use
the DateFormatter.parse [sic] to parse a string representing an UTC date and
your formatter has a Timezone!=GMT, then your java.util.Date object
isn't a UTC date.
Is it right?

No.

The resultant 'Date' will be set to the UTC time corresponding to the
'TimeZone' actually used. In other words, you force the string to represent
the 'TimeZone' actually used. In your example, it is the string that isn't a
UTC date, not the 'Date'.

If it is, what does tha statement "java.util.Date is
always an UTC date" mean?

Just what it says.

 

[Hole]

To[o] many surprises and gotchas show up if you don't do time zone
information just right, and it's hard to verify that you've done this
correctly for all time zones. If I want to test that my entire app uses
time zones correctly, how do I do that? Set my test harness to each time
zone and run a full app test? That's kind of ridiculous, but it's need
to catch any error in converting from one time zone to another anywhere
in the app. One missed "GMT" anywhere in the app and it'll fail, but I'm
not sure that I've got every "GMT" where I need it until I test all
possible date, time, and TZ combinations!

Is it that much harder to check if TimeZone is provided everywhere
than to check if VerySimpleDate is used instead of Date/Calendar
everywhere?

If you layer and encapsulate your design properly, then it isn't necessary to
look the "entire" app for time zone but just those places that bring the data
in to GMT and the ones that express it out to a time zone.  The suggestion
that you need the string "GMT" in a zillion places is ill founded.

For example, in a system with a database you store all dates and times in the
DB as Zulu time, and call out the local time at the local client and only
there, and in specific, well-documented spots at that.

Since the so-called "VerySimpleDate" is nothing more nor less than the
familiar 'java.util.Date', which by design holds values in Zulu time, the
problem is nearly completely solved for us already for Earth-based
applications.  (Galactic time zones will require some adjustment.)

Hi there,

Since the DST is both a political and an output format issue, it's got
some sense to set the timezone in a DateFormatter, but I'd have some
concerns.

However, I read that util.Date is ever an UTC date . But if you use
the DateFormatter.parse to parse a string representing an UTC date and
your formatter has a Timezone!=GMT,  then your java.util.Date object
isn't a UTC date.
Is it right? If it is, what does tha statement "java.util.Date is
always an UTC date" mean?

For everyone is interested in the TimeZone thing:

If your original poor UTC string date passes unharmed any "silent &
transparent" conversion from a TimeZone to your local default one and
you have to insert that Timestamp in your database, remember to use
the method CallableStatement.setTimestamp() that accepts a Calendar
instance with the proper TimeZone adjusted, since the JDBC Driver will
use your default TimeZone when it constructs the TIMESTAMP.

 

[Lew]

For everyone is interested in the TimeZone thing:

If your original poor UTC string date passes unharmed any "silent&
transparent" conversion from a TimeZone to your local default one and
you have to insert that Timestamp in your database, remember to use
the method CallableStatement.setTimestamp() that accepts a Calendar
instance with the proper TimeZone adjusted, since the JDBC Driver will
use your default TimeZone when it constructs the TIMESTAMP.

Even better, since 'CallableStatement' is intended for stored procedure use
'PreparedStatement', intended to execute SQL commands. I don't understand the
wisdom of 'CallableStatement' if you're not invoking stored procedures.

<http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html#setDate(int,
java.sql.Date, java.util.Calendar)>
allows you to use a 'TimeZone' other than the default.

As a minute or two with the Javadocs would reveal.

Retrieval can use the corresponding
<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getDate(int,
java.util.Calendar)>

and of course there are parallel
<http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html#setTimestamp(int,
java.sql.Timestamp, java.util.Calendar)>

and
<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getTimestamp(java.lang.String,
java.util.Calendar)>

Note further that 'CallableStatement' inherits these setter methods.

 

[Hole]

Even better, since 'CallableStatement' is intended for stored procedure use
'PreparedStatement', intended to execute SQL commands.  I don't understand the
wisdom of 'CallableStatement' if you're not invoking stored procedures.

<http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html...,
java.sql.Date, java.util.Calendar)>
allows you to use a 'TimeZone' other than the default.

As a minute or two with the Javadocs would reveal.

Retrieval can use the corresponding
<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getDate...,
java.util.Calendar)>

and of course there are parallel
<http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html...,
java.sql.Timestamp, java.util.Calendar)>

and
<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getTime...,
java.util.Calendar)>

Note further that 'CallableStatement' inherits these setter methods.

Hi Lew,

indeed, I call an SP from my class.
However, even if I didn't check, I was sure that same signature
methods are available in other JDBC classes with similar aims so the
reader of this thread could have easily checked himself.

I thank you for your valuable tips and suggestions but I don't
understand your polemical writing.

 

[Lew]

Even better, since 'CallableStatement' is intended for stored procedure use
'PreparedStatement', intended to execute SQL commands. I don't understand the
wisdom of 'CallableStatement' if you're not invoking stored procedures.

<http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html...,
java.sql.Date, java.util.Calendar)>
allows you to use a 'TimeZone' other than the default.

As a minute or two with the Javadocs would reveal.

Retrieval can use the corresponding
<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getDate...,
java.util.Calendar)>

and of course there are parallel
<http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html...,
java.sql.Timestamp, java.util.Calendar)>

and
<http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getTime...,
java.util.Calendar)>

Note further that 'CallableStatement' inherits these setter methods.

Please don't quote sigs.

Hi Lew,

indeed, I call an SP from my class.

A detail you omitted. Now I comprehend.

Your advice was absolute - "remember to use the method
CallableStatement.setTimestamp()" - as if there were no alternatives.

However, even if I didn't check, I was sure that same signature
methods are available in other JDBC classes with similar aims so the
reader of this thread could have easily checked himself.

And now they have help knowing where to look.

I thank you for your valuable tips and suggestions but I don't
understand your polemical writing.

Huh?

I don't want to engender dispute. My aim is to encourage liberal use of
Javadocs, and to point out alternatives the absolutism of your advice foreclosed.

I've been using JDBC since my first Java job over a decade ago, and I still
read and reread the Javadocs for 'Statement', 'PreparedStatement', 'ResultSet'
and the rest, never mind the newer APIs.