how do i fetch date timestamp as UTC

Question

I am storing date timestamps in DB as UTC value, while retrieving it back i need to make as UTC time and need to convert to specific timezone value.

i.e. 2015-05-01 00:09:30:00 UTC time need to convert to IST(Or other timezone)

resultSet.getDate("VisitDate")

please help on this.

score 10 · Answer 1 · edited Jun 20 '20 at 09:12

10

Java 8

You can use a ZonedDateTime set to UTC and then translate it to a LocalDateTime using something like...

java.sql.Timestamp ts = resultSet.getTimestamp("VisitDate");
ZonedDateTime utcDateTime = ZonedDateTime.ofInstant(ts.toInstant(), ZoneId.of("UTC"));
LocalDateTime localDateTime = utcDateTime.withZoneSameInstant(ZoneId.systemDefault()).toLocalDateTime();

Obviously, I've use ZoneId.systemDefault() in the example (convert the zoned date/time to local date/time), but you can pass what ever zone you want/need

Joda-Time

And similarly with Joda-Time if you're not using Java 8

java.sql.Timestamp ts = resultSet.getTimestamp("VisitDate");
LocalDateTime utcDateTime = new LocalDateTime(ts, DateTimeZone.UTC);
DateTime hereDateTime = utcDateTime.toDateTime(DateTimeZone.getDefault());

edited Jun 20 '20 at 09:12

Community

1
1

answered May 19 '15 at 07:23

MadProgrammer

343,457
22
230
366

One thing that isn't quite right about this is that it assumes the db's default `Calendar` is in UTC, but that might not be the case. In h2 for example the default `Calendar` is `Calendar.getInstance()` which is the systems calendar. What one must do is `ZoneId dbZoneId = ZoneId.of("Europe/London"); Calendar dbCalendar = Calendar.getInstance(TimeZone.getTimeZone(dbZoneId)); TimeStamp ts = resultSet.getTimestamp("VisitDate", dbCalendar);`. This will then retrieve the `Timestamp` instance using the calendar that was used to store the field in the first place. – Brett Ryan Dec 09 '16 at 01:52
Just clarifying my prior comment. This can become evident if the h2 database is created in one zone, then copied to a machine in another zone. The process that created the data would have stored this with UK `Timestamp`s, the process reading will by default read them as if they are AU `Timestamp`s. – Brett Ryan Dec 09 '16 at 02:04
@BrettRyan This is where having the time stored in a centrally defined time zone is a good idea :P – MadProgrammer Feb 11 '17 at 02:56
the illustration I gave was an example I actually have to deal with. Most DB's I've worked with default to not storing as UTC and instead use the system default. I have a db that gets created in the UK then fired up in Australia, there's no context from the db alone to know what zone the timestamps are in. While not relevant to the question, altering the way this is achieved is not an option. – Brett Ryan Feb 11 '17 at 06:10

Areeb Gillani · Answer 2 · 2015-05-19T07:30:02.183

private final static String DATEFORMAT = "yyyy-MM-dd HH:mm:ss";
public static String getCurrentTimeInUTC(){

    final SimpleDateFormat sdf = new SimpleDateFormat(DATEFORMAT);
    sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
    final String utcTime = sdf.format(new Date());


    return utcTime;

}

U can change the UTC string to any known timezone string to get that formate. Do vote if this is helpful. for changing the timezone you can use this function

public static String getLocalTime(String timeZone) throws ParseException{
     DateFormat formatter = null;
        Date convertedDate = null;

        DateFormat outputFormat=new  SimpleDateFormat(DATEFORMAT);
        formatter = new SimpleDateFormat(DATEFORMAT);
        formatter.setTimeZone(TimeZone.getTimeZone(timeZone));
        convertedDate = (Date) formatter.parse(getCurrentTimeInUTC());
        return outputFormat.format(convertedDate);
}

Enjoy :-)

Now you can use the second function as in the second last line you can give your UTC date for further conversion. — Areeb Gillani, May 19 '15 at 07:30

score 1 · Answer 3 · edited May 23 '17 at 10:31

As you said while lodaing into DB you are setting as UTC timezone, But when retriving the Timezone back from DB it can not be UTC again as DB wont store Timezone information for more details checkout this link Dealing with Timezones in DB

So according to that it might return the Timezone of your local JVM or DB server Timezone settings.

Anyway to change the Timezone as mentioned in other answer you can Use SimpleDateFormat

  final SimpleDateFormat sdf = new SimpleDateFormat(DATEFORMAT);
sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
final String utcTime = sdf.format(new Date());

how do i fetch date timestamp as UTC

3 Answers3

Java 8

Joda-Time