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

Discussion in 'Java' started by lightning, Mar 17, 2008.

  1. lightning

    lightning Guest

    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 ?
     
    lightning, Mar 17, 2008
    #1
    1. Advertising

  2. lightning

    Lew Guest

    lightning wrote:
    > 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.

    --
    Lew
     
    Lew, Mar 17, 2008
    #2
    1. Advertising

  3. On Mar 17, 8:53 am, Lew <> wrote:
    > lightning wrote:
    > > 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).
     
    Owen Jacobson, Mar 17, 2008
    #3
  4. lightning

    Mark Space Guest

    lightning wrote:
    > 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.
     
    Mark Space, Mar 17, 2008
    #4
  5. lightning

    Wojtek Guest

    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.

    --
    Wojtek :)
     
    Wojtek, Mar 17, 2008
    #5
  6. lightning

    Arne Vajhøj Guest

    Owen Jacobson wrote:
    > 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.
     
    Arne Vajhøj, Mar 18, 2008
    #6
  7. lightning

    Lew Guest

    Lew wrote :
    >> You'd store it as a TIMESTAMP


    Wojtek 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.

    --
    Lew
     
    Lew, Mar 18, 2008
    #7
  8. Lew wrote:
    > 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
     
    Arne Vajhøj, Mar 18, 2008
    #8
  9. lightning

    Lew Guest

    Lew wrote:
    >> only in MySQL, AFAIK. Certainly not in any
    >> other DBMS that claims SQL compliance.


    Arne Vajhøj wrote:
    > 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>

    --
    Lew
     
    Lew, Mar 18, 2008
    #9
  10. lightning

    lightning Guest

    On Mar 18, 8:32 am, Arne Vajhøj <> wrote:

    > 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);
    }

    }
     
    lightning, Mar 19, 2008
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Christos TZOTZIOY Georgiou
    Replies:
    3
    Views:
    711
    Christos TZOTZIOY Georgiou
    Sep 13, 2003
  2. Tim Peters
    Replies:
    0
    Views:
    537
    Tim Peters
    Sep 9, 2003
  3. mp
    Replies:
    1
    Views:
    392
    John Machin
    Jul 28, 2006
  4. Martin
    Replies:
    0
    Views:
    338
    Martin
    Dec 27, 2008
  5. Replies:
    2
    Views:
    762
    M.-A. Lemburg
    Jan 6, 2009
Loading...

Share This Page