I'm not sure I understand. TIMESTAMP without timezone would always give
me a local time, right? I want to store and retrieve an absolute time
value---the equivalent to System.currentTimeMillis().
Let me break it down like this: If I construct a
Timestamp(System.currentTimeMillis()), that gives me an absolute
computer time. Is there no type I can use in PostgreSQL so that if I
set(timestamp) a value using jOOQ, and then timestamp=get() on another
computer, I can be guaranteed that I will get back the original
System.currentTimeMillis()? This is the simplest and most fundamental
time representation that exists.
Are you saying that my time zone setting on my JVM or my database will
influence the value I retrieve? But that is
inappropriate---System.currentTimeMillis() is an absolute time value
that should not be modified by time zones (because it is tied to UTC).
I find that absurd!! We have a database that supposedly is one of the
most advanced and most standards-compliant, and we can't rely on it to
give us back the same absolute time value?? Or is the problem with jOOQ?
Surely I am misinterpreting the situation... (Like I said, I'm not an
SQL expert.)
Garret
On 6/17/2015 11:52 PM, Lukas Eder wrote:
Hi Garret,
The problem with the TIMESTAMP WITH TIMEZONE data type is that it is
supported only in JDBC 4.2 onwards (i.e. Java 8). jOOQ 3.7 will start
adding formal support for Java 8, so it will be good to finally solve
the "timezone" issue thoroughly. Right now, jOOQ doesn't do anything
"special", so the behaviour will match that of your JDBC driver (which
is usually to take your local timezone).
In the meantime, you could get timezones right by implementing your
own data type binding (see
http://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings).
However, if you don't absolutely *need* to have different timezones in
your database, you're usually best off avoiding them and using the
older TIMESTAMP WITHOUT TIMEZONE (or just TIMESTAMP) data type. Once
you do add support for timezones (apart from display in the UI),
you're opening pandora's box on various levels in your application.
2015-06-17 19:15 GMT+02:00 Garret Wilson <[email protected]
<mailto:[email protected]>>:
For example, here is one worry I get from the PostgreSQL
documentation
<http://www.postgresql.org/docs/9.4/static/datatype-datetime.html>:
When a timestamp with time zone value is output, it is always
converted from UTC to the current timezone zone, and displayed as
local time in that zone. To see the time in another time zone,
either change timezone or use the AT TIME ZONE construct (see
Section 9.9.3
<http://www.postgresql.org/docs/9.4/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT>).
I interpret this to mean that, unless jOOQ uses AT TIME ZONE
(specifying UTC) when querying the value, the value, even though
/stored/ in terms of UTC, would be converted to some arbitrary
(depending on the current timezone zone) time zone before being
converted to an absolute time value (analogous to Instant). So
does jOOQ do the appropriate conversions to ensure that the
Timestamp I store and the Timestamp I retrieve will always contain
the same absolute time value, regardless of the system or database
timezone in use?
--
You received this message because you are subscribed to the Google
Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to [email protected]
<mailto:[email protected]>.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to a topic in the
Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/jooq-user/TebxZ7dxzn8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
[email protected]
<mailto:[email protected]>.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups "jOOQ User
Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
