CODEWITHSUNDEEP
javaoracle-databasetimestampjodatime
Casey Harrils
Casey Harrils

Reputation: 2963

Using JAVA to store date/time info in the DB?

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

Upvotes: 4

Views: 3057

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59486

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.

Upvotes: 4

Related Questions