SEO

Hi everyone! I'm having an problem when I try to insert data into a
timestamp field, let me try to explain what is going in on.

I developed my application at my local machine and at this machine
everything is working as it should, but when I deployed the application into
the production server, every time I save an timestamp it saves it one hour
before. The method that is inserting the data is pasted below:

############################
public int inserir(CompromissoVO vo, Connection conn) throws Exception {
PreparedStatement stp = null;

int chave = 0;

final String sql = "INSERT INTO "
+ ESQUEMA
+ ".compromisso (com_id, com_criacao, com_data, "
+ "com_titulo, com_confidencial, tco_id, com_agendamento,
aud_id, "
+ "via_id, soe_id)"
+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

try {

chave = ConnectionManager.getSequence("comp_seq");
int i = 1;

stp = conn.prepareStatement(sql);

stp.setInt(i++, chave);
Date agora = new Date();
stp.setTimestamp(i++, new Timestamp(agora.getTime()));

SimpleDateFormat sd = new SimpleDateFormat("dd/MM/yyyy
HH:mm:ss");
log.debug("Inserir - Data compromisso: " + sd.format(vo.getData
()));
stp.setTimestamp(i++, new Timestamp(vo.getData().getTime()));
stp.setString(i++, vo.getTitulo());
stp.setString(i++, vo.getConfidencial());
stp.setInt(i++, vo.getTipo().getCodigo());
stp.setString(i++, vo.getTipoAgendamento());
if (vo.getAudiencia() > 0){
stp.setInt(i++, vo.getAudiencia());
} else {
stp.setNull(i++, Types.INTEGER);
}
if (vo.getViagem() > 0){
stp.setInt(i++, vo.getViagem());
} else {
stp.setNull(i++, Types.INTEGER);
}
if (vo.getSolenidade() > 0){
stp.setInt(i++, vo.getSolenidade());
} else {
stp.setNull(i++, Types.INTEGER);
}

stp.execute();

} catch (NegocioException ne) {
throw ne;
} catch (Exception e) {
log.debug("sql: " + sql);
throw new NegocioException("error.inserir.compromisso", e);
}

return chave;
}
############################

The vo.getData() return the date which was typed by the user, I logged the
date to see if it was right and I got it was, but when I look at the
database the date is one hour before.

Log: 2006-01-25 16:05:08,877 -> DEBUG (CompromissoDAO.java:140) - Alterar -
Data compromisso: 12/01/2006 12:00:00
The data which was saved at the database: 2006-01-12 11:00:00

My table script is:

CREATE TABLE compromisso
(
com_id numeric(6) NOT NULL,
com_criacao timestamp NOT NULL,
com_data timestamp NOT NULL,
com_titulo varchar(200) NOT NULL,
com_confidencial varchar(400),
com_agendamento char(1),
tco_id numeric(6) NOT NULL,
aud_id numeric(6),
via_id numeric(6),
soe_id numeric(6),
CONSTRAINT compromisso_pk PRIMARY KEY (com_id),
CONSTRAINT com_tipocompromisso_fk FOREIGN KEY (tco_id) REFERENCES
compromisso_tipo (tco_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT compromisso_audiencia_fk FOREIGN KEY (aud_id) REFERENCES
registro_audiencia (aud_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT compromisso_solenidade_fk FOREIGN KEY (soe_id) REFERENCES
registro_solenidade (soe_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT compromisso_viagem_fk FOREIGN KEY (via_id) REFERENCES
registro_viagem (via_id) ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE compromisso OWNER TO uzzi;
GRANT ALL ON TABLE compromisso TO uzzi;


Does any one have an idea what is going in on?

Thanks,

--
Lucas Sanabio
-----------------------------------------------------------------------------
Sun Certified Programmer for the Java 2 Platform
Consultor Java - Marx Tecnologia
Email: lucas@marx.com.br
Cel: (61) 8402-8876

Lucas Sanabio wrote:
> Hi everyone! I'm having an problem when I try to insert data into a
> timestamp field, let me try to explain what is going in on.
>
> I developed my application at my local machine and at this machine
> everything is working as it should, but when I deployed the application
> into the production server, every time I save an timestamp it saves it
> one hour before. The method that is inserting the data is pasted below:

What is the actual value inserted?

I'd guess that your client and server timezone do not match, so that
(for example) 2006-01-12 12:00:00 in the client's default timezone is
2006-01-12 12:00:00 +0100; if the server timezone is +0200 then this
will be represented as 2006-01-12 13:00:00 +0200 (which is the same
instant in time).

If the actual value inserted is wrong, the first thing I would do is to
try a recent driver as I did a number of time-and-date-related fixes
relatively recently. What driver version are you currently using?

-O

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match