Re: the best practice to deal with datetime in mysql using jdbc?
On Mar 17, 8:53 am, Lew <l...@lewscanon.com> wrote:
lightning wrote:
I found that standard jdbc [sic] api [sic] does not have a very convenie=
nt 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 representati=
on
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 signif=
icant
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).