Results 1 to 14 of 14
  1. #1
    Join Date
    May 2009
    Posts
    11

    Unanswered: Conversion with Dates

    According to a quick google, the JMSTimestamp is:
    The value is in the format of a normal millis absolute time value in the Java programming language, which is the difference, measured in milliseconds, between the a given time and midnight, January 1, 1970 UTC.

    SET @MiliSeconds = 1241091011533
    SELECT DATEADD(MS, @MiliSeconds % 1000, DATEADD(SECOND, @MiliSeconds / 1000, '19700101'))

    How to use this in DB2 above command is for Microsoft SQL Server only.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    select ms + (miliseconds * 1000) MICROSECONDS

    Andy

  3. #3
    Join Date
    May 2009
    Posts
    11
    How to do this in DB2

    String s = "1241436617660";
    java.sql.Date sqlDate = new java.sql.Date(Long.parseLong(s.trim()));

    System.out.println("utilDate:" + utilDate);
    System.out.println("sqlDate:" + sqlDate);

    Result -
    utilDate:Mon May 04 13:04:38 EDT 2009
    sqlDate:2009-05-04

    I want to give input as string "1241436617660" in DB2 and get result as "Mon May 04 13:04:38EDT 2009". Which query can give such results.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You want to use java.sql.Timestamp, not java.sql.Date.

    Andy

  5. #5
    Join Date
    May 2009
    Posts
    11
    This is not giving Time correctly, how do we use this

    select
    DATE(
    TIMESTAMP('1970-01-01','00.00.00')
    + (1241436617660 <from table>/1000) seconds ) , TIME(
    TIMESTAMP('1970-01-01','00.00.00')
    + (<from table>/1000) seconds ) from <table_name>

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the data type of the column you are using?

    And, what is the result you are getting?

    Andy

  7. #7
    Join Date
    May 2009
    Posts
    11
    Datatype is xml type, have used Xquery to fetch from the XML
    timestampis varchar(2000) and then casted to decimal CAST(t.timestampis as DECIMAL(31,0)

    --QYERY IS ---

    select t.destination, t.message_id,v.ts, DATE(
    TIMESTAMP('1970-01-01','00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) , TIME(
    TIMESTAMP('1970-01-01-00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds )
    ,t.timestampis,t.bodyis
    from table_UNDELIVERED v,
    xmltable( 'declare namespace jms1="http://www.abc.com/namespaces/tnt/plugins/jms"; $c/jms1:ActivityOutput' passing v.message as "c"
    columns
    destination varchar(200) path 'declare namespace jms1="http://www.abc.com/namespaces/tnt/plugins/jms";JMSHeaders/jms1:JMSDestination' ,
    message_id varchar(200) path 'declare namespace jms1="http://www.abc.com/namespaces/tnt/plugins/jms"; JMSHeaders/jms1:JMSMessageID',
    timestampis varchar(200) path 'declare namespace jms1="http://www.abc.com/namespaces/tnt/plugins/jms"; JMSHeaders/jms1:JMSTimestamp',
    bodyis varchar(2000) path 'declare namespace jms1="http://www.abc.com/namespaces/tnt/plugins/jms"; Body'
    ) as t
    where (v.ts < current timestamp ) and (v.ts + 24 hours> current timestamp )

    RESULT SHOULD show 5th column - TIME as 07:30:47 AM but its showing as 11:30:17 ,looks to me as

    TIME(
    TIMESTAMP('1970-01-01-00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds )

    is not correct

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Add these columns to your query and see if they are correct:

    TIMESTAMP('1970-01-01-00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds )


    CAST(t.timestampis as DECIMAL(31,0))/1000

    Andy

  9. #9
    Join Date
    May 2009
    Posts
    11
    Hi Andy,

    These cloumns alrerady exist ....

    select
    DATE(
    TIMESTAMP('1970-01-01','00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) , TIME( TIMESTAMP('1970-01-01-00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds )
    from <table name>

    Result for second column (TIME) is not correct, its giving different values...

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think you misunderstood me. Change this query:


    select
    DATE(
    TIMESTAMP('1970-01-01','00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) , TIME( TIMESTAMP('1970-01-01-00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds )
    from <table name>

    to this:


    select
    DATE(
    TIMESTAMP('1970-01-01','00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) , TIME( TIMESTAMP('1970-01-01-00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ),
    TIMESTAMP('1970-01-01-00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ),
    TIMESTAMP('1970-01-01-00.00.00')
    CAST(t.timestampis as DECIMAL(31,0))/1000
    from <table name>

    Then see if the value look OK for the ne columns.


    Andy

  11. #11
    Join Date
    May 2009
    Posts
    11
    Can you confirm, query has some problem.. what are 3rd and 4th select doing...

    select

    DATE(TIMESTAMP('1970-01-01','00.00.00') + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) ,

    TIME( TIMESTAMP('1970-01-01-00.00.00') + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ),

    TIMESTAMP('1970-01-01-00.00.00') + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ),

    TIMESTAMP('1970-01-01-00.00.00')
    CAST(t.timestampis as DECIMAL(31,0))/1000

    from <table name>


    This doesnot work...

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This doesnot work...
    What means that?
    Did you got error message(s)?
    Or, did you got wrong(different from your expectations) results?

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by funnyme.25
    Can you confirm, query has some problem.. what are 3rd and 4th select doing...

    select

    DATE(TIMESTAMP('1970-01-01','00.00.00') + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) ,

    TIME( TIMESTAMP('1970-01-01-00.00.00') + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ),

    TIMESTAMP('1970-01-01-00.00.00') + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ),

    TIMESTAMP('1970-01-01-00.00.00')
    CAST(t.timestampis as DECIMAL(31,0))/1000

    from <table name>


    This doesnot work...

    There is a comma missing, it should be:

    select
    DATE(
    TIMESTAMP('1970-01-01','00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) , TIME( TIMESTAMP('1970-01-01-00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ),
    TIMESTAMP('1970-01-01-00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ),
    TIMESTAMP('1970-01-01-00.00.00'), -- missing comma
    CAST(t.timestampis as DECIMAL(31,0))/1000
    from <table name>

    Andy

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) I thought that you want to add "TIMESTAMP(" like following code.
    2) I want to recommend strongly to format your code with new line and indention(and use [code] tag to keep them on your post).
    It will be usefull to find simple syntax errors(like missing or extra commas, unpaired parentheses, so on) on your code.
    Code:
    select
           DATE(
                TIMESTAMP('1970-01-01','00.00.00')
                + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) ,
           TIME(
                TIMESTAMP('1970-01-01-00.00.00')
                + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) ,
           TIMESTAMP(
                TIMESTAMP('1970-01-01-00.00.00')
                + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) ,
           TIMESTAMP('1970-01-01-00.00.00'), -- missing comma
           CAST(t.timestampis as DECIMAL(31,0))/1000
      from <table name>

Posting Permissions

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