Solved

Timestamp retrieval

Posted on 2004-09-02
5
1,112 Views
Last Modified: 2012-08-14
Need your help.

How to retrieve SQLT_TIMESTAMP binary representation (including fraction) in Oracle9 using dynamic embedded SQL ("Method 4": declare->open->describe->fetch->close), and what is the struct format (SQLDA::V[i] for timestamp [when SQLDA::T[i]=SQLT_TIMESTAMP])?

Thanks in advance!
0
Comment
Question by:Demiurg
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 11963078
Can you elaborate ? Is it just me having hard time understanding your question?

are you trying to retrieve the timestamp and display them?

0
 
LVL 4

Author Comment

by:Demiurg
ID: 11965057
I write a program (MSVC 6.0) that communicates with Oracle 9 using dynamic embedded SQL.
I want to fetch value(s) (using cursor) of a column of type SQLT_TIMESTAMP. After "prepare" statement sqlda items contain: T[i]=187 (SQLT_TIMESTAMP), L[i]=11 (seems like timestamp struct is 11 bytes long). I assign V[i] to a pointer to 128-byte array (array is zeroed). When "fetch" statement is executed, program fails with exception (memory access violation). Then i tried to put a pointer to another memory block at the beginning of 128-byte array (rest of it is still zeroed). In this case that block is filled with struct like
{
short year;
char month;
char day;
char hour;
char minute;
char second;
}, and than 13 zero bytes except for 15th, which equal to 0x03.
The question now is where is fraction (the column has 6 digits of fraction).
In general, i need to retrieve timestamp not just to display it, but to use it in calculations, thus using string representation is risky due to possible different time formats, so i need some fixed binary one. The workaround i know is to use "EXEC SQL ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD-HH24.MI.SSXFF'" and then fetch it as a string (overriding T[i]=5, for example), but i'd prefer not to use it.
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 11995837
The safest way is to use OCI function.

void OCIDateTimeGetTime ( dvoid         *hndl,
                          OCIError      *err,
                          OCIDateTime   *datetime,
                          ub1           *hour,
                          ub1           *min,
                          ub1           *sec,
                          ub4           *fsec );

SQL> select dump(current_timestamp),current_timestamp from dual;
DUMP(CURRENT_TIMESTAMP)
--------------------------------------------------------------------------------
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
Typ=188 Len=20: 212,7,9,7,11,37,26,71,40,144,244,57,1,0,5,68,0,0,0,77
07-SEP-04 12.37.26.972329 +01:00

You can see century + 100, decade + 100, month, day, hh24, mi, ss in the byte stream but also there is a GMT offset and fractional seconds there too.
Oracle are reluctant to let you know these formats because they may change in the future so would like you to use functional api to isolate functionality from the implementation,
0
 
LVL 4

Author Comment

by:Demiurg
ID: 11996236
Thanks!
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11996736
http://download-east.oracle.com/otn_hosted_doc/jdeveloper/904preview/jdbc-javadoc/oracle/sql/TIMESTAMPTZ.html

Byte       Represents
0         Century (119 for 1990)
1         Decade  (190 for 1990)
2         Month
3         Day      
4         Hour
5         Minute
6         Seconds
7         Nanoseconds (Most Significant bit)
8         Nanoseconds
9         Nanoseconds
10        Nanoseconds (Least Significant Bit)
11,12     Region id or Timezone Hour/Minute        
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 76
Oracle SQL Select unique values from two columns 4 53
Repeat query 13 46
Oracle encryption 12 31
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question