1

I know that there are tons of different tutorials on time conversion, but this one got me very confused. My task is to read UTC DATE from Oracle DB and convert it into BST time (in a more human readable format).

Facts:

  • Field in the DB is of DATE type.
  • When i perform SELECT query it returns 2011-07-12 15:26:07 result.
  • I'm located in Poland, hence in July the TimeZone here is UTC+2

What's happening:

On the Java side I'm using "classical" JDBC connection to the DB.

When I perform Timestamp timestampDate = resultSet.getTimestamp(COLUMN_NAME) I get the result ... but ...

System.out.println(timestampDate) prints to the console 2011-07-12 15:26:07.0 (which is similar to what I see in the DB tool.

System.out.println(timestampDate.getTime()); prints to the console 1310477167000 (which is wondering, because according to the ms to date converter i found online, it's basically 2011-07-12 13:26:07.0 (2h earlier - which somehow might be related to Polish timezone on that date)

When I perform conversion according to this code:

ukDateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); ukDateFormatter.setTimeZone(TimeZone.getTimeZone("BST")); return ukDateFormatter.format(timestampDate.getTime());

I get 2011-07-12 19:26:07 which I can't really explain.

I was also trying this

GregorianCalendar calendar = new GregorianCalendar(); calendar.setTime(timestampDate); calendar.setTimeZone(TimeZone.getTimeZone("BST")); return ukDateFormatter.format(calendar.getTime());

with the same result.

Question

How to properly read DATE from Oracle DB in "timezone agnostic" format and convert it into BST?

amerykanin
  • 255
  • 2
  • 5
  • 15
  • I am confused. If the field (really, "column") in the DB is of `DATE` data type, then that is - generally - **not** a "UTC DATE". The `DATE` data type in Oracle does not have a time zone attached. If you, located in Poland, enter `insert into table_t (date_column) values (to_date('2015-12-11 10:00:00', 'yyyy-mm-dd hh24:mi:ss))` into your database, and I do the same from the U.S., that is the date that will be stored for both of us, and it will be a date WITHOUT time zone. Please clarify what you mean; when you read a `DATE`, how do you decide what time zone it really belongs to? –  Jan 25 '17 at 15:00
  • Let's assume, that DATE column has no timezone attached: - then why after reading into `Timestamp` in Java and performing `getTime` I get a confusing result? - so according to what you're saying, I should be able to read the date from DB and do a simple conversion with the calendar ... but somehow I can't to do it in such a straightforward way. – amerykanin Jan 25 '17 at 15:09
  • 1
    Note - Java will interpret `"BST"` as if you meant "Bangladesh Standard Time". It literally maps it to `"Asia/Dhaka"`. Don't use time zone abbreviations as identifiers. See also http://stackoverflow.com/a/41683097/634824 – Matt Johnson-Pint Jan 26 '17 at 18:59

3 Answers3

1

Here's a way of doing it in the database side:

with dates as (select to_date('01/07/2016 10:39:29', 'dd/mm/yyyy hh24:mi:ss') dt from dual union all
               select to_date('01/02/2016 09:18:41', 'dd/mm/yyyy hh24:mi:ss') dt from dual)
select dt,
       cast(dt AS TIMESTAMP) utc_dt_ts,
       from_tz(cast(dt AS TIMESTAMP), 'UTC') AT time zone 'Europe/London' dt_as_ts_bst,
       cast(from_tz(cast(dt AS TIMESTAMP), 'UTC') AT time zone 'Europe/London' AS DATE) dt_at_bst
from   dates;

DT                  UTC_DT_TS                                         DT_AS_TS_BST                                      DT_AT_BST
------------------- ------------------------------------------------- ------------------------------------------------- -------------------
01/07/2016 10:39:29 01-JUL-16 10.39.29.000000                         01-JUL-16 11.39.29.000000 EUROPE/LONDON           01/07/2016 11:39:29
01/02/2016 09:18:41 01-FEB-16 09.18.41.000000                         01-FEB-16 09.18.41.000000 EUROPE/LONDON           01/02/2016 09:18:41

The fourth column (dt_at_bst) is the one that shows how to take the date and turn it into another date at BST. It does this by first casting the date as a timestamp and then telling Oracle to treat it as a timestamp at UTC and to output the timestamp for the 'Europe/London' region. Specifying the region like this (rather than passing a specific +01:00 timezone) means that the resultant timestamp will be daylight savings aware. Specifying the region as a three letter shortcut is not advised since that may represent more than one region - e.g. BST could be British Summer Time or Bering Standard Time; both very different things!

I have assumed that by BST you mean British Summer Time, so I have specified the region for the timestamp to be moved to as Europe/London. You would need to adjust this as applicable, if you need a different timezone.

I have included a winter and a summer date in my sample data to show you the effects of casting it into BST - the summer time is expecting to be changed, and the winter time is not.

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • The OP mentioned he/she is in Poland (UTC+2). I don't know if that is relevant. If it is, perhaps he/she didn't mean the input (a date, therefore without any time zone info attached to it) is to be interpreted as UTC. Hopefully he/she will be able to adapt your answer if he/she needs to convert from the Polish summer time zone to whatever else. –  Jan 25 '17 at 15:04
  • @mathguy yeah, it wasn't spectacularly clear from the OP's post what they meant; I took a punt and guessed that the dates are stored in UTC time, based on them wanting `to read UTC DATE from Oracle DB`. I think they mentioned they were in Poland because they were getting getting a result two hours earlier than the date, which they thought might be as a result of them being in Poland. – Boneist Jan 25 '17 at 15:08
  • It is a workable solution, but has some downsides for more complex global applications. As example when App server must provide exact time in user's time zone, but all three time zones are different (DB Server, App Server and users) for cases like "Cut Off Time" in financial world. – Vadim Jan 25 '17 at 15:37
  • @Vadim That's the problem with using the `DATE` datatype instead of something like `TIMESTAMP WITH LOCAL TIMEZONE` to store the dates in the database in the first place, I guess. – Boneist Jan 25 '17 at 15:40
  • @Boneist Exactly. I had that many times... Very often all servers in the system setted up in GMT timezone, regardless of geographical localtion. – Vadim Jan 25 '17 at 15:47
  • @Vadim it helps if you can confirm all the dates are at UTC, because at least then they're all consistent. – Boneist Jan 25 '17 at 15:50
  • @Boneist correct. It is up to implementor what to choose based on exact situation. – Vadim Jan 25 '17 at 15:56
  • Java will interpret "BST" as "Bangladesh Standard Time". It maps it to "Asia/Dhaka" – Matt Johnson-Pint Jan 26 '17 at 19:01
0

Actually it is not about Oracle, but more about Java. First of all: When you use 

System.out.println(timestampDate)

in output you see already adjusted time to your computer time zone.

It is always adjusted when you use Date (i.e.

Calendar.getTime() or Timestamp.getTime())

Code to play with:

    SimpleDateFormat dtFmt = new SimpleDateFormat("HH:mm:ss");
    NumberFormat nFmt = NumberFormat.getIntegerInstance();
    nFmt.setMinimumIntegerDigits(2);
    long currentTimeMs = System.currentTimeMillis();

    GregorianCalendar utcCalendar = new GregorianCalendar(
            TimeZone.getTimeZone("UTC"));

    GregorianCalendar bstCalendar = new GregorianCalendar(
            TimeZone.getTimeZone("Europe/London"));

    GregorianCalendar localCalendar = new GregorianCalendar();

    utcCalendar.setTimeInMillis(currentTimeMs);
    bstCalendar.setTimeInMillis(currentTimeMs);
    localCalendar.setTimeInMillis(currentTimeMs);

    System.out.println("---- milliseconds ----");
    System.out.println("Current ms       : " + currentTimeMs);
    System.out.println("Local Calendar ms: " + localCalendar.getTimeInMillis());
    System.out.println("UTC Calendar   ms: " + utcCalendar.getTimeInMillis());
    System.out.println("BST Calendar   ms: " + bstCalendar.getTimeInMillis());

    System.out.println("---- SimpleFormat Time ----");
    System.out.println("Current Time: "
            + dtFmt.format(new Date(currentTimeMs)));
    System.out.println("Local Time: " + dtFmt.format(localCalendar.getTime()));
    System.out.println("UTC Time  : " + dtFmt.format(utcCalendar.getTime()));
    System.out.println("BST Time  : " + dtFmt.format(bstCalendar.getTime()));

    System.out.println("---- Calendar Zone Time ----");
    System.out.println("Local Zone Time: "
            + nFmt.format(localCalendar.get(Calendar.HOUR_OF_DAY)) + ":"
            + nFmt.format(localCalendar.get(Calendar.MINUTE)) + ":"
            + nFmt.format(localCalendar.get(Calendar.SECOND)));

    System.out.println("UTC Zone Time  : "
            + nFmt.format(utcCalendar.get(Calendar.HOUR_OF_DAY)) + ":"
            + nFmt.format(utcCalendar.get(Calendar.MINUTE)) + ":"
            + nFmt.format(utcCalendar.get(Calendar.SECOND)));

    System.out.println("BST Zone Time  : "
            + nFmt.format(bstCalendar.get(Calendar.HOUR_OF_DAY)) + ":"
            + nFmt.format(bstCalendar.get(Calendar.MINUTE)) + ":"
            + nFmt.format(bstCalendar.get(Calendar.SECOND)));

}

As you will see each Calendar returns Time fields (HOUR_OF_DAY, MINUTE, SECOND) according to its TimeZone, not what you print or format from Calendar.getTime())

Vadim
  • 4,027
  • 2
  • 10
  • 26
  • What's interesting, is that after performing `getTimestamp(...)` on my local machine and debugging what I exactly get, I can see that `Timestamp timestampDate` has `2011-07-12T15:26:07.000+0200` which would suggest some automatic enhancing it with my local timezone ... is there a way to avoid that? – amerykanin Jan 25 '17 at 17:22
  • No, there is no way. That what I told about. Date and Timestamp as well, do not care information about Time Zone. Actual value is milliseconds from 1/1/1970 GMT time. So, what you see from toString() method is adjusted String representation from you computer TimeZone settings, not from Timestamp object. – Vadim Jan 25 '17 at 17:43
  • Just keep in mind. Date and Timestamp objects point to exact moment in time. Thta means if you initiated transaction at 8 PM your local time. It heppended to me at 1 PM my time, but it was exact one moment of time. So... those classes have no capability to represent that moment in different time zones - only in current computer timeZone. Calendar can show that moment (which is that long value) can reperesent a moment in needed TimeZone. – Vadim Jan 25 '17 at 17:54
0

What I did, and it seems to be working for me:

ukDateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); ukDateFormatter.setTimeZone(TimeZone.getTimeZone("Europe/London"));

and performing:

Timestamp timestampDate = rs.getTimestamp(...); DateTime dateTime = new DateTime(timestampDate).withZoneRetainFields(DateTimeZone.UTC); System.out.println(ukDateFormatter.format(dateTime.getMillis()));

prints:

2011-07-12 16:26:07 from the input 2011-07-12 15:26:07

Why happened here?

What was so problematic here, is that rs.getTimestamp(...) was returning the date from the database "as it is" (since DATE column type doesn't preserve the timezone) implicitly but was adding some information about my local timezone - which I didn't wanted.

Easiest solution was to use joda and create new object, retaining values, but changing timezone to UTC. From that point conversion with SimpleDateFormat is quite straightforward.

amerykanin
  • 255
  • 2
  • 5
  • 15