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.