the best practice to deal with datetime in mysql using jdbc?

[lightning]

I found that standard jdbc api does not have a very convenient way to
deal with datetime.

So I use it in this way:


DateFormat df =
DateFormat.getDateTimeInstance(DateFormat.LONG,DateFormat.LONG,
Locale.CHINA);
DateFormat dfp =
DateFormat.getDateTimeInstance(DateFormat.MEDIUM,DateFormat.MEDIUM,
Locale.CHINA);


String output=df.format(dfp.parse(rs.getString("time")));

Is this the best practice ?

 

[Lew]

I found that standard jdbc [sic] api [sic] does not have a very convenient way to
deal with datetime.

Maybe not, but it does have very convenient ways to deal with TIMESTAMP.

So I use it in this way:


DateFormat df =
DateFormat.getDateTimeInstance(DateFormat.LONG,DateFormat.LONG,
Locale.CHINA);
DateFormat dfp =
DateFormat.getDateTimeInstance(DateFormat.MEDIUM,DateFormat.MEDIUM,
Locale.CHINA);


String output=df.format(dfp.parse(rs.getString("time")));

Is this the best practice ?

No. It's pretty much worst practice.

Logically, a "datetime", really a SQL TIMESTAMP as there is no such thing as a
"datetime" in Java or SQL, is not formatted, just an abstract representation
of a moment.

Normally you wouldn't store such a value as a CHAR, VARCHAR or other text
variant. You'd store it as a TIMESTAMP. So ResultSet.getString() is the
wrong method to use. The right method is getTimestamp().

Likewise you have getDate() and getTime() for SQL DATE and TIME values,
respectively.

The Java types for TIMESTAMP, DATE and TIME are java.sql.Timestamp,
java.sql.Date and java.sql.Time, respectively. All three are direct
subclasses of java.util.Date. Read the docs carefully; there are significant
caveats to these classes.

 

[Owen Jacobson]

I found that standard jdbc [sic] api [sic] does not have a very convenient way to
deal with datetime.

Maybe not, but it does have very convenient ways to deal with TIMESTAMP.

So I use it in this way:

DateFormat df =
DateFormat.getDateTimeInstance(DateFormat.LONG,DateFormat.LONG,
                           Locale.CHINA);
DateFormat dfp =
DateFormat.getDateTimeInstance(DateFormat.MEDIUM,DateFormat.MEDIUM,
                           Locale.CHINA);

String output=df.format(dfp.parse(rs.getString("time")));

Is this the best practice ?

No.  It's pretty much worst practice.

Logically, a "datetime", really a SQL TIMESTAMP as there is no such thing as a
"datetime" in Java or SQL, is not formatted, just an abstract representation
of a moment.

Normally you wouldn't store such a value as a CHAR, VARCHAR or other text
variant.  You'd store it as a TIMESTAMP.  So ResultSet.getString() is the
wrong method to use.  The right method is getTimestamp().

Likewise you have getDate() and getTime() for SQL DATE and TIME values,
respectively.

The Java types for TIMESTAMP, DATE and TIME are java.sql.Timestamp,
java.sql.Date and java.sql.Time, respectively.  All three are direct
subclasses of java.util.Date.  Read the docs carefully; there are significant
caveats to these classes.

For any other RDBMS I'd agree with Lew, but MySQL has some brain
damage in this department. In MySQLese, DATETIME is the type allowing
'2008-03-17 00:03:14' for a large range of dates and times, with one-
second resolution (corresponding to the SQL TIMESTAMP type), and
TIMESTAMP is the MySQL mapping of its implementation's time_t type (on
most unices, a 32-bit int representing seconds since 1970).

MySQL TIMESTAMPs also have some constraints about how many of them you
can have and in what order, within a table, which is completely inane
and makes the type hard to use for general-purpose storage of
timestamps -- MySQL very strongly assumes TIMESTAMP means "timetamp of
the creation of this row".

The MySQL java connector should have some documentation indicating how
DATETIME columns map to JDBC types -- I wouldn't be surprised if the
java.sql.Timestamp type was an appropriate mapping (along with
getTimestamp/setTimestamp).

 

[Mark Space]

I found that standard jdbc api does not have a very convenient way to
deal with datetime.

Really? That seems odd. Which JDBC connector is it? The one from the
MySQL site? Can you link to it so we can look at the documentation?

Assuming you really can't use DATETIME, I'd convert all times to a
BIGINT, maybe as seconds or milliseconds, and store that as GMT. Call
the column SECONDS-GMT or something just to be sure folks get it.

But that might not be best practice either. Best practice is probably
to read the documentation and figure out how to get the JDBC to work.

 

[Wojtek]

Lew wrote :

You'd store it as a TIMESTAMP

Careful with a column set as type TIMESTAMP. Many DB's will
/automatically/ set a TIMESTAMP column to the current time. So when you
update the row without specifying a value for the TIMESTAMP column, the
DB will set a value for you.

I always use a DATETIME type, as the DB leaves this alone.

 

[Arne Vajhøj]

For any other RDBMS I'd agree with Lew, but MySQL has some brain
damage in this department. In MySQLese, DATETIME is the type allowing
'2008-03-17 00:03:14' for a large range of dates and times, with one-
second resolution (corresponding to the SQL TIMESTAMP type), and
TIMESTAMP is the MySQL mapping of its implementation's time_t type (on
most unices, a 32-bit int representing seconds since 1970).

MySQL TIMESTAMPs also have some constraints about how many of them you
can have and in what order, within a table, which is completely inane
and makes the type hard to use for general-purpose storage of
timestamps -- MySQL very strongly assumes TIMESTAMP means "timetamp of
the creation of this row".

The MySQL java connector should have some documentation indicating how
DATETIME columns map to JDBC types -- I wouldn't be surprised if the
java.sql.Timestamp type was an appropriate mapping (along with
getTimestamp/setTimestamp).

MySQL DATETIME should be used with Java Timestamp.

MySQL TIMESTAMP is for fields that get automaticly updated with the
time of last change to the row (without the app doing anything).

It is a very useful feature. And it should also be obvious why
it does not make any sense to have two such fields.

Its behavior is documented. Yes - people has been burned by that
behavior, but using a database without reading basic documentation
has always been a risky business.

Arne

PS: In newer MySQL versions TIMESTAMP offers more options for
behavior than described above.

 

[Lew]

Lew wrote :

Careful with a column set as type TIMESTAMP. Many DB's will
/automatically/ set a TIMESTAMP column to the current time. So when you
update the row without specifying a value for the TIMESTAMP column, the
DB will set a value for you.

I always use a DATETIME type, as the DB leaves this alone.

As Arne pointed out, you are speaking only of the MySQL dialect, not standard
SQL. Standard SQL does not have a DATETIME type, only TIMESTAMP, which does
not have an automatic setting. That behavior is not in "many DB's [sic]",
only in MySQL, AFAIK. Certainly not in any other DBMS that claims SQL compliance.

Regardless, as with any other dialect difference, you use JDBC to hide the
variations, in this case behind the java.sql.Timestamp type, as Arne
mentioned. JDBC maps the Java type to the closest available type, which in
MySQL is the crippled DATETIME.

 

[Arne Vajhøj]

As Arne pointed out, you are speaking only of the MySQL dialect, not
standard SQL. Standard SQL does not have a DATETIME type, only
TIMESTAMP, which does not have an automatic setting. That behavior is
not in "many DB's [sic]", only in MySQL, AFAIK. Certainly not in any
other DBMS that claims SQL compliance.

SQLServer 2005 BOL:

datetime and smalldatetime
Represent the date and the time of day.

Data type Range Accuracy
datetime
January 1, 1753, through December 31, 9999
3.33 milliseconds

smalldatetime
January 1, 1900, through June 6, 2079
1 minute
and:

Each database has a counter that is incremented for each insert or
>update operation that is performed on a table that contains a timestamp
>column within the database. This counter is the database timestamp.
>This tracks a relative time within a database, not an actual time that
>can be associated with a clock. A table can have only one timestamp
>column. Every time that a row with a timestamp column is modified or
>inserted, the incremented database timestamp value is inserted in the
>timestamp column.

Arne

 

[Lew]

SQLServer 2005 BOL:

Well, shiver me timbers.

I stand corrected, sort of.

using a database without reading basic documentation
has always been a risky business.

I actually don't mind that SQL is such a loose standard. The areas like this
are cognate enough, for all that they could be tricky if we ignore the RTFM
advice, that at least we don't necessarily have to change our Java code for
them if we make the right translations between the SQL platforms.

Most of the differences between SQL dialects fall in the area of enhancements,
such as the variants of LIMIT ... OFFSET and the various flavors of subqueries
as columns, temporary views or tables, update clauses and whatnot. More
important differences exist in the support of OLAP and scalable, stable
deployment.

As a programmer who is more used to Standard-ish-esque-like SQLs like Oracle
and Postgres, I can no more afford to be parochial in my approach to DBMSes
than can those weaned on the Microsoft / MySQL diet. It's handy to turn up
Rosetta Stones like one mentione a few times hereabouts,

<http://troels.arvin.dk/db/rdbms/>

which specifically mentions TIMESTAMP:
<http://troels.arvin.dk/db/rdbms/#data_types-date_and_time-timestamp>
and also auto-sequencing keys, another biggie:
<http://troels.arvin.dk/db/rdbms/#mix-identity>

 

[lightning]

MySQL DATETIME should be used with Java Timestamp.

Yes, you are right.
In mysql,to deal with Datetime,
read:
rs.getTimestamp()
write:
rs.setTimestamp()

And more, I surveyed apache common DBUtils, its BeanProcessor only
deal with property of Java Timestamp ,but not with Java Date or Java
Time.

Common DBUtils tell me that I should always use Java Timestamp instead
of what else.

this is the code in BeanProcessor:


protected Object processColumn(ResultSet rs, int index, Class
propType)
throws SQLException {

if (propType.equals(String.class)) {
return rs.getString(index);

} else if (
propType.equals(Integer.TYPE) ||
propType.equals(Integer.class)) {
return new Integer(rs.getInt(index));

} else if (
propType.equals(Boolean.TYPE) ||
propType.equals(Boolean.class)) {
return new Boolean(rs.getBoolean(index));

} else if (propType.equals(Long.TYPE) ||
propType.equals(Long.class)) {
return new Long(rs.getLong(index));

} else if (
propType.equals(Double.TYPE) ||
propType.equals(Double.class)) {
return new Double(rs.getDouble(index));

} else if (
propType.equals(Float.TYPE) ||
propType.equals(Float.class)) {
return new Float(rs.getFloat(index));

} else if (
propType.equals(Short.TYPE) ||
propType.equals(Short.class)) {
return new Short(rs.getShort(index));

} else if (propType.equals(Byte.TYPE) ||
propType.equals(Byte.class)) {
return new Byte(rs.getByte(index));

} else if (propType.equals(Timestamp.class)) {
return rs.getTimestamp(index);

} else {
return rs.getObject(index);
}

}