Hot questions for Using Joda-Time in oracle

Question:

I'm using MyBatis 3.2, and want to map Oracle DATE data type to Java org.joda.time.DateTime data type.

Here is my configuration:

<resultMap id="something" type="com.myself.SomeClass">
  <result property="creationDate" column="CREATION_DATE" javaType="org.joda.time.DateTime" />
</resultMap>

But I get following error:

Caused by: org.apache.ibatis.builder.BuilderException
         : Error parsing SQL Mapper Configuration. 
Cause    : org.apache.ibatis.builder.BuilderException
         : Error parsing Mapper XML. 
Cause    : java.lang.IllegalStateException
         : No typehandler found for property creationDate

Is my configuration correct? Or is it caused by my Oracle data type is DATE instead of DATETIME? Does MyBatis support joda DateTime?


Answer:

I think you need to use TypeResolver to handle JodaTime to Oracle DateTime. I had experience with this with MyBatis with MySQL and you might use this is a guide that might help you with your issue.

I used this github project as guide on how to use TypeResolver: https://github.com/LukeL99/joda-time-mybatis

And in one of my mappers, I have this code:

<result column="expiryDate" property="expiryDate" javaType="org.joda.time.DateTime" typeHandler="org.joda.time.mybatis.handlers.DateTimeTypeHandler"/>

The org.joda.time.mybatis.handlers.DateTimeTypeHandler is class from the github project I posted.

I hope this would help guide you.

Question:

What is the best way/approach to store date/time info (and timestamps in general) so the information that can be displayed in different timezones?

My thoughts were to make the DB just hold time in a specified format (like GMT or UTC). When one reads from the DB, convert any date/time/timestamp data collected to the local timestamp for the area.

When inserting data into the DB. The following would apply.

  1. Collect the time from the User Interface as a string
  2. convert the time to the local date/time representation (where the timezone is included)
  3. convert the local date/time to GMT (or UTC)
  4. Save to the DB in GMT / UTC format

I have seen the Joda Time package and had considered using this as well.

What is the normal way that people go about resolving this issue? I hope this is not too vague - I have seen the Oracle DB here save date/time with a timezone that seems to be local.

Ex: I get the following:

SQL> SELECT EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP)||':'||
       EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP)
FROM dual;  
Result  => -5:0

Any hints on coming about this issue would be greatly appreciated.

TIA

Update

The following was done:

SQL> ALTER DATABASE SET TIME_ZONE = 'UTC';

The system was "bounced"

SQL>  select dbtimezone from dual;
DBT
---
UTC

Out of curiosity, how are you doing your inserts into the DB tables so that the Timezone is taken into account? I get errors when using the TIMESTAMP type for a column:

SQL> create table shot (t timestamp);
Table created.

SQL> insert into shot values( '26-FEB-09 11.36.25.390713 AM Pacific/Auckland');
insert into shot values( '26-FEB-09 11.36.25.390713 AM Pacific/Auckland')
                         *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

SQL>  insert into shot values( '26-FEB-09 11.36.25.390713 AM');
1 row created.

Update Also adding link to old post on setting the "session timezone" within java/jdbc/Oracle. If I am understanding this correctly, a certain .jar file is needed to be sure that the "session timezone" is the same as the "database timezone" when connecting to the database to run java code.

Setting session timezone with spring jdbc oracle


Answer:

I would recommend to use data type TIMESTAMP WITH LOCAL TIME ZONE. All values of TIMESTAMP WITH LOCAL TIME ZONE are internally stored in DBTIMEZONE, in your case UTC (thus you cannot change DBTIMEZONE anymore once you have inserted any data of this data type). When you select such column then the time is always shown in current user session time zone, SESSIONTIMEZONE. Hence it is essential to set your session time zone properly.

When you run insert into shot values( '26-FEB-09 11.36.25.390713 AM Pacific/Auckland'); you get an error because '26-FEB-09 11.36.25.390713 AM Pacific/Auckland' is a string - not a timestamp!

You can insert timestamp values in various ways, for example:

  • TIMESTAMP '2009-02-16 23:36:25.390713 Pacific/Auckland'
  • TO_TIMESTAMP_TZ('26-FEB-09 11.36.25.390713 AM Pacific/Auckland', 'DD-MON-RR HH:MI:SS.FF AM TZR')
  • FROM_TZ(TIMESTAMP '2000-03-28 18:00:00', 'Pacific/Auckland')
  • FROM_TZ(TIMESTAMP '2000-03-28 18:00:00', SESSIONTIMEZONE) (although you could skip it as SESSIONTIMEZONE is the default)
  • FROM_TZ(TO_TIMESTAMP('26-FEB-09 11.36.25.390713 AM', 'DD-MON-RR HH:MI:SS.FF AM'), 'Pacific/Auckland')

Be careful with these expressions:

  • TIMESTAMP '1999-10-29 01:30:00' AT TIME ZONE 'Pacific/Auckland'
  • TO_TIMESTAMP('26-FEB-09 11.36.25.390713 AM', 'DD-MON-RR HH:MI:SS.FF AM') AT TIME ZONE 'Pacific/Auckland'

TIMESTAMP '1999-10-29 01:30:00' AT TIME ZONE 'Pacific/Auckland' actually means (FROM_TZ(TIMESTAMP '1999-10-29 01:30:00', 'SESSIONTIMEZONE') AT TIME ZONE 'Pacific/Auckland', so you may get an unwanted time shift.

Note, when you use TIMESTAMP literal then the format is fix at YYYY-MM-DD HH24:MI:SS

If you don't specify any time zone information (for example TIMESTAMP '2009-02-16 11:36:25') then Oracle considers it as current user session time zone SESSIONTIMEZONE.

  • SYSTIMESTAMP returns current time in the time zone of database server's operating system, not at DBTIMEZONE - although often they are set to the same value.

  • CURRENT_TIMESTAMP returns current time in current user session time zone. Data type is TIMESTAMP WITH TIME ZONE.

  • LOCALTIMESTAMP also returns current time in current user session time zone, however data type is TIMESTAMP, not TIMESTAMP WITH TIME ZONE.

Another note, when you have TIMESTAMP WITH LOCAL TIME ZONE then you cannot format output like this SELECT TO_CHAR(t, 'DD/MM/YYYY HH24:MI:SS ZTR') FROM shot because by definition TIMESTAMP WITH LOCAL TIME ZONE is always shown in current user session time zone and TZR (i.e. Time zone region name) does not make any sense. Well, you could consider this as an Oracle bug, but to a certain extent it makes sense.

Question:

I am getting the following exception:

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value did not match expected type. [java.util.Date (n/a)]; 
nested exception is java.lang.IllegalArgumentException: Parameter value did not match expected type [java.util.Date (n/a)]

Here is my query method in my repository:

 @Query("SELECT COUNT(entity) FROM Person entity WHERE trunc(entity.date) BETWEEN :startTime AND :endTime")
 int getPersonBetweenDates(@Param("startTime") DateTime var1, @Param("endTime") DateTime var2);

And my implementation in component:

 int totalPersons = personRepo.getPersonBetweenDates(new DateTime().withTimeAtStartOfDay(), DateTime.now());

Why am I getting this error? It seems that the two DateTime parameters in the implementation match those in my method?


Answer:

Parameter value did not match expected type. [java.util.Date (n/a)];

Instead of using joda's DateTime in method parameters, use java.util.Date, Like following:

@Query("SELECT COUNT(entity) FROM Person entity WHERE trunc(entity.date) BETWEEN :startTime AND :endTime")
int getPersonBetweenDates(@Param("startTime") Date var1, @Param("endTime") Date var2);

Then in your client code, if you have some DateTime instances, you can use toDate method to convert the DateTime into a Date:

personRepo.getPersonBetweenDates(new DateTime().withTimeAtStartOfDay().toDate(), DateTime.now().toDate());

Question:

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?


Answer:

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.