Is Java.Sql.Timestamp Timezone Specific

Is java.sql.Timestamp timezone specific?

Although it is not explicitly specified for setTimestamp(int parameterIndex, Timestamp x) drivers have to follow the rules established by the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) javadoc:

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom time zone. If no Calendar object is specified, the driver uses the default time zone, which is that of the virtual machine running the application.

When you call with setTimestamp(int parameterIndex, Timestamp x) the JDBC driver uses the time zone of the virtual machine to calculate the date and time of the timestamp in that time zone. This date and time is what is stored in the database, and if the database column does not store time zone information, then any information about the zone is lost (which means it is up to the application(s) using the database to use the same time zone consistently or come up with another scheme to discern timezone (ie store in a separate column).

For example: Your local time zone is GMT+2. You store "2012-12-25 10:00:00 UTC". The actual value stored in the database is "2012-12-25 12:00:00". You retrieve it again: you get it back again as "2012-12-25 10:00:00 UTC" (but only if you retrieve it using getTimestamp(..)), but when another application accesses the database in time zone GMT+0, it will retrieve the timestamp as "2012-12-25 12:00:00 UTC".

If you want to store it in a different timezone, then you need to use the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) with a Calendar instance in the required timezone. Just make sure you also use the equivalent getter with the same time zone when retrieving values (if you use a TIMESTAMP without timezone information in your database).

So, assuming you want to store the actual GMT timezone, you need to use:

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
stmt.setTimestamp(11, tsSchedStartTime, cal);

With JDBC 4.2 a compliant driver should support java.time.LocalDateTime (and java.time.LocalTime) for TIMESTAMP (and TIME) through get/set/updateObject. The java.time.Local* classes are without time zones, so no conversion needs to be applied (although that might open a new set of problems if your code did assume a specific time zone).

java.sql.Timestamp has a timezone

This appears to solve the problem. I'm guessing when it returns a LocalDateTime it does not use the timezone.

LocalDateTime localDT = timestamp.toLocalDateTime();
odt = localDT.atOffset(ZoneOffset.UTC);

updated with suggestion from @Ole V.V.

Does the timezone matter when parsing a Timestamp?

tl;dr

org.threeten.bp.OffsetDateTime odt = 
    OffsetDateTime.parse(
        "2012-02-12T09:08:13.123456-0400",
        org.threeten.bp.format.DateTimeFormatter.ofPattern( "yyyy-MM-dd'T'HH:mm:ssZ" )  // Specify pattern as workaround for Java 8 bug in failing to parse if optional colon is not present.
    )
;

Using java.time

Rather than receiving a String from your database, you should retrieve an object, a date-time object, specifically a java.time object.

The java.time classes supplant the troublesome old date-time classes including java.sql.Timestamp. If your JDBC driver supports JDBC 4.2 and later, you can pass and receive java.time objects directly.

Instant

The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction). So this is equivalent to java.sql.Timestamp including support for the six digits of microseconds of your input data, so no precision lost per the requirements of your Question.

Instant instant = myResultSet.getObject( … , Instant.class ) ;

instant.toString(): 2012-02-12T13:08:13.123456Z

ZonedDateTime

If you want to see that same moment through the lens of a particular region's wall-clock time, apply a ZoneId to get a ZonedDateTime object.

ZoneId z = ZoneId.of( "America/St_Thomas" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

zdt.toString(): 2012-02-12T09:08:13.123456-04:00[America/St_Thomas]

OffsetDateTime

As for your direct Question of how to make sense of the string 2012-02-12T09:08:13.123456-0400 as a date-time value, parse as an OffsetDateTime.

A time zone has a name in the format of continent/region, and represents a history of past, present, and future changes to a region’s offset caused by anomalies such as Daylight Saving Time (DST). We have clue as to the time zone with your string, so we use OffsetDateTime rather than ZonedDateTime.

OffsetDateTime odt = OffsetDateTime.parse( "2012-02-12T09:08:13.123456-0400" ) ;

Well, that line of code above should have worked, but in Java 8 there is a small bug in parsing the offset lacking the optional COLON character between the hours and minutes. So -04:00 in Java 8 will parse but not -0400. Bug fixed in Java 9. Your String is indeed compliant with the ISO 8601 standard for date-time formats used by default in the java.time classes. Tip: Generally best to always format your offsets with the colon, and both hours/minutes and with a padding zero – I've seen other protocols and libraries expect only such full format.

Until you move to Java 9, specify the formatting pattern explicitly rather than rely on the implicit default pattern, as a workaround for this bug.

OffsetDateTime odt = 
    OffsetDateTime.parse(
        "2012-02-12T09:08:13.123456-0400",
        DateTimeFormatter.ofPattern( "yyyy-MM-dd'T'HH:mm:ssZ" )  // Specify pattern as workaround for Java 8 bug in failing to parse if optional colon is not present.
    )
;

Converting

If your JDBC driver is not yet compliant with JDBC 4.2, retrieve a java.sql.Timestamp object, for use only briefly. Immediately convert to java.time using new methods added to the old date-time classes.

java.sql.Timestamp ts = myResultSet.getTimestamp( … ) ;
Instant instant = ts.toInstant();

Proceed to do your business logic in java.time classes. To send a date-time back to the database convert from Instant to java.sql.Timestamp.

myPreparedStatement.setTimestamp( … , java.sql.Timestamp.from( instant ) ) ;

Java 6 & 7

In Java 6 & 7, the above concepts still apply, but java.time is not built-in. Use the ThreeTen-Backport library instead. To obtain, see bullets below.

In Java 7, you cannot use JDBC 4.2 features. So we cannot directly access java.time objects from the database through the JDBC driver. As seen above, we must convert briefly into java.sql.Timestamp from Instant. Call the utility methods DateTimeUtils.toInstant(Timestamp sqlTimestamp) & DateTimeUtils.toSqlTimestamp(Instant instant).

java.sql.Timestamp ts = myResultSet.getTimestamp( … ) ;
Instant instant = DateTimeUtils.toInstant( ts ) ;

…and…

java.sql.Timestamp ts = DateTimeUtils.toSqlTimestamp( instant ) ;
myPreparedStatement.setTimestamp( … , ts ) ;

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, and later
  
  
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  
  
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  
  
  • The ThreeTenABP project adapts ThreeTen-Backport (mentioned above) for Android specifically.
  • See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

java.sql.Timestamp: changing timezone of Timestamp

java.sql.Timestamp objects don't have time zones - they are instants in time, like java.util.Date.

If you're thinking of them as being in a particular time zone, you may either be getting confused due to misleading output (e.g. something automatically converting that instant into a local time using a default time zone) or you may have created the data in an inappropriate way. The answer you need will depend on the details of your situation.

For example, if you just want to display a Timestamp value in a particular time zone, you can use SimpleDateFormat, set the time zone appropriately, and just format the Timestamp (as it extends Date). I don't believe that will allow you to display as much precision as the timestamp stores internally, but that may not be a problem for you.

If your data has been created incorrectly then there may or may not be a way to "correct" it - there may be some ambiguities due to daylight saving time changes, for example. However, the more we know about it the better we'll be able to help you.

java.sql.Date and Time Timezone differs

1. java.sql.Date is an extremely unfortunate API design messup. That class extends java.util.Date, and that class is a lie. It does not represent a date at all (check the source code if you are understandably skeptical). It represent a moment in time, devoid of any timezone information, based on milliseconds since UTC new year's 1970. Anything you care to coerce from a Date object that isn't a very large number or a direct swap to a more appropriate type that doesn't lie (such as java.time.Instant) is therefore suspect: It is picking a timezone implicitly and mixing that in, in order to provide you your answer. This is why most of Date's methods, such as .getYear(), are marked deprecated: In the java core libs, a deprecation marker usually doesn't actually mean "This will be removed soon", it actually means: "This is fundamentally broken and you should never call this method". (See: Thread.stop).

2. Nevertheless JDBC API (what you use to talk to DBs) was built on top of this; java.sql.Date as well as java.sql.Timestamp extend java.util.Date and thereby inherit the messup. This means date handling in this fashion will convert timestamps in the DB that have full timezone info into timezoneless constructs, and then java-side you can add new timezones, which is a bad way of doing things.

3. Unfortunately date/time is extremely complicated (see below) and databases have wildly different ways of storing it; usually multiple slightly different date/time types, such as 'TIMESTAMP', 'TIME WITH TIME ZONE', etcetera.

4. Because of this there is no unique advice that works regardless of context: The right answer depends on your JDBC driver version, DB engine, DB engine version, and needs. This means the best approach is generally to first understand the fundamentals so that you can adapt and figure out what would work best for your specific situation.

5. java.util.Calendar is even worse. Again a lie (it represents time. Not a calendar!), the API is extremely badly designed (it is very non-java-like). There is a reason this second try at date/time API resulted in yet another date/time library (java.time): It's bad. Don't use it.

So, let me try to explain the basics:

You like to wake up at 8 in the morning. It's noon and you check your phone. It says 'the next alarm will ring in 20 hours'. You now hop onto a concorde at Schiphol Airport in Amsterdam, and fly west, to New York. The flight takes 3 hours. When you land, you check your phone. Should it say 'the next alarm will ring in 17 hours' (3 hours of flight time have passed), or should it say: 'the next alarm will ring in 23 hours' (you actually land at 9 in the morning in New York time because its 6 hours earlier there relative to Amsterdam, so it's 23 hours until 8 o' clock in the morning local time). The right answer is presumably: 23 hours. This requires the concept of 'local time': A point in time stated in terms of years, months, days, hours, minutes, and seconds - but no timezone, and not even 'please assume a timezone for me', but the concept '... where-ever you are now'.

Before you jumped in the plane, you made an appointment at a barber's in Amsterdam for when you return. You made it for March 8th, 2022, at 12:00. When you check your phone it reads: "365 days, 0 hours, 0 minutes, and 0 seconds" as you made the appointment. If you fly to new york, that should now read "364 days, 21 hours, 0 minutes, and 0 seconds": The fact that you're currently in new york has no bearing on the situation whatsoever. You'd think that time-as-millis-since-UTC should thus suffice, but no it does not: Imagine that the netherlands abolishes daylight savings time (crazy idea? No, quite likely actually). The very instant that the gavel hits the desk and the law is adopted that The Netherlands would switch to summer time in 2021, and then stay there forever more, that very moment? Your phone's indicator for 'time until barber appointment' should instantly increment by 1 hour (or it is decrement?). Because that barber appointment isn't going to reschedule itself to be at 11:00 or 13:00.

During your flight, you snapped a pic of the tulip fields before the plane crossed the atlantic. The timestamp of this picture is yet another concept of time: If, somehow, the netherlands decides to apply the timezone change retroactively, then the timestamp in terms of 'this picture was taken on 2021, march 8th, 12:05, Amsterdam time' should instantly pop and now read 13:05 or 11:05 instead. In this way it is unlike the barber appointment.

Before this question can be answered, you need to figure out which of the 3 different concepts of time your situation boils down to.

Only the java.time package fully covers this all. Respectively:

• Alarm clock times are represented by LocalDate, LocalTime, and LocalDateTime.

• Appointment times are represented by ZonedDateTime.

• When did I make the picture times are represented by Instant.

The java.sql.Date type is most equivalent to Instant, which is bizarre, because you'd think that this is more java.sql.Timestamp's bailiwick.

Pragmatics, how to get the job done

Your first stop is to fully understand your DB engine's data types. For example, in postgres:

---