Convert database time to IST datetime

Question

I have a time value stored in my database in

HH:mm:ss

format (using MySQL's

time

type). This time is to be considered as a value of IST timezone. The server on which my Java code runs follows the UTC timezone.

How can I get a formatted datetime in

yyyy-MM-dd HH:mm:ss

in IST (or in UTC millis)? Following is what I've tried till now:

// ... Code truncated for brevity

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

LocalTime time = resultSet.getTime("send_time").toLocalTime();

LocalDateTime datetime = time.atDate(LocalDate.now());

System.out.println(datetime.format(formatter));

The above correctly prints the datetime on my local machine, which is on IST, but I'm concerned about how it will behave on the remote server.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Your approach is fine and should work regardless of your computer's time zone since there is no time zone information in either LocalTime or LocalDateTime. One possible issue is with LocalDate.now() which returns today's date in the computer's local time zone, not in IST. You may want to replace it with LocalDate.now(ZoneId.of("Asia/Calcutta")).

Or as commented by @OleV.V. you could use the new driver facilities to derive a LocalTime directly:

LocalTime time = resultSet.getObject("send_time", LocalTime.class);

Note possible caveats with your approach:

if the time zone you use introduces DST, you may end up with two identical times in your DB that were actually different instants - using UTC to store times is probably more robust
time in mysql can store values smaller than 00:00 and larger than 23:59:59.999999, in which case you may experience unexpected behaviours on the Java side.

Convert database time to IST datetime

Sign up