Re: Pro*C and TO_DATE queries into long variables

In article <5dac3ab3.0108151205.7c74efc_at_posting.google.com>, carygerber_at_yahoo.com says...
>
>I searched on how to store dates in minutes or seconds since 1970, and
>found how to do this from discussion groups like these. However, when
>I implement C code to do certain date conversion queries into long
>variables, I get incorrect results. We opened a case number, but the
>response was that this was not a bug, development has known of this
>behavior for years, but they say it is not a bug, therefore there is
>no issue.
>
>Please review the following. If I do not round the intermediate
>results in the query, I get an incorrect answer into a long host
>variable, but any double/float variable gets the right answer. Oracle
>is truncating incorrectly in regards to long storage.
>
>Please help me understand why I have to jump thru hoops to get the
>correct answer, when all 3rd party line editors (SQL processors, like
>PL/SQL, etc) return the correct answers.
>
>It is not a problem with C-conversion to integer (long). It is a
>problem with whatever PRO*C is generating. I have changed the query to
>return the same value 3 times, once into different data types of C. I
>further do a C-conversion of a float to integer and do not get the
>same integer value that the query returns into my int host variable.
>
>Connect [rcWfh]XZ%h\knc_b^cnnbgr] -> ediuser/ediuser
>Seconds without Round -> 16612342.000000, 16612342.000000, 16612341
>(returned from query into long host variable)
>C conversion to int -> 16612342 (same variable, just assigned the int.
>truncated value from the double float above)
>Seconds with Round -> 16612342.000000, 16612342.000000, 16612342
>C conversion to int -> 16612342
>
>

Well, this is a very common problem with all MIXED mode arithemtic in all languages. I remember doing stuff with PL/I with fix bin(15), pic'999.99' and float bin(21) -- boy, could you get some weird answers with the conversions going on.

The problem is you are taking a floating point number from Oracle and having it convert it to to a different mode (an int).

By using the ROUND -- you are avoiding the issue -- you took that number that was a float an in the database converted it, implicitly cast it, as an integer. Now the database sees this as a number(38,0) (no decimal) and everything rounds/truncates the right way. Before it was more like "38 digits with a scale of whatever you need".

Interestingly enough -- 1440 is the wrong thing to multiply by - it is really 24*60*60 = 86400 and once you do that (since date1-date2 = partial day, you must multiply by 24 hours * 60 minutes * 60 seconds to get the seconds between)

Seconds without Round -> 996740520.000000, 996740544.000000, 996740519 C conversion to int -> 996740520

so, whats up with that float now? thats actually OK since a float has 6 digits of precision and a double has 13 -- mixed mode arithmetic again.

So, just like you cast in C, you can cast in SQL:

    exec sql select

        cast( (TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss') - 
              TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 86400 
                      as number(38) ),
        cast( (TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss') - 
              TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 86400 
                      as number(38) ),
        cast( (TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss') - 
              TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 86400 
                      as number(38) )
        into :dsecs_since_70, :fsecs_since_70, :isecs_since_70
        from DUAL;

that'll get you:

Seconds without Round -> 996740520.000000, 996740544.000000, 996740520 C conversion to int -> 996740520

showing that the float should NOT be used and double or long are OK cause this number cannot have any scale (no wacky conversions to go through).

Beware mixed mode math -- always be on the lookout for it, always beware of it (avoid it when you can). An explicit cast here is the right thing to do.

Also, you might consider that the unix number of seconds since midnight is from UTC -- you really need to add a new_time call in there to correct for timezones -- so:

cast( (new_time(sysdate,'EDT','GMT') -

                     to_date('01-jan-1970','dd-mon-yyyy')) * (24*60*60) 
      as number(38) )

might be more appropriate (change EDT to your timezone of course) ....

>
>/***** INCLUDES HERE ******/
>int main(int argc, char *argv[])
>{
> int count = 0;
> int ret = 0;
>
> EXEC SQL begin declare section;
> char connect[16];
> EVENT_DS one_evt;
>
> /**** TEST ****/
> double dsecs_since_70;
> float fsecs_since_70;
> long isecs_since_70;
> EXEC SQL end declare section;
>
> /****** CONNECT HERE *****/
>
> EXEC SQL set transaction isolation level read committed;
> if(!SQLOK()) exit(1);
>
>
> /***** TEST *****/
> exec sql select
> (TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss') -
>TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440,
> (TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss') -
>TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440,
> (TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss') -
>TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440
> into :dsecs_since_70, :fsecs_since_70, :isecs_since_70
> from DUAL;
> if (!SQLOK()) exit(1);
>
> printf("Seconds without Round -> %lf, %f, %ld\n", dsecs_since_70,
>fsecs_since_70, isecs_since_70);
> isecs_since_70 = (long)dsecs_since_70;
> printf("C conversion to int -> %ld\n", isecs_since_70);
>
> exec sql select
> ROUND((TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss')
>- TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440),
> ROUND((TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss')
>- TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440),
> ROUND((TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss')
>- TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440)
> into :dsecs_since_70, :fsecs_since_70, :isecs_since_70
> from DUAL;
> if (!SQLOK()) exit(1);
>
> printf("Seconds with Round -> %lf, %f, %ld\n", dsecs_since_70,
>fsecs_since_70, isecs_since_70);
> isecs_since_70 = (long)dsecs_since_70;
> printf("C conversion to int -> %ld\n", isecs_since_70);
>}

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp