Solved

Timestamp retrieval

Posted on 2004-09-02
5
1,115 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
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.
Suggested Courses

632 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