Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2015
    Posts
    5

    Unanswered: DB2 Handling of DST

    I'm spending some time investigating issues related to daylight savings time. During my analysis, I was testing using the date/time of 11/01/2015 01:55 CDT. This time is interesting because it is 5 minutes before the 1 hour fallback that happens at the end of daylight savings time. I was testing using a Java application with a Spring JDBCTemplate and a DB2 database. I created a java.sql.Timestamp object with that date and time. I verified it by printing it out with timezone. I then used the Spring JDBCTemplate to store it in the database into a Timestamp column.

    To my absolute horror, when I read it back out of the database, it came up as 11/01/2015 01:55 CST. I verified this a second time with a database tool.

    This seems to suggest something bad happened either in Spring, JDBC, or in DB2 itself. Wherever the conversion happens between local time and whatever DB2 uses internally (presumably internally it has some timezone agnostic format like UTC or the number of milliseconds past some UTC date/time or something).

    I then was wondering what would happen if I tried to insert the date directly from the database tool. This is when I realized I didn't know how to do that. You can use a format like this '2015-11-1 01:55:00', but that's not good enough. Because of the hour and the local timezone, this time specification is ambiguous -- it happens twice, once in CDT and once in CST. To make it unambiguous, you would also have to specify the timezone. Is there a valid way to do this (to include the timezone) when inserting a timestamp using a text string?

    Anyone have any other thoughts on this?

    DB2 v10.1.0.5 fixpack 5.

  2. #2
    Join Date
    Sep 2015
    Posts
    5
    I did a little more work to refine what I'm talking about here. I cut Spring out of the equation, and created a simple demo program in Java 7 (v 1.7.0_25). The DB2 database version is in my original post.

    Here's the sample Java program (imports excluded):

    Code:
    public class DSTProblemDemo {
    
    	private static DateFormat dfLocal = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
    	
    	public static void main(String[] args) {
    		try {
    			demo();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    
    	private static Connection getConnection() throws SQLException {
    		Properties props = new Properties();
    		props.put("user", "user");
    		props.put("password", "password");
    		return DriverManager.getConnection("jdbc:db2://server:port/dbname:currentSchema=WORKER;", props);
    	}
    	
    	private static void demo() throws Exception {
    		Date date = dfLocal.parse("11/01/2015 01:55 CDT");
    		Timestamp timestamp = new Timestamp(date.getTime());
    		System.out.println("The timestamp is " + dfLocal.format(timestamp) + " (" + timestamp.getTime() + " ms)");
    		
    		System.out.println("Inserting timestamp into the database...");
    		Connection con = getConnection();
    		PreparedStatement ps = con.prepareStatement("insert into WORKER.DST_DEMO (TS) values ?");
    		ps.setTimestamp(1, timestamp);
    		ps.executeUpdate();
    		con.commit();
    		
    		System.out.println("Querying timestamp from the database...");
    		Statement query = con.createStatement();
    		ResultSet rs = query.executeQuery("select TS from WORKER.DST_DEMO");
    		while (rs.next()) {
    			timestamp = rs.getTimestamp(1);
    			System.out.println("The timestamp is " + dfLocal.format(timestamp) + " (" + timestamp.getTime() + " ms)");
    		}
    	}
    }
    Here is the sample program output:

    Code:
    The timestamp is 11/01/2015 01:55 CDT (1446360900000 ms)
    Inserting timestamp into the database...
    Querying timestamp from the database...
    The timestamp is 11/01/2015 01:55 CST (1446364500000 ms)
    And for reference, here's the DB table I used (empty when program was run):

    Code:
    CREATE TABLE WORKER.DST_DEMO  ( 
    	ID	INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL,
    	TS	TIMESTAMP,
    	PRIMARY KEY(ID)
    )
    What's going on here and in what ways can this be fixed?

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please state which DB2 version and OS you are using.

    For DB2 LUW (any version), timestamps in the database do not contain timezone info. I suspect that it is the java layer that is giving you the results are seeing. Try the inserts using the command line and see what you get.

    Andy

  4. #4
    Join Date
    Sep 2015
    Posts
    5
    Quote Originally Posted by ARWinner View Post
    Please state which DB2 version and OS you are using.

    For DB2 LUW (any version), timestamps in the database do not contain timezone info. I suspect that it is the java layer that is giving you the results are seeing. Try the inserts using the command line and see what you get.

    Andy
    The DB2 version is DB2 v10.1.0.5 fixpack 5. I'm not certain as to the OS, as I don't have direct access to the system. I can't try doing an insert from the command line (like straight SQL?) because I don't know how to format it. As I mentioned in my first post, with things set up for U.S. Central Time, you can pass some SQL this "'2015-11-1 01:55:00'" but that's ambiguous without the timezone information because it happens twice.

    This gets us back to your saying timestamps in the database do not contain timezone info. Please clarify. A Java Date or Timestamp object does not contain timezone info either, but it doesn't need timezone info, because it's all based off of UTC and timezones are just a formatting/display issue. So it's important to know whether it is storing some timezone agnostic format like UTC or something based off of a fixed point in time, or if it is storing -- quite literally -- this '2015-11-1 01:55:00' -- which is meaningless without knowing what timezone it came from.

    This is CRITICALLY important to the integrity of systems everywhere. Java is in heavy use for web applications throughout the world. The Java Timestamp object is a thin wrapper around the Java Date object. The Java Date object represents a specific instant in time, and is intended to reflect coordinated universal time (UTC). To a software developer like myself who is not also heavily entrenched in how databases work under the covers, it is easy to assume that a database timestamp column would operate in roughly the same manner. Why would a database data type known as a "timestamp" not be able to represent an instant in time without needing additional information? But I'm starting to suspect that is what is going on here.

    My next step was to try to research the definition of a DB2 timestamp, which is actually rather hard to find. I've tried multiple searches and gone through multiple pages of search results for each variation of my search text and have yet to find a clear definition of what a DB2 timestamp is. I still don't know, but I'll keep searching.

    If my suspicions about all of this are correct, then the issue is further confused by many database products that allow you to turn on display of timezone for timestamp columns. If DB2 timestamps indeed don't know the timezone and don't store the timestamp in a timezone agnostic way, then these products should never show a timezone for a DB2 timestamp. As in my example, they should only show this: "2015-11-1 01:55:00" because there is no way to know what the correct local timezone would be.

    I'm seriously distressed by all of this. That tech people might have come up with a database type called a timestamp that doesn't actually tell you what time it is, and that the datatype was built into Java JDBC as if it does tell you what time it is, and that none of this was taught to me as part of my Computer Science degree, nor was it ever mentioned by anyone in my last decade of software development in the Java field, nor can any information about it be easily found on the internet, and that I only happened across it because I took vacation time to research this issue. Please tell me that's not what's happening here. I really hope it's just some flaw in the version of DB2 I'm testing and not the normal case, because otherwise I can almost guarantee you that there are probably billions of date/times throughout millions of systems throughout the world that are storing the wrong date/times for timing events right now.

    My apologies if I sound pissed. It's just that I am pissed. Everything about this just seems wrong to me, from it's lack of visibility to it's global implications to the fact that no one anywhere seems to know or care about it. I'm embarrassed that I'm just now researching the issue and that I still don't have a clear answer, and yet others seem more oblivious than me. My head is figuratively about to explode over this.

    Okay...lets step back and simplify this, as if I was brand new to computer science, a brand new student if you will. Here's the question:

    How do you store an instant in time in a database?

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    DB2 on Z/os (starting from v10) supports datatype "timestamp with timezone"

    To find your DB2-server version and platform , check if an SQL query like the one below returns results (some versions/platforms of DB2 won't have the required view)

    select char(os_name, 10) as os_name
    ,char(os_version,5) as os_version
    ,char(os_release,5) as os_release
    ,char(host_name,15) as host_name
    ,configured_cpu as "#cpus"
    ,total_memory as ram
    ,char(os_full_version,10) as os_full_version
    ,char(os_kernel_version,10) as os_kernel_version
    ,char(os_arch_type,5) as os_arch_type
    from sysibmadm.env_sys_info
    with ur;

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    For DB2 LUW, it is up to the application to define what the timestamp means. In our database we have timeststamps that are in UTC--these are for storing when something happened. This is system wide, and uses the DB2 server as the source. We also have timestamps that are in local time, the time zone is implied based on what state (US) it resides in. But both meaning are stored in the same type of datatype-- TIMESTAMP--because there is nothing else to store this type of data.

    Why is it up to the database system to determine the meaning of the data?

    Andy

  7. #7
    Join Date
    Sep 2015
    Posts
    5
    Quote Originally Posted by ARWinner View Post
    Why is it up to the database system to determine the meaning of the data?

    Andy
    I would argue because that is what would actually make a timestamp data type useful. When you want to store a "timestamp", something of the nature '2015-9-9 9:45:41', you don't really want to store '2015-9-9 9:45:41', you want to store when something happened. If you want to store when something happened, then you store it as a measurement from a known fixed point in time. Because '2015-9-9 9:45:41' doesn't tell you enough information. It doesn't matter if you want to see it in your local time, or GMT, or whatever, that's just an artifact of how you want it displayed.

    If I'm to accept that the DB2 timestamp has no such frame of reference, I can demonstrate a myriad of problems this causes. My first example program is one demonstration. You can't just leave the timestamps as is, now you have to also somehow store the timezone or convert the timestamp to some common base like GMT. Now the external application, and every developer that ever touches it, has to know to do this conversion when storing the timestamp and also when reading it back out. There is a somewhat straightforward way to do this in Java JDBC, but it starts getting uglier if you are accessing the data through an object relational model (ORM). With the ORM, now you need some extra custom code hacked in somewhere to manipulate the timestamps when storing and reading them back out. Perhaps the Java JDBC designers should have come up with a alternate class for storing these DB2 style timestamps and not base them off of the Java Date object that internally has a frame of reference; this would have helped eliminate much of the confusion. But they didn't, suggesting they had trouble as well trying to match the database data types to the application data types. More problems. The "black box" is broken, and you can't count on storing a Java Timestamp in a database and getting it back the same without extra code and looking closely at how databases are implementing their timestamps.

    At the end of the day, had the timestamp data type had a frame of reference to begin with, all of these problems would go away and everything would work beautifully.

    I'm about ready to take this back to the Java forums. I need to take a deeper dive into Java JDBC and also see if any Java experts have information on how to deal with the problem in Java. I still don't know how to fix the problem, but maybe I'm at least starting to understand it.
    Last edited by Skotty; 09-09-15 at 12:36.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Again, it is not the responsibility of the database to determine how the data gets used, and as such its meaning. If a timestamp needs to be further defined as which time zone it refers to, then that information needs to be stored in the database as well--this is not a database need, but an application need--the application is defining the meaning. It is the application's responsibility to ensure that the data is accurate.

    The database is just a repository. Many programs (which I am referring to as the application) can interface with this repository. These can be written in numerous languages and they all can and do represent a timestamp differently internally. If one program interprets a timestamp differently than another, then that is the fault of the application, not the database.

    DB2 timestamps are indeed stored as a value that can be compared and have arithmetic (adding days, hours, etc) performed on them and giving accurate results. This does mean that they are a measurement from a fixed point. What that point is is defined by the application.

    For example these two timestamps are equal: 2015-09-09-15.53.50.189998 2015-09-09-19.53.50.189998 They appear differently because the first one is US Eastern Daylight Savings and the other is GMT. Technically they are identical, but they would be stored differently--that is because the meaning of them is different. Again this is the application (me) defining the meaning.

    Andy

  9. #9
    Join Date
    Sep 2015
    Posts
    5
    Quote Originally Posted by ARWinner View Post
    Again, it is not the responsibility of the database to determine how the data gets used, and as such its meaning. If a timestamp needs to be further defined as which time zone it refers to, then that information needs to be stored in the database as well--this is not a database need, but an application need--the application is defining the meaning. It is the application's responsibility to ensure that the data is accurate.

    The database is just a repository. Many programs (which I am referring to as the application) can interface with this repository. These can be written in numerous languages and they all can and do represent a timestamp differently internally. If one program interprets a timestamp differently than another, then that is the fault of the application, not the database.

    DB2 timestamps are indeed stored as a value that can be compared and have arithmetic (adding days, hours, etc) performed on them and giving accurate results. This does mean that they are a measurement from a fixed point. What that point is is defined by the application.

    For example these two timestamps are equal: 2015-09-09-15.53.50.189998 2015-09-09-19.53.50.189998 They appear differently because the first one is US Eastern Daylight Savings and the other is GMT. Technically they are identical, but they would be stored differently--that is because the meaning of them is different. Again this is the application (me) defining the meaning.

    Andy
    Each data type in a database is there to serve a need. What need does a database Timestamp serve? I would argue that it would have been most useful if a Timestamp was there to store an instant in time, without needing additional outside information to understand how to interpret it. But that's apparently not the case.

    I'm sure some folks will just say I'm wrong because I'm not a regular poster and not a recognized expert. But at this point, if my latest understanding is finally correct, I would say there are two groups of people who didn't design things as well as they should have.

    1) Database developers that came up with and implemented the Timestamp data type for databases. As a developer who as worked on a lot of different systems, I can say that there is a frequent need to store when things happen. It would have been ideal if a Timestamp met this need. I would argue it doesn't. It doesn't because the Timestamp data type fails to encapsulate all of information needed to properly store an instant in time. And applications are forced to all externally know how to "patch in" the missing information. This makes the Timestamp data type not nearly as useful as it could have been.

    2) Java developers that came up with and implemented the Timestamp data type in Java. The Java Timestamp class DOES have all of the information needed to identify an instant in time, because it is based on the Java Date class which is defined as an offset from a specific date in UTC. Thus, the Java Timestamp does not appear to properly reflect what the database Timestamp is. This is a recipe for mass confusion and incorrect usage. I would be really interested in talking to them to see why they implemented it the way they did. Are there some brands of database with Timestamp data types that behave differently, and they didn't want to have to define vendor specific Java data types?

    To understand how things have gone horribly wrong, I would point back to my original demonstration program. If you were to survey Java developers what the output to that program would be, I would put big money down that over 90% of Java developers would get the answer wrong. If I wasn't betting money, I'd probably guess over 99%. It is short and clean, and looks like it should simply store and retrieve a timestamp, and that it's value should remain the same. But that's not what happens. Another hint that things have gone horribly wrong is that I've literally spent days trying to figure this out now on my own free time; a good design shouldn't require so much effort to figure out something that should have been relatively simple.

    If you are interested in the discussion on the Java side, I started a thread on the Oracle Java community forums under the title "Handling of Timezones and Daylight Savings Time (DST) in Timestamps" under the Java Database Connectivity (JDBC) space.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Do you expect DB2 to supply you with every possible data type you might need? That is absurd. DB2 gives you the primitive data types necessary to do any task. If you need to store the timezone, then you need to create a place to put it. You are free to create UDT to suit your needs.

    I do not think DB2 is to blame for Java's shortcomings. It is the developer's responsibility to ensure the integrity of the data. If they use constructs that cause situations like this, then, in my opinion, they are doing it wrong. There is the old adage I learned 30 years ago that will always apply: garbage in -- garbage out.

    As for saving when something happens, I would suggest that the database server's time would be more accurate then relying on an application's supplied time. That is why when we timestamp things we use the special registers (CURRENT TIMESTAMP - CURRENT TIMEZONE) to store the GMT of the timestamp. Again our application knows that this is GMT and deals with it accordingly.

    Andy

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •