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.

select ms + (miliseconds * 1000) MICROSECONDS

Andy

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.

You want to use java.sql.Timestamp, not java.sql.Date.

Andy

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>

What is the data type of the column you are using?

And, what is the result you are getting?

Andy

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

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

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...

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

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...

What means that?
Did you got error message(s)?
Or, did you got wrong(different from your expectations) results?

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

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.