How to retrieve oracle Timestamp column in expected timezone in Java?

Question

I have a timestamp column in oracle table. While storing time I store it in UTC in this column. For retrieving this time stamp I am using Spring's JdbcTemplate while returns object of type TimeStamp.

I want to get date time string in "dd-MM-YYYY HH:mm:ss" format in current time zone. In order to achieve that I am trying following code:

new LocalDateTime(<retrieved TimeStamp>, <current user DateTimeZone>).toString("yyyy-MM-dd HH:mm:ss")

Both LocalDateTime and DateTimeZone from Joda library.

How ever this isn't working as per expected. Instead of current user's time zone above code gives me date time string in UTC only.

What am I missing?

Answer 1

I think your application is using java.util.Date which has no time zone information, the toString usage applies the JVM’s current default time zone when creating a string.

You can adjust the timezone instant by (Using Joda Library)

ZonedDateTime Tokyo = ZonedDateTime.ofInstant (instant,zoneIdTokyo) ;

Or implement zones

DateTimeZone zone = DateTimeZone.forID("Asia/Tokyo");

You are using LocalDateTime which is immutable class representing a local date and time (no time zone)

EDIT - You can try this (I haven't tested it)

DateTime udate = new DateTime("2016-05-01T20:10:04", DateTimeZone.UTC);
System.out.println(udate);
DateTime zone = udate.plusMillis(10000)
.withZone(DateTimeZone.forID("Asia/Kolkata"));
System.out.println(zone);

Answer 2

Add utc calendar when fetching the timestamp from database, so the jdbc driver can use this calendar timezone instead of default system timezone.

//Assign utc calendar
Calendar utc= Calendar.getInstance();
utc.setTimeZone(TimeZone.getTimeZone("UTC"));
Timestamp timestamp = rs.getTimestamp("timestampcolumn", utc);
//Convert to client date time
DateTime dateTime = new DateTime(timestamp.getTime(), DateTimeZone.forID("Asia/Kolkata"));
//Format
DateTimeFormatter formatter = DateTimeFormat.forPattern("dd-MM-YYYY HH:mm:ss");
//Change to client wall clock time
LocalDateTime localDateTime = dateTime.toLocalDateTime();
String formattedlocalDateTime = formatter.print(localDateTime)

Example

String utcTime = "2016-06-17 14:22:02Z";
DateTimeFormatter parser = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ssZ");
DateTime dateTime = parser.parseDateTime(utcTime).withZone(DateTimeZone.forID("Asia/Kolkata"));
DateTimeFormatter formatter = DateTimeFormat.forPattern("dd-MM-YYYY HH:mm:ss");
System.out.println(dateTime);
LocalDateTime localDateTime = dateTime.toLocalDateTime();
String formattedlocalDateTime = formatter.print(localDateTime);
System.out.println(formattedlocalDateTime);