3

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.

Improve this question
3
  • I am getting java.text.ParseException: Unparseable date: "Wed Jun 20 10:22:42 PDT 2012" Commented Jun 26, 2012 at 20:55
  • 2
    So there you have it, format is completely wrong no? Commented Jun 26, 2012 at 20:57
  • 1
    A java.sql.Timestamp doesn't have a timezone, so you can't change it. Commented Apr 1, 2014 at 11:48

5 Answers 5

Reset to default
5

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?

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thank you for clarification and detailed description. FYI, Indeed this question is very old: Year 2012. at that time Java SE 7 was the latest released version.
The above helped me figure out a solution: java.sql.Timestamp is a point in time of UTC. For SQL server (2008 and greater) the following can be used to return the current Timezone Offset in minutes datepart(tzoffset,SYSDATETIMEOFFSET()) I then converted the java.sql.Timestamp to a Joda DateTime object and issued a .plusMinutes of the offset value returned in the sql statement. Because of Daylight savings, I returned the timezone offset on every query.
Neither Postgres nor Mysql don't support Instant. Are there drivers which do?
@andreoss Supporting Instant in any JDBC driver would be utterly trivial: OffsetDateTime#toInstant and Instant#atOffset( ZoneOffset.UTC ). That why it makes no sense at all for the JDBC 4.2 team to have required support for OffsetDateTime but not Instant.
1

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
Improve this answer

3 Comments

Hey @Phatfingers, I tried your solution, it seems that I am on right track, but the thing is I already have formatted Timestamp displaying time in UTC. I need to convert to different timezones. In your code sample, on the line sdf.setCalendar(pacific); it is not converting time to PST, because my development timezone is 'pasific'. What I need is to set that retrieved 'ts' to UTC and then convert it to different timezones; but I am not able to achieve it. Is there any way to mention that value in 'ts' is in UTC. and then convert to required timezones?
The key is to treat your Timestamp as only ever representing UTC, and to treat the display of a date and time as a presentation function that incorporates time zone. Ignore the fact the Timestamp has a toString() method that uses your system's time zone. Under the hood, it's always tracking time in UTC and has no real concept of time zone built in. You don't "convert" your Timestamp to one time zone or another, rather, you format the output of the Timestamp using one time zone or another.
@sankethm7 if your system's time zone is PST and the native toString() output of your Timestamp is appears to be a valid UTC time, then you may have inadvertently adjusted the value of the Timestamp to be offset by 8 hours from the correct time. You wouldn't want that.
1

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();            
}
Improve this answer

2 Comments

Please explain what you did. Now the only way is to compare code in question and answer. Also please accept the answer that you consider "solving" your problem. You can accept own answer as well.
For future readers, this solution doesn't take daylight savings into account or leap years. A more complete solution would use the timezone and calendar objects described in other answers.
0

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
Improve this answer

1 Comment

I already have formatted time in Timestamp type and it is actually UTC time. I need to convert to different timezones and return as the same format and in Timestamp type. So I don't think calculating in milliseconds is a good way to convert time in different timezones. I am keeping it as my last option.
0

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)

Improve this answer

2 Comments

isn't a single argument parse inherited from java.text.DateFormat?
@Sebastian, I tried using parse(string, parsePosition). But it returns null.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.