Hi,

I'm trying to map 'logoutTime' java.sql.Timestamp property to SQL TIMESTAMP, but SchemaExport keeps generating DATETIME instead. How can I generate TIMESTAMP?

thanks,
-nikita

Hibernate version:
3.1
Mapping documents:
<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping
>
<class
name="net.eviltwinstudios.common.beans.UserSession"
table="user_sessions"
>

<id
name="id"
column="id"
type="java.lang.String"
>
<generator class="assigned">
</generator>
</id>

<version
name="hibernateVersion"
column="hibernateVersion"
type="java.lang.Integer"
unsaved-value="null"
/>

<property
name="loginTime"
type="java.sql.Timestamp"
update="true"
insert="true"
column="loginTime"
/>

<property
name="logoutTime"
type="timestamp"
update="true"
insert="true"
column="logoutTime"
/>
</class>

</hibernate-mapping>

POJO Source :


Full stack trace of any exception that occurs:

Name and version of the database you are using:
Mysql 5.0 InnoDB
The generated SQL (show_sql=true):
DROP TABLE IF EXISTS `evilsite`.`user_sessions`;
CREATE TABLE `evilsite`.`user_sessions` (
`id` varchar(255) NOT NULL,
`hibernateVersion` int(11) NOT NULL,
`loginTime` datetime default NULL,
`logoutTime` datetime default NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Are you sure you don't want datetime? It's pretty rare to have both a version and a timestamp column, and I've never heard of a table with two timestamp columns like you've got. Hibernate only supports one timestamp per class. Look at the <timestamp> element, ref docs section 5.1.8.

Just so that you're clear: most DBMSs, including (afaik) MySQL, have broken from SQL standards and use datetime as the SQL type for recording dates and/or times. timestamp is used as the SQL type for timestamping a row's change: it's just a row-version type, usually a long integer value, much like java's Date type (a time since an epoch).

If you're confident that timestamp is the correct SQL type, then you can edit org/hibernate/dialect/MySQLDialect.java, and change the lineReplace the datetime with timestamp. Then rebuild the jar. Do verify that the timestamp SQL type does what you expect it to do, first.

Verified that timestamp is significantly different than datetime on MySQL, though it's not a simple int as it is on some DBMSs. From the MySQL manual:

• DATETIME
  
  A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers.
  
• TIMESTAMP
  
  A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037.
  
  A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. By default, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value. Variations on automatic initialization and update properties are described in Section 11.3.1.1, “TIMESTAMP Properties as of MySQL 4.1”.

So that's the same as the <timestamp> element, but hibernate supports only one of those per mapping.

Agreed that TIMESTAMP in mySQL behaves differently from DATETIME, that's why it is a different mySQL type. Apparently in JIRA there was the same issue in Oracle and it has been fixed. The Hibernate documentation states that TIMESTAMP maps to the SQL TIMESTAMP type. So is it something that should be fixed in Hibernate or in the documentation?

I have the following mapping:
<hibernate-mapping>
<class name="events.Event" table="EVENTS">
<id name="id" column="EVENT_ID">
<generator class="native"/>
</id>
<property name="date" type="timestamp" column="EVENT_DATE"/>
<property name="title"/>
</class>
</hibernate-mapping>

and here the DDL generated from the log:

[java] 17:09:25,843 INFO Environment:479 - Hibernate 3.1.2
...
[java] 17:09:27,626 DEBUG SchemaExport:296 - create table EVENTS (EVENT_ID bigint not null auto_increment, EVENT_DATE datetime, title v
archar(255), primary key (EVENT_ID)) type=InnoDB

This is not correct, the TIMESTAMP is the first one on the table and it still is not mapped to a TIMESTAMP type.

I am using Hibernate 3.1.2, not sure it has been fixed in the latest version, I can't upgrade right now.

Should this be a JIRA issue?

Thanks

Fred

Hibernate type="timestamp" is MySQL DATETIME, nothing to fix. If you want type="mysql_timestamp", write a UserType.

Not sure I understand the meaning of the reply... Maybe you can expand a bit? Hibernate TIMESTAMP maps to my SQL-DATETIME because...

The Hibernate documentation states:

5.2.2. Basic value types
The built-in basic mapping types may be roughly categorized into
integer, long, short, float, double, character, byte, boolean, yes_no, true_false
Type mappings from Java primitives or wrapper classes to appropriate (vendor-specific) SQL column
types. boolean, yes_no and true_false are all alternative encodings for a Java boolean or
java.lang.Boolean.
string
A type mapping from java.lang.String to VARCHAR (or Oracle VARCHAR2).

date, time, timestamp
Type mappings from java.util.Date and its subclasses to SQL types DATE, TIME and TIMESTAMP (or equivalent).

So how do we get the "TIMESTAMP" type in Hibernate to map to the "TIMESTAMP" in my SQL? Or should the documentation state that the Hibernate TIMESTAMP maps to DATETIME?

Thanks

Not sure I understand the meaning of the reply... Maybe you can expand a bit? Hibernate TIMESTAMP maps to my SQL-DATETIME because...

The Hibernate documentation states:

5.2.2. Basic value types
The built-in basic mapping types may be roughly categorized into
integer, long, short, float, double, character, byte, boolean, yes_no, true_false
Type mappings from Java primitives or wrapper classes to appropriate (vendor-specific) SQL column
types. boolean, yes_no and true_false are all alternative encodings for a Java boolean or
java.lang.Boolean.
string
A type mapping from java.lang.String to VARCHAR (or Oracle VARCHAR2).

date, time, timestamp
Type mappings from java.util.Date and its subclasses to SQL types DATE, TIME and TIMESTAMP (or equivalent).

So how do we get the "TIMESTAMP" type in Hibernate to map to the "TIMESTAMP" in my SQL? Or should the documentation state that the Hibernate TIMESTAMP maps to DATETIME?

Thanks

Why was this question never answered?

How can I tell Hibernate that it should map to MySQL TIMESTAMP instead of a DATETIME?


Thanks in anticipation.