Convert java.sql.Timestamp to java.sql.Timestamp in another timezone

Question

I am in need to manipulate on java.sql.Timestamp.

Input to the function is: Formatted DateTime in java.sql.Timestamp [Possible date formats are: MM/dd/yyyy hh:mm:ss aa, MM/dd/yyyy hh:mm:ss, MM/dd/yyyy hh:mm aa, MM/dd/yyyy HH:mm, MM/dd/yy hh:mm aa, MM/dd/yy HH:mm, MM/dd/yyyy, and some others]

Required Output: java.sql.Timestamp in another Timezone the same formatted DateTime as input

So basically I need to change timezone of the DateTime in java.sql.Timestamp

I have seen other posts, which mention to use JODA, but I can't use it due to some restrictions.

I have tried - to convert java.sql.Timestamp to java.date.Calendar, - then change the timezone, - then convert to it to date - format date to the same formatted datetime

See the code below:

Timestamp ts = "2012-06-20 18:22:42.0";  // I get this type of value from another function
Calendar cal = Calendar.getInstance();
cal.setTime(ts);
cal.add(Calendar.HOUR, -8);
String string = cal.getTime().toString();     // return value is in " DAY MMM dd hh:mm:ss PDT yyyy " format i.e. Wed Jun 20 10:22:42 PDT 2012
SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss");  // This could be any format required
Date date;
try {
   date = formatter.parse(string);             // I am getting exception here on parsing 
} catch (ParseException e1) {
   e1.printStackTrace();
}

Can anyone tell me what is wrong here, or is there any other way to manipulate on Timezone for java.sql.Timestamp ?

Thanks.

Answer 1

You are misunderstanding and abusing these classes.

Timestamp & Date have no time zone but UTC

manipulate on Timezone for java.sql.Timestamp

A java.sql.Timestamp is always a moment in UTC. No other time zone is involved, only UTC. Ditto for java.util.Date – always in UTC, no other time zone involved.

So your Question, as quoted above, does not make sense.

Timestamp & Date have no “format”

Neither Timestamp nor Date have a “format”. They use their own internally defined way to track the date-time. They are not strings, so they have no format. You can generate a String to represent their value in a particular format, but such a String is distinct and separate from the generating object.

java.time

You are using troublesome old date-time classes that wore supplanted years ago by the java.time classes.

Both Timestamp and Date are replaced by 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).

Your input is

String input = "2012-06-20 18:22:42.0" ;

That input is nearly compliant with standard ISO 8601 format. To comply fully, replace the SPACE in the middle with a T.

String input = "2012-06-20 18:22:42.0".replace( " " , "T" ) ;

Parse as a LocalDateTime because it lacks an indicator of offset-from-UTC or time zone.

LocalDateTime ldt = LocalDateTime.parse( input ) ;

A LocalDateTime, like your input string, does not represent a moment, is not a point on the timeline. Without the context of a time zone or offset-from-UTC, it has no real meaning. It represents only potential moments along a range of about 26-27 hours.

If you know the intended time zone, apply it to get a ZonedDateTime object.

ZoneId z = ZoneId.of( "Africa/Tunis" ) ;
ZonedDateTime zdt = ldt.atZone( z ) ;

As for the other formats you mentioned, your Question is not at all clear. Search Stack Overflow for DateTimeFormatter class to see many examples and discussions of generating/parsing strings with the java.time classes. But first, get clear on the crucial concept that strings are not the date-time objects, and the date-time objects are not strings.

Database

If you were using java.sql.Timestamp to exchange data with a database, no need for that class anymore. As of JDBC 4.2 and later, you can directly exchange java.time objects with your database.

myPreparedStatement.setObject( … , instant ) ;

…and…

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

---

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.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

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
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). 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.

Answer 2

I got it solved, I am putting code for reference.

Timestamp ts = "2012-06-20 18:22:42.0"; // input date in Timestamp format
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
Calendar cal = Calendar.getInstance();
cal.setTime(ts)
cal.add(Calendar.HOUR,-7); // Time different between UTC and PDT is -7 hours
String convertedCal = dateFormat.format(cal.getTime());  // This String is converted datetime
 /* Now convert String formatted DateTime to Timestamp*/
SimpleDateFormat formatFrom = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
try {    
    Date date = formatFrom.parse(convertedCal);
    Timestamp finalTS = new Timestamp(date.getTime()); // Final value in Timestamp: 2012-06-20 11:22:42.0
} catch (Exception e) {
    e.printStackTrace();            
}

Answer 3

Couldn't you simply:

1. Get original time in milliseconds
2. Convert timezone difference to milliseconds
3. Add or subtract the difference from the original time.
4. Create a new timestamp using the new time in milliseconds

Answer 4

you miss one argumment in formatter.parse http://docs.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html#parse(java.lang.String,%20java.text.ParsePosition)

Answer 5

Think of Timestamp as being a fixed point in time, disconnected from where on earth you happen to be looking at a clock.

If you want to display what's on the calendar/clock for a person at that instant in a particular time zone, you can set a calendar to that time zone and then associate your SimpleDateFormat to that calendar.

For example:

public void testFormat() throws Exception {
    Calendar pacific = Calendar.getInstance(TimeZone.getTimeZone("America/Los_Angeles"));
    Calendar atlantic = Calendar.getInstance(TimeZone.getTimeZone("America/New_York"));
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    Timestamp ts = new Timestamp(System.currentTimeMillis());
    sdf.setCalendar(pacific);
    System.out.println(sdf.format(ts));
    sdf.setCalendar(atlantic);
    System.out.println(sdf.format(ts));
}

My output was:

2012-06-25 20:27:12.506
2012-06-25 23:27:12.506