Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: Conversion of integer to timestamp format

    Hi,

    How can I convert an integer to db2 timestamp format? For example, I have the number 1240341010 and would like to convert it to a format like 2009-10-10-05.00.00.000000

    Thanks!!

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink

    select timestamp(date(1240), time('3:41')) + 010 microsecond
    from sysibm.sysdummy1

    Lenny

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    The internal representation of a timestamp is a string of 10 bytes.
    Each byte consists of 2 packed decimal digits.
    The first 4 bytes represent the date, the next 3 bytes the time, and the last 3 bytes the microseconds.

    Lenny

  4. #4
    Join Date
    Dec 2002
    Posts
    123
    thanks for the response -- this is what I get back :

    db2 "select timestamp(date(1240), time('3:41')) + 010 microsecond from sysibm.sysdummy1"

    1
    --------------------------
    0004-05-24-03.41.00.000010


    It should give -- 2009-04-21-19.10.10
    I forgot to mention that the integer is the number of seconds since Jan 1, 1970... in any case, I appreciate the response!
    Last edited by db2user; 10-15-09 at 14:14.

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    you storing timestamp in integer field?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Dec 2002
    Posts
    123
    Someone i used to work with wrote a program that inserts time into a table in integer format.. bad idea I know!

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up

    Quote Originally Posted by db2user
    thanks for the response -- this is what I get back :

    db2 "select timestamp(date(1240), time('3:41')) + 010 microsecond from sysibm.sysdummy1"

    1
    --------------------------
    0004-05-24-03.41.00.000010


    It should give -- 2009-04-21-19.10.10
    I forgot to mention that the integer is the number of seconds since Jan 1, 1970... in any case, I appreciate the response!
    Code:
    select timestamp(date('1970-01-01'), time('00:00:00')) + 1240341010 second NTMS
    from sysibm.sysdummy1
    NTMS
    2009-04-21 19:10:10.000000
    It's Funny

    Lenny Kh.

  8. #8
    Join Date
    Dec 2002
    Posts
    123
    great, thank you!!

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by db2user
    great, thank you!!
    No problem !

Posting Permissions

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