Llayland’s Food, Oracle, and Haskell

August 7, 2008

Default precision fractional for timestamp datatype is 6. Huh?

Filed under: Oracle — llayland @ 6:09 am

I’ve always read that the default precision for timestamps was 6.

From “Oracle® Database SQL Language Reference 11g Release 1 (11.1) Part Number B28286-03

TIMESTAMP Datatype

The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. This datatype is useful for storing precise time values. Specify the TIMESTAMP datatype as follows:

TIMESTAMP [(fractional_seconds_precision)]

where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this datatype, the value can be a number in the range 0 to 9. The default is 6.

I found a fun little case where the default precision is 0. It was the end of a long day when I did something similar to this:

SQL> create or replace type timestamp_tab as table of timestamp;
2 /

Type created.

SQL>
SQL> create or replace function get_ts return timestamp_tab
2 is
3 retval timestamp_tab;
4 begin
5 select
6 to_timestamp('20080102','YYYYMMDD')
7 + interval '0.1' second * (rownum - 2)
8 bulk collect into retval
9 from dual
10 connect by level <= 3;
11
12 return retval;
13 end get_ts;
14 /

Function created.

SQL>
SQL> begin
2 for rec in (select * from table(get_ts)) loop
3 dbms_output.put_line(rec.column_value);
4 dbms_output.put_line(to_char(rec.column_value, 'FF'));
5 end loop;
6 end ;
7 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000

PL/SQL procedure successfully completed.

Strange, I’ve lost my fractional seconds. Maybe I need to explicitly set the precision in my type:


SQL> create or replace type timestamp_tab as table of timestamp(4);
2 /Type created.

SQL>
SQL> begin
2 for rec in (select * from table(get_ts)) loop
3 dbms_output.put_line(rec.column_value);
4 dbms_output.put_line(to_char(rec.column_value, 'FF'));
5 end loop;
6 end ;
7 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000

PL/SQL procedure successfully completed.

No dice. Let’s go back to basics to make sure I’m not just braindead at this point:


SQL> begin
2 dbms_output.put_line(
3 to_timestamp('20080102','YYYYMMDD') + interval '0.1' second * -1
4 );
5 end;
6 /
2008-01-01 23:59:59.900000000PL/SQL procedure successfully completed.

SQL>
SQL> begin
2 for rec in (
3 select to_timestamp('20080102','YYYYMMDD')
4 + interval '0.1' second * -1 ts
5 from dual
6 ) loop
7 dbms_output.put_line(rec.ts);
8 end loop;
9 end;
10 /
2008-01-01 23:59:59.900000000

PL/SQL procedure successfully completed.

So, I do normally get six digits by default.  Maybe it is a bulk collect issue?


SQL> create or replace function get_ts2 return timestamp_tab
2 is
3 retval timestamp_tab := timestamp_tab();
4 begin
5 retval.extend(3);
6
7 for rec in (
8 select rownum n,
9 to_timestamp('20080102','YYYYMMDD')
10 + interval '0.1' second * (rownum - 2) ts
11 from dual
12 connect by level <= 3
13 ) loop
14 retval(rec.n) := rec.ts;
15 end loop;
16 return retval;
17 end get_ts2;
18 /Function created.

SQL>
SQL> begin
2 for rec in (select * from table(get_ts2)) loop
3 dbms_output.put_line(rec.column_value);
4 dbms_output.put_line(to_char(rec.column_value, 'FF'));
5 end loop;
6 end ;
7 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000

PL/SQL procedure successfully completed.

Nope.  Maybe an explicit cursor?


SQL>
SQL> declare
2 cursor ts_cur is
3 select * from table(get_ts);
4 begin
5 for rec in ts_cur loop
6 dbms_output.put_line(rec.column_value);
7 dbms_output.put_line(to_char(rec.column_value, 'FF'));
8 end loop;
9 end ;
10 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000PL/SQL procedure successfully completed.

Nope.  Hmm, I vaguely remember that you can declare the return type of a cursor but I’ve never had to do it before.


SQL>
SQL> declare
2 type ts_rec is record(ts timestamp);
3 cursor ts_cur return ts_rec is
4 select column_value from table(get_ts);
5 begin
6 for rec in ts_cur loop
7 dbms_output.put_line(rec.ts);
8 dbms_output.put_line(to_char(rec.ts, 'FF'));
9 end loop;
10 end ;
11 /
2008-01-01 23:59:59.900000
900000000
2008-01-02 00:00:00.000000
000000000
2008-01-02 00:00:00.100000
100000000PL/SQL procedure successfully completed.

Aha, so the default precision for a timestamp when returned from a table function into a cursor is 0. Just for grins:


SQL>
SQL> declare
2 type ts_rec is record(ts timestamp(0));
3 cursor ts_cur return ts_rec is
4 select column_value from table(get_ts);
5 begin
6 for rec in ts_cur loop
7 dbms_output.put_line(rec.ts);
8 dbms_output.put_line(to_char(rec.ts, 'FF'));
9 end loop;
10 end ;
11 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000PL/SQL procedure successfully completed.

So, problem solved. Hopefully this will save someone out there some time and head scratching.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: