Doctimestamp in WM database and timezone

, ,
1

Hello

I have an issue while trying to query the bizdoc table from the webMethods TN schema.
My problem is the display of “doctimestamp” record : I typically use the following query

select to_char (DOCTIMESTAMP,‘YYYY/MM/DD HH24:MI:SS.FF’) as DOCTIMESTAMP from bizdoc where DOCID=‘54cfpp008peddb2400000iti’

The issue here is that it display me the UTC time which is not my exact timezone (depends of winter time UTC+1 or UTC+2).

Is there a possibility to retrieve the good display of this record ?

Sample :
result of the query : 2014/06/02 14:16:12.725
a simple select sysdate from dual at the same time : 2014/06/02 16:16:12.725

2

try this with Oracle:
select
CAST((from_tz(cast(DOCTIMESTAMP AS TIMESTAMP),‘GMT’) AT TIME ZONE ‘CET’) AS DATE)
from table;

1 Like
3

Hi,

Tong wang is right. Just adding another one.

  1. SELECT FROM_TZ(CAST(TO_DATE(DOCTIMESTAMP,
    ‘YYYY-MM-DD HH:MI:SS.FF’) AS TIMESTAMP), ‘America/New_York’)
    AT TIME ZONE ‘asia/calcutta’ “Converted Time”
    FROM DUAL;

    Sample output: 01-DEC-99 21.30.00.000000000 ASIA/CALCUTTA (Input: “1999-12-01 11:00:00”)

  2. ALTER SESSION SET TIME_ZONE = ‘asia/calcutta’;
    select CURRENT_TIMESTAMP from dual;

or

ALTER SESSION SET TIME_ZONE = '+5:30';
select CURRENT_TIMESTAMP from dual;

Hope this will help you.

4

Hello

It helped me :smiley:
I just have another question. Would it be possible to format the result using a to_char function ?
I tried it but have now a sql error (date format not recognizable)

EDIT : I found the solution

select
to_char (CAST((from_tz(cast(DOCTIMESTAMP AS TIMESTAMP),‘GMT’) AT TIME ZONE ‘CET’) AS TIMESTAMP) ,‘YYYY/MM/DD HH24:MI:SS.FF’)
from table;
In any cases thanks for your help both :!:

5

Hi Julien,
While you have got the detail that you are looking for, just want to add that, there are two built-in services which would retrieve the content of bizdoc by providing the document id if you want to use it for some processing.

wm.tn.doc:view, and wm.tn.doc:viewAs

My 2 cents…

-Senthil