CODEWITHSUNDEEP
databaseoracle-databasedatedatetimetimezone
Lokesh
Lokesh

Reputation: 7940

Storing Date with timezone

I have been working with oracle DB for a long time and have been storing dates without bothering about timezone. But now we have a issue where client and server are in different timezones and we need to perform date/time conversion according to timezone. This has opened up lots of questions now:

  1. Should we always store date/time along with its timezone or not? I am asking this because if servertimezone changes then my data will be corrupted.

  2. If oracle db server is located in a particular region then should it always run on local timezone? Is there any standard for this?

My second point is in relation to DR database servers which are located in different regions but have same data as prod DB. If timezones are not same for both DB then we are in trouble.

Upvotes: 0

Views: 373

Answers (1)

T.S.
T.S.

Reputation: 19394

To be able to show clients date in their timezone you need some offset. for example, your server runs in US EDT and you saved time like that for years. All your data is saved that way. So, you need to create a field, where you store offset for each user. You will then need to apply this offset to each date/time field on select. How you do this, I have no idea - because I have no idea how you use your data. Is it just a select statement, a report, or a website? But if this was an application, client would load user info including offset. For example, user in US EDT will have offset of 0 in your case. And then, any date/time field that is requested from db should get offset value applied. Of course, since your application was not designed for this from the beginning, it might will take some good effort.

Upvotes: 1

Related Questions