Thread: timestamp issues

I have a machine physically located in the USA, but
the OS (Linux) time is set to New Zealand time. So if
I execute "date" on the command prompt, I get the New
Zealand time.

I have java classes converting all time to timezone
"Europe/Amsterdam".

I have tables with "TIMESTAMP" fields (no timezone).

If I do this (through a JDBC call):

"UPDATE users SET last_login=current_timestamp"

then the database explorer shows the times in New
Zealand time, as expected. But my the Java classes see
the time (after converting the time to the Amsterdam
timezone) as 19 hours ahead of Amsterdam time. (19
hours is, I think, the difference between the USA and
New Zealand times?). I also tried with LOCALTIMESTAMP
instead of current_timestamp, same result.

If, however, I change the update to this:

"UPDATE users SET last_login=?"
with
prepareStatement.setTimestamp(1, new Timestamp(
    System.currentTimeMillis());

Then the java classes see the time correct as it
should.

Why is this? Why does System.currentTimeMillis() and
current_time give different times?

Charl Gerber wrote:

> I have tables with "TIMESTAMP" fields (no timezone).
>
> If I do this (through a JDBC call):
>
> "UPDATE users SET last_login=current_timestamp"
>
> then the database explorer shows the times in New
> Zealand time, as expected. But my the Java classes see
> the time (after converting the time to the Amsterdam
> timezone) as 19 hours ahead of Amsterdam time. (19
> hours is, I think, the difference between the USA and
> New Zealand times?). I also tried with LOCALTIMESTAMP
> instead of current_timestamp, same result.

When you use getTimestamp() on a timestamp-without-timezone column, the
JDBC driver treats it as a timestamp in the (Java client's) default
timezone, a US timezone in your case I believe.

In theory you can pass an appropriate Calendar object to getTimestamp()
to tell the driver to treat it as a timestamp in that timezone if no
timezone information is associated with the timestamp column. The code
looks a bit hairy though, I'd be interested to know if this actually works.

-O