Hot questions for Using Joda-Time in oracle
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?
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.
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.
- Collect the time from the User Interface as a string
- convert the time to the local date/time representation (where the timezone is included)
- convert the local date/time to GMT (or UTC)
- 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.
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 18.104.22.1680713 AM Pacific/Auckland'); insert into shot values( '26-FEB-09 22.214.171.1240713 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 126.96.36.1990713 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
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 188.8.131.520713 AM Pacific/Auckland'); you get an error because
'26-FEB-09 184.108.40.2060713 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 220.127.116.110713 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
SESSIONTIMEZONEis the default)
FROM_TZ(TO_TIMESTAMP('26-FEB-09 18.104.22.1680713 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 22.214.171.1240713 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
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
SYSTIMESTAMPreturns 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_TIMESTAMPreturns current time in current user session time zone. Data type is
TIMESTAMP WITH TIME ZONE.
LOCALTIMESTAMPalso returns current time in current user session time zone, however data type is
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.
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?
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
personRepo.getPersonBetweenDates(new DateTime().withTimeAtStartOfDay().toDate(), DateTime.now().toDate());
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).
- Field in the DB is of
- When i perform
SELECTquery it returns
- I'm located in Poland, hence in July the TimeZone here is
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");
2011-07-12 19:26:07 which I can't really explain.
I was also trying this
GregorianCalendar calendar = new GregorianCalendar();
with the same result.
How to properly read DATE from Oracle DB in "timezone agnostic" format and convert it into BST?
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.