2015-06-18 20:45 GMT+02:00 Garret Wilson <[email protected]>: > On 6/18/2015 11:25 AM, Lukas Eder wrote: > > Garret. No one said that date, time and timezones are easy :) > > > I'm fully versed in date, time, and time zones. I have co-written a > complex time scheduling program which routed appointments of a customer in > one time zone to consultants based upon their local available times in > various time zones around the world. >
I wish my mobile phone's calendar was written by someone like you :) > The System.getCurrentTimeMillis() *is* in fact easy. It has a > specification. It is internally tied to UTC. It is represented by > java.util.Date and java.time.Instant (Java 8) and org.joda.time.Instant. It > should *always be identical regardless of what time zone you are in*. It > is only when you want to convert that absolute instant to some local date > that you run into problems. > ... or when you toString() / "fromString()" it, which is something rather confusing for many people, I suspect. > But converting to a local time I can handle. What I want to make sure is > that my Date/Instant is stored and retrieved with no > modifications---independent of the time zone of my database server or my > JVM or whatever. This Date/Instant is at its basis a long integer > value---and it never, ever, ever changes!! > Then, use TIMESTAMP WITH TIME ZONE, and implement your data type Binding. We'll probably finally ship this out of the box in jOOQ 3.7. > Lukas, I'll go read your extensive references, which look entertaining. :) > But in the end, all I want to know is that I the following JUnit > pseudo-code will work: > > final long now=System.getCurrentTimeMillis(); > final Timestamp before=new Timestamp(now); > ... > //create record > jooqRecord.setTimestamp(before); > ... > //change JVM time zone to "foo" > //change database time zone to "bar" > //retrieve record > final Timestamp after=jooqRecord.getTimestamp(); > assertEqual(before, after); //a timestamp should never, ever change!! > > It will if you're using TIMESTAMP WITH TIME ZONE. It won't if you're using TIMESTAMP (WITHOUT TIME ZONE) However, the linked issue (https://github.com/jOOQ/jOOQ/issues/2738) still prevails, as jOOQ currently doesn't explicitly support TIMESTAMP WITH TIME ZONE, thus inlining the timestamp (without timezone) will again produce wrong results, no matter what data type you're using. I apologize in advance if you already answered that question---I'm going to > carefully review what you wrote, because you put a lot of time into writing > it. But the last part, "... if you're not operating across time zones, > you're fine ...", makes me very wary. ;) > You should be wary. Most people are oblivious of this issue. Most software is written with the SQL TIMESTAMP data type. And that's mostly fine, as the actual machine writing/reading timestamps usually doesn't change timezones ever. What timezone the end user is in doesn't really matter to this discussion, as that most often takes place on a different layer of the application. But it's certainly a good reason to be wary. The early Java folks made a lot of interesting default choices. Like the default locale, timezone, encoding, etc... The System.getCurrentTimeMillis() *is* in fact easy. Not to say that there aren't some interesting gotchas, but those aren't > relevant to the issue I'm addressing here. The point is that this values > doesn't change across time zones. I like it as well. It can (almost) never go wrong. Looking forward to your further findings. Lukas -- 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.
