Retrieving time zone of date stored in database

Question

My J2EE application is deployed in cluster and servers can be in multiple timezones. For example one server is in EST and another server in CST.

Consider a scenario where request goes to server in EST and store the date and time in database (without timezone). There is another request to retrieve the same date. But that request may go to another server in CST.

Is there any way to determine which time zone date is stored in database?

PS: I am using Oracle/Java/Hibernate.

Answer 1

When you use data type DATE or TIMESTAMP then you don't have any information about the time zone - unless your application ensures always a certain time zone (e.g. UTC) when data are inserted or updated.

Andreas statement is not fully correct, in Oracle database you have three time zones:

1. The database time zone

   You can interrogate it with DBTIMEZONE. It is relevant only for TIMESTAMP WITH LOCAL TIME ZONE data type columns and defines the storage format. Thus you cannot change DBTIMEZONE on your database if the database contains a table with a TIMESTAMP WITH LOCAL TIME ZONE column and the column contains data. Note, this is NOT the timezone of SYSDATE and SYSTIMESTAMP

2. The time zone of database server operating system

   This time zone is relevant for result of SYSDATE and SYSTIMESTAMP.

3. The time zone of your session

   You can interrogate it with SESSIONTIMEZONE. It might be determined by your Web Application server or by your Client Browser time zone. (I am not an expert for web technologies) In case of a fat client it can be set in your Registry or environment variable ORA_SDTZ. I don't know which one has precedence and which value you get when either of them are not defined, it may vary also on your Database-Provider and drivers. SESSIONTIMEZONE can be changed at any time by user with ALTER SESSION SET TIME_ZONE=...

Date type TIMESTAMP WITH TIME ZONE stores times with time zone information. You can convert them to client local time zone or populate as inserted or anything else, depending on your requirements. You get the time zone value with EXTRACT function or when you convert to string, e.g. TO_CHAR(TS_VALUE, TZH:TZM) or TO_CHAR(TS_VALUE, TZR).

Date type TIMESTAMP WITH LOCAL TIME ZONE also stores times with time zone information. However, it always displays the time in current session time zone of the client, you cannot convert it to any other time zone (unless to do a CAST beforehand). Thus, when you try to do TO_CHAR(TS_VALUE, 'hh24:mi:ss TZH:TZM') with TIMESTAMP WITH LOCAL TIME ZONE value, you get an error!

Answer 2

No. If you store the DATE without a time zone, there is no way to query the time zone.

You, as the developer, needs to decide how time zones are handled. Remember you have 3 time zones:

• Database server time zone.
• Web Application server time zone.
• Client Browser time zone.

If a user in Los Angeles enters a time, what should a user in Paris see?

If a user in Sydney performs an action, what action timestamp should a user in Toronto see?

Until you can answer those questions, i.e. define your requirements, a solution/implementation is impossible to code/verify.

As mentioned in a comment, there are basically two ways to handle time zones:

1. Store all dates in a fixed time zone, e.g. UTC, and convert where needed, which is pretty much everywhere.
2. Store dates with timezone, and either convert as needed or display with time zone. Users would likely prefer conversions, but that is a design decision for you to make.