Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    1

    Red face Unanswered: New to DB2 - Question about Timestamp field

    Good Day,

    IBM Db2 Database for Windows Version 9.
    About DB2 Administration Tools Environment
    DB2 administration tools level:
    Product identifier SQL09019
    Level identifier 020A0107
    Level DB2 v9.1.900.215
    Build level s100326
    PTF WR21463
    Java development kit (JDK):
    Level IBM Corporation 1.5.0
    Windows 2008 64 bit

    I am very new to DB2 and database administration. I have a DB2 database that I am looking at for the first time. My intentions are to connect to the DB2 database for doing reporting on a Service Management Call tracking tool. DB2 is the database for our Call tracking application. Currently, I connect to the database via an HP ODBC driver. This driver then connects to the application to get the data for my reporting. We don't directly connect to the DB2 database. (I hope that makes sense).

    In our application, there is a table called CLOCKS. This table contains 14 columns. 4 of the 14 fields are Data Type TIMESTAMP. (Length 10) Nullable Yes.

    In the application, the date in those four fields are stored as an elapsed time.
    If a ticket in the system has been opened for 12 minutes, the field looks like 00:12:14 in the application.

    When I look in DB2, the information does not look like 00:12:14. It looks like Jan 1, 4000 12:10:59 AM 000000. When I click into the field in DB2, it then looks like 4000-01-01 00:10:59.0.

    The HP ODBC driver "translates" the proper time for me when I go through the application. But, it is so slow to query. I would ideally like to go directly against the DB2 database (bypassing the application altogether).

    Can anyone tell me why the field in DB2 stores the timestamp as Jan 1, 4000 12:10:59 AM 000000 instead of 00:12:14. Or, can someone tell me how I can translate Jan 1, 4000 12:10:59 AM 000000 into 00:12:14?

    Any help is appreciated!

    Thanks!

    Jennifer

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A timestamp is the combination of a date and a time, i.e. 4000-01-01 as date and 12:10:59 as time in your case. From your explanation, a timestamp doesn't seem to be the correct approach - if you want to track durations, don't use a timestamp but rather some other data type like an INT or BIGINT and store the seconds in such a column. If you want to have absolute points in time, then a timestamp would be a good approach. So it boils down to using the correct data type for each type of data.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Jennifer, DB2 stores timestamp information in a table in a modified pack format.

    4000015000
    0011009000

    it is read top to bottom and left to right. You will notice that it takes 10 bytes to store.

    Beyond that, how it is displayed is up to the application.

    When you mention:
    When I look in DB2, the information does not look like 00:12:14. It looks like Jan 1, 4000 12:10:59 AM 000000. When I click into the field in DB2, it then looks like 4000-01-01 00:10:59.0.
    How are you looking at the data? Are you using Command Editor or Control Center with the result in a Grid? If you are, the Jan 1,... format is set in the application and (according to IBM) can't be changed.

    If you select the column so the output shows up in the result pane (you can change Tool Settings on the Command Editor tab and unchecking the 'Display Results for a single query on the Query Results pane'), you should see the (normal) output of:

    4000-01-01-00.10.59.000000

Posting Permissions

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