Start a new topic

timestamp - Why is DST displaying 1 hour greater for DST start timestamps?

[This topic is migrated from our old forums. The original author name has been removed]

In my Teradata database, I have a table with a *TIMESTAMP* column. The data in the column is (same as when I loaded it from Excel and checked using WinSQL): 2012-03-11 02:00:00 2013-03-10 02:00:00 2014-03-09 02:00:00 2015-03-08 02:00:00 2016-03-13 02:00:00 2017-03-12 02:00:00 2018-03-11 02:00:00 DBVisualizer Pro 9.1.9 displays the data as: 2012-03-11 03:00:00 2013-03-10 03:00:00 2014-03-09 03:00:00 2015-03-08 03:00:00 2016-03-13 03:00:00 2017-03-12 03:00:00 2018-03-11 03:00:00 Why are the start timestamps being displaying *1 hour greater* than the actual DST start timestamps? Edited by: . on Aug 20, 2014 12:41 AM

[This reply is migrated from our old forums.]

Re: timestamp - Why is DST displaying 1 hour greater for DST start timestamps?
Hi, It is most likely due to the Time Zone setting in Tools->Tool Properties possibly in combination with JDBC driver settings. There are so many different TIMESTAMP variations between databases and so many differences between JDBC drivers in how they adjust timestamp values that I can only suggest that you try a few things. First, look at the JDBC driver documentation to see if it has any properties for adjusting timestamp values. If not, try setting Time Zone in DbVisualizer to e.g "Etc/GMT". Note! Restart DbVisualizer after changing the Time Zone setting, otherwise you may get incorrect results. Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: timestamp - Why is DST displaying 1 hour greater for DST start timestamps?
When I change the Time Zone to America/Phoenix everythings look correct. Time Zone to America/New York the start time is off by an hour Time Zone to America/Chicago the start time is off by an hour Time Zone to America/Denver the start time is off by an hour Time Zone to America/Los_Angeles the start time is off by an hour I am using the same JDBC driver that is being used in the other tools and everything is displaying correctly in the other tools. So it looks like the issue is specific to something in DBVisualizer Pro. I upgraded to 9.1.10 and the issue is still there.
[This reply is migrated from our old forums.]

Re: timestamp - Why is DST displaying 1 hour greater for DST start timestamps?
Hi, Are the other tools Java/JDBC based? I'm not sure it's really an "issue" (as in something not working as it should), but rather how TIMESTAMP data depends on a specific Timezone setting. As I said initially, this varies greatly between databases and drivers. In many cases, it makes most sense to save the data as GMT timestamps in the database and then adjust them to the local timezone when working with the data. In other cases, it makes more sense to work with the data using the raw data timezone. Some databases also have a TIMESTAMP datatype that contains the timezone while others use just a raw timestamp and an assumed timezone. Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: timestamp - Why is DST displaying 1 hour greater for DST start timestamps?
Daylight sayings time in the USA starts and ends always at *2AM*. Phoenix doesn't observe Daylight sayings time. It is an issue because a time that is in the database as 2AM should display in dbvis as 2AM when all I am doing is looking at the data. I am not trying to convert anything. Teradata SQL Assistant displays the start times as 2AM when I connect with JDBC.
[This reply is migrated from our old forums.]

Re: timestamp - Why is DST displaying 1 hour greater for DST start timestamps?
Hi, But you said it works when you set Time Zone to America/Phoenix, right? DbVisualizer should pick up the timezone to use as the default from the OS, so maybe you have another timezone setting at the OS level? Best Regards, Hans