Garret. No one said that date, time and timezones are easy :) There's an amusing piece here, which I recommend reading: http://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time
And when that's not enough, read also: http://infiniteundo.com/post/25509354022/more-falsehoods-programmers-believe-about-time I like the bit about "Unix time is the number of seconds since Jan 1st 1970."... unix time doesn't have a way to represent leap seconds ;) Back to JDBC. Here's an interesting piece by Mark Rotteveel, the Jaybird developer (Firebird JDBC driver): http://stackoverflow.com/a/14070771/521799 Mark's explanation can be observed as follows: Connection c = getConnection(); try (PreparedStatement ps = c.prepareStatement( "select" + " ?::timestamp," + " ?::timestamp," + " ?::timestamp with time zone," + " ?::timestamp with time zone" )) { ps.setTimestamp(1, new Timestamp(0)); ps.setTimestamp(2, new Timestamp(0), Calendar.getInstance(TimeZone.getTimeZone("UTC"))); ps.setTimestamp(3, new Timestamp(0)); ps.setTimestamp(4, new Timestamp(0), Calendar.getInstance(TimeZone.getTimeZone("UTC"))); try (ResultSet rs = ps.executeQuery()) { rs.next(); System.out.println(rs.getTimestamp(1) + " / " + rs.getTimestamp(1).getTime()); System.out.println(rs.getTimestamp(2, Calendar.getInstance(TimeZone.getTimeZone("UTC"))) + " / " + rs.getTimestamp(2, Calendar.getInstance(TimeZone.getTimeZone("UTC"))).getTime()); System.out.println(rs.getTimestamp(3) + " / " + rs.getTimestamp(3).getTime()); System.out.println(rs.getTimestamp(4, Calendar.getInstance(TimeZone.getTimeZone("UTC"))) + " / " + rs.getTimestamp(4, Calendar.getInstance(TimeZone.getTimeZone("UTC"))).getTime()); } } The above program uses all permutations of using timezones and not using timezones in Java and in the DB, and the output is always the same: 1970-01-01 01:00:00.0 / 0 1970-01-01 01:00:00.0 / 0 1970-01-01 01:00:00.0 / 0 1970-01-01 01:00:00.0 / 0 As you can see, in each case, the UTC timestamp 0 was correctly stored and retrieved from the database. My own locale is CET / CEST, which was UTC+1 at Epoch, which is what is getting output on Timestamp.toString(). Things get interesting when you use timestamp literals, both in SQL and/or in Java. If you replace the bind variables as such: ps.setTimestamp(1, Timestamp.valueOf("1970-01-01 00:00:00")); ps.setTimestamp(2, Timestamp.valueOf("1970-01-01 00:00:00"), Calendar.getInstance(TimeZone.getTimeZone("UTC"))); ps.setTimestamp(3, Timestamp.valueOf("1970-01-01 00:00:00")); ps.setTimestamp(4, Timestamp.valueOf("1970-01-01 00:00:00"), Calendar.getInstance(TimeZone.getTimeZone("UTC"))); This is what I'm getting on my machine 1970-01-01 00:00:00.0 / -3600000 1970-01-01 00:00:00.0 / -3600000 1970-01-01 00:00:00.0 / -3600000 1970-01-01 00:00:00.0 / -3600000 I.e. not Epoch, but the timestamp literal that I sent to the server in the first place. Observe that the four combinations of binding / fetching still always produce the same timestamp. Let's see what happens if the session writing to the database uses a different timezone (let's assume you're in PST) than the session fetching from the database (I'm using again CET or UTC). I'm running this program: try (PreparedStatement ps = c.prepareStatement( "select" + " ?::timestamp," + " ?::timestamp," + " ?::timestamp with time zone," + " ?::timestamp with time zone" )) { ps.setTimestamp(1, new Timestamp(0), Calendar.getInstance(TimeZone.getTimeZone("PST"))); ps.setTimestamp(2, new Timestamp(0), Calendar.getInstance(TimeZone.getTimeZone("PST"))); ps.setTimestamp(3, new Timestamp(0), Calendar.getInstance(TimeZone.getTimeZone("PST"))); ps.setTimestamp(4, new Timestamp(0), Calendar.getInstance(TimeZone.getTimeZone("PST"))); try (ResultSet rs = ps.executeQuery()) { rs.next(); System.out.println(rs.getTimestamp(1) + " / " + rs.getTimestamp(1).getTime()); System.out.println(rs.getTimestamp(2, Calendar.getInstance(TimeZone.getTimeZone("UTC"))) + " / " + rs.getTimestamp(2, Calendar.getInstance(TimeZone.getTimeZone("UTC"))).getTime()); System.out.println(rs.getTimestamp(3) + " / " + rs.getTimestamp(3).getTime()); System.out.println(rs.getTimestamp(4, Calendar.getInstance(TimeZone.getTimeZone("UTC"))) + " / " + rs.getTimestamp(4, Calendar.getInstance(TimeZone.getTimeZone("UTC"))).getTime()); } } It yields this output: 1969-12-31 16:00:00.0 / -32400000 1969-12-31 17:00:00.0 / -28800000 1970-01-01 01:00:00.0 / 0 1970-01-01 01:00:00.0 / 0 The first timestamp was Epoch stored as PST (16:00), then the timezone information was removed by the database, which turned Epoch into the local time you had at Epoch (-28800 seconds / -8h). Now, when I'm fetching this time from my own timezone CET, I will still get the local time that you had stored (16:00), but in my timezone, this is no longer -28800 seconds, but -32400 seconds (-9h). Quirky enough? When we use the TIMESTAMP WITH TIME ZONE data type in the database, the timezone is maintained (PST), and when I fetch the Timestamp value, no matter if using CET or UTC, I will still get Epoch, which was safely stored to the database. *TL;DR:* If the UTC timestamp matters to you, use TIMESTAMP WITH TIMEZONE, but you'll have to implement your own data type Binding ( http://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings), because jOOQ currently doesn't support that data type. Once you use your own data type Binding, you can also use Java 8's time API, which better represent these different types than java.sql.Timestamp + the ugly Calendar. If the local time matters to you, or if you're not operating across time zones, you're fine using TIMESTAMP and jOOQ's Field<Timestamp>. Cheers, Lukas 2015-06-18 15:59 GMT+02:00 Garret Wilson <[email protected]>: > 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]>: > >> 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]. >> 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]. > 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. > -- 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.
