what is the different between timestamp data type without parameter and with parameter 0:
timestamp
VS timestamp(0)
The TIMESTAMP datatype is an extension of the DATE datatype. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE datatype.
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision.
Oracle Database has five date-time data types: TIMESTAMP. TIMESTAMP WITH TIME ZONE. TIMESTAMP WITH LOCAL TIME ZONE.
@JoseDavidGarciaLlanos - A DATE stores a time to the second. A TIMESTAMP can store up to 9 digits of fractional seconds. You can also declare a TIMESTAMP WITH TIME ZONE or a TIMESTAMP WITH LOCAL TIME ZONE that stores a time zone as well so that you can convert times from one time zone to another.
The number in parentheses specifies the precision of fractional seconds to be stored. So, (0)
would mean don't store any fraction of a second, and use only whole seconds. The default value if unspecified is 6 digits after the decimal separator.
So an unspecified value would store a date like:
TIMESTAMP 24-JAN-2012 08.00.05.993847 AM
And specifying (0)
stores only:
TIMESTAMP(0) 24-JAN-2012 08.00.05 AM
See Oracle documentation on data types.
Quite simply the number is the precision of the timestamp, the fraction of a second held in the column:
SQL> create table t23
2 (ts0 timestamp(0)
3 , ts3 timestamp(3)
4 , ts6 timestamp(6)
5 )
6 /
Table created.
SQL> insert into t23 values (systimestamp, systimestamp, systimestamp)
2 /
1 row created.
SQL> select * from t23
2 /
TS0
---------------------------------------------------------------------------
TS3
---------------------------------------------------------------------------
TS6
---------------------------------------------------------------------------
24-JAN-12 05.57.12 AM
24-JAN-12 05.57.12.003 AM
24-JAN-12 05.57.12.002648 AM
SQL>
If we don't specify a precision then the timestamp defaults to six places.
SQL> alter table t23 add ts_def timestamp;
Table altered.
SQL> update t23
2 set ts_def = systimestamp
3 /
1 row updated.
SQL> select * from t23
2 /
TS0
---------------------------------------------------------------------------
TS3
---------------------------------------------------------------------------
TS6
---------------------------------------------------------------------------
TS_DEF
---------------------------------------------------------------------------
24-JAN-12 05.57.12 AM
24-JAN-12 05.57.12.003 AM
24-JAN-12 05.57.12.002648 AM
24-JAN-12 05.59.27.293305 AM
SQL>
Note that I'm running on Linux so my TIMESTAMP
column actually gives me precision to six places i.e. microseconds. This would also be the case on most (all?) flavours of Unix. On Windows the limit is three places i.e. milliseconds. (Is this still true of the most modern flavours of Windows - citation needed).
As might be expected, the documentation covers this. Find out more.
"when you create timestamp(9) this gives you nanos right"
Only if the OS supports it. As you can see, my OEL appliance does not:
SQL> alter table t23 add ts_nano timestamp(9)
2 /
Table altered.
SQL> update t23 set ts_nano = systimestamp(9)
2 /
1 row updated.
SQL> select * from t23
2 /
TS0
---------------------------------------------------------------------------
TS3
---------------------------------------------------------------------------
TS6
---------------------------------------------------------------------------
TS_DEF
---------------------------------------------------------------------------
TS_NANO
---------------------------------------------------------------------------
24-JAN-12 05.57.12 AM
24-JAN-12 05.57.12.003 AM
24-JAN-12 05.57.12.002648 AM
24-JAN-12 05.59.27.293305 AM
24-JAN-12 08.28.03.990557000 AM
SQL>
(Those trailing zeroes could be a coincidence but they aren't.)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With