Solved

Timestamp retrieval

Posted on 2004-09-02
5
1,110 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.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now