Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB2 - Changing TimeZone

 
Sam Kumar
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
If we change the timezone, let say from PST to EST, what will happen to existing record which were persisted with PST format. Is DB2 will automatically take care of the conversion of the timestamp value in all the tables.
Thanks in advance.
SK
 
Paulo Salgado
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sushil.
I don't think so. What is in the table doesn't get changed unless you specifically command to do so. I believe up to V7, DB2 just stores the timestamp value and no related time zone for conversions. In other words, it know only what timestamp value is in the column, but has no clue of what time zone you expect that to be in.
Paulo
 
Sam Kumar
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paulo:
Thanks for your reply.
We are using DB2 7.2, in that case I may have to manually update timestamp values in all the tables as we are permanently changing our DB and Web server timezone.
You mentioned that upto v7, DB2 just stores the timestamp value only, so in version 8, DB2 will store the timezone also along with the timestamp? I looked into IBM document "Whats new in Version 8" and it says under "JDBC Driver Enhancement":
"Date, Time, and Timestamp values that can be set or retrieved for a particular time zone using a Calendar object."
I could not understand fully above statement...
Thanks.
SK
 
Paulo Salgado
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sushil.
Yes, I believe you will have to update your database manually.
I should have been more clear about the V7 statement. What I meant to say is that I haven't worked with V8 yet and don't know how that works exactly, though I don't think they would have changed that.
I haven't read the docs or worked with V8 yet, as I said before, but I'll will make an assumption which I believe is right about the JDBC Driver enhancements you mentionend.
They probably are saying that from V8 you'll be able to use a Calendar object when mapping to a DATE or TIMESTAMP column. Upon recognizing that you are using a Calendar object, the JDBC driver would take into account the time zone of the Calendar object, the timezone of your system and convert the timestamp (or date) appropriately before inserting it into the database.
Once this value is inserted into the table, it is there and won't be changed unless you execute an UPDATE SQL statement somehow to forcefully change it. I don't believe by changing your server's time zone you will affect the values already stored in your tables.
The reason I think they wouldn't change the way the timestamp/date types are stored is, of course, because legacy applications, and that the database is manipulated by programs in several different programming languages, which most of the time would not have time zone associated with timestamp/date as Java has in the Calendar class.
Paulo
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic