Timestamp retrieval

Posted on 2004-09-02
Medium Priority
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!
Question by:Demiurg
  • 2
  • 2
LVL 23

Expert Comment

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?


Author Comment

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

Accepted Solution

earth man2 earned 2000 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;
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 +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,

Author Comment

ID: 11996236
LVL 22

Expert Comment

by:earth man2
ID: 11996736

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        

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

592 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