 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / General / March 2008the best practice to deal with datetime in mysql using jdbc?
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 ? > 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: > [quoted text clipped - 8 lines] > > 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.  SignatureLew > > I found that standard jdbc [sic] api [sic] does not have a very convenient way to > > deal with datetime. [quoted text clipped - 31 lines] > 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). > 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 [quoted text clipped - 13 lines] > 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. > 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); } } 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. SignatureWojtek :-) 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. 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. SignatureLew > 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. [quoted text clipped - 7 lines] > 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 [quoted text clipped - 4 lines] >inserted, the incremented database timestamp value is inserted in the >timestamp column. Arne Lew wrote: >> only in MySQL, AFAIK. Certainly not in any >> other DBMS that claims SQL compliance. > SQLServer 2005 BOL: Well, shiver me timbers. I stand corrected, sort of. <http://msdn2.microsoft.com/en-us/library/ms182776.aspx> >> The Transact-SQL timestamp data type is different from the timestamp data type >> defined in the SQL-2003 standard. The SQL-2003 timestamp data type is >> equivalent to the Transact-SQL datetime data type. We see ever more strongly as you wrote: > 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> SignatureLew > 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. Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.