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

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.

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.

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

Do you have control over the table definitions in the database, i.e. can you choose the data types of columns freely? Will the system be used from many timezones? — Mick Mnemonic, Jul 28 '17 at 21:10
You can set the database timezone to UTC time by default. So, all statements that use current_timestamp or now() generate time that follows UTC. Any time entered by user on front-end can be converted to UTC before being sent to server. When retrieving times, return the UTC times to the frontend, which should convert it to local time. — Sashi, Jul 28 '17 at 21:15
Essentially, make sure that all times stored in the database follow one timezone (preferably standard UTC/GMT). This will make your application foolproof if you have servers in multiple timezones and userbase in multiple timezones. — Sashi, Jul 28 '17 at 21:23
Joda-Time is in maintainance mode and is being replaced by the new APIs, so I don't recommend start a new project with it. Even in [joda's website](http://www.joda.org/joda-time) it says: *"Note that Joda-Time is considered to be a largely “finished” project. No major enhancements are planned. If using Java SE 8, please migrate to java.time (JSR-310)."* - prefer [Java 8 new API](https://docs.oracle.com/javase/tutorial/datetime/) or [Threeten backport](http://www.threeten.org/threetenbp/) for Java <= 7 — , Jul 28 '17 at 23:13
Regarding your question, it's better to internally keep all in UTC, and convert to a timezone only when displaying to users — , Jul 28 '17 at 23:15
@Mick Mnemonic - I can choose the types for the database columns and yes, the system will be used from many timezones — Casey Harrils, Jul 29 '17 at 00:06
@Sashi - thanks for the info, can you look at the update I have made to the post? Working to see how one can do an insert — Casey Harrils, Jul 29 '17 at 00:08
@Hugo - thanks for the information. I will use the backport and not Joda. — Casey Harrils, Jul 29 '17 at 00:08
Don't think Oracle Timestamp column supports Timezone (similar to MySQL Timestamp). It's up to you to convert the time value to UTC time before sending it to DB. Looks like the Oracle 'Timestamp with Time Zone' data type supports Timezones. https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006081 — Sashi, Jul 29 '17 at 01:40
Have you considered Oracle's [Timestamp with timezone datatype](https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006081) ? — krokodilko, Jul 29 '17 at 06:58

Wernfried Domscheit · Accepted Answer · 2017-07-29T15:14:32.707

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.

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

1 Answers1