Interpretation of timestamp types in Oracle (timestamp storage format)

interpretation of timestamp types in Oracle

Sql> CREATE TABLE Test_time (col_time timestamp);

Table has been created.

sql> INSERT into test_time values (To_timestamp (' 0001-1-1 0:0:0.0 ', ' syyyy-mm-dd hh24:mi:ss.ff '));

1 lines have been created.

sql> INSERT into test_time values (To_timestamp (' 2000-1-1 0:0:0.0 ', ' syyyy-mm-dd hh24:mi:ss.ff '));

1 lines have been created.

sql> INSERT into test_time values (To_timestamp (' 9999-12-31 23:59:59.999999 ', ' syyyy-mm-dd hh24:mi:ss.ff '));

1 lines have been created.

sql> INSERT into test_time values (To_timestamp (' -0001-1-1 0:0:0.0 ', ' syyyy-mm-dd hh24:mi:ss.ff '));

1 lines have been created.

sql> INSERT into test_time values (To_timestamp (' -0100-3-4 13:2:3.234015 ', ' syyyy-mm-dd hh24:mi:ss.ff '));

1 lines have been created.

sql> INSERT into test_time values (Systimestamp);

1 lines have been created.

sql> INSERT into test_time values (To_timestamp (' 2000-1-1 0:0:0.123456789 ', ' syyyy-mm-dd hh24:mi:ss.ff9 '));

1 lines have been created.

Sql> commit;

Submit completed.

Sql> Select To_char (col_time, ' syyyy-mm-dd hh24:mi:ss.ff9 ') time, dump (col_time) dump_time
2 from Test_time;

Time Dump_time
------------------------------ ----------------------------------------------------
0001-01-01 00:00:00.000000000 typ=180 len=7:100,101,1,1,1,1,1
2000-01-01 00:00:00.000000000 typ=180 len=7:120,100,1,1,1,1,1
9999-12-31 23:59:59.999999000 typ=180 len=11:199,199,12,31,24,60,60,59,154,198,24

//The above time is divided into four parts stored separately: first and four according to the same rules into the timestamp variable, the second part of the original deposit, the third part of the Minutes in the second +1 after deposit.
-0001-01-01 00:00:00.000000000 typ=180 len=7:100,99,1,1,1,1,1
-0100-03-04 13:02:03.234015000 typ=180 len=11:99,100,3,4,14,3,4,13,242,201,24
2004-12-15 16:14:52.738000000 typ=180 len=11:120,104,12,15,17,15,53,43,252,252,128
2000-01-01 00:00:00.123457000 typ=180 len=11:120,100,1,1,1,1,1,7,91,205,232

7 rows have been selected.

In contrast to the date type, you can see that for the timestamp type, if you do not include microsecond information or a microsecond value of 0, the storage result is exactly the same as date. When the microsecond value is 0 o'clock, Oracle does not save the microsecond information in order to save space.

if the millisecond value is not 0,oracle, the microsecond value is saved as a 9-digit number.

For example 999999000, save for 59,154,198,24;234015000 Save as 13,242,201, 24.

Sql> Select To_char (999999000, ' xxxxxxxxxx ') from dual;

TO_CHAR (999
-----------
3b9ac618

Sql> Select To_number (' 3b ', ' xxx ') One, To_number (' 9a ', ' xxx ') two,
2 to_number (' C6 ', ' xxx ') three, to_number (' ', ' xxx ') four from dual;

One Two THREE FOUR
---------- ---------- ---------- ----------
59 154 198 24

Sql> Select To_char (234015000, ' xxxxxxxx ') from dual;

To_char (2
---------
df2c918

Sql> Select To_number (' d ', ' xxx ') One, To_number (' F2 ', ' xxx ') two,
2 to_number (' C9 ', ' xxx ') three, to_number (' ', ' xxx ') four from dual;

One Two THREE FOUR
---------- ---------- ---------- ----------
13 242 201 24

In addition, note that, without specifying the precision of the case, timestamp default to take 6 bits. The length is more than 6 digits and will be rounded up to 6 digits. If you want to save 9-bit timestamp, you must explicitly specify the precision.

Sql> ALTER TABLE Test_time Modify (col_time timestamp (9));

The table has changed.

sql> INSERT into test_time values (To_timestamp (' 2000-1-1 0:0:0.123456789 ', ' syyyy-mm-dd hh24:mi:ss.ff9 '));

1 lines have been created.

Sql> Select To_char (col_time, ' syyyy-mm-dd hh24:mi:ss.ff9 ') time, dump (col_time) dump_time
2 from Test_time;

time                            dump_time
------------------------------------------- --------------------------------------
 0001-01-01 00:00:00.000000000 typ=180 len=7:100,101,1,1,1,1,1
 2000-01-01 00:00:00.000000000 typ=180 len=7:120,100,1,1,1,1,1
 9999-12-31 23:59:59.999999000 Typ=180 len=11:199,199,12,31,24,60,60,59,154,198,24
-0001-01-01 00:00:00.000000000 typ=180 len=7:100,99,1,1,1,1,1
- 0100-03-04 13:02:03.234015000 typ=180 len=11:99,100,3,4,14,3,4,13,242,201,24
 2004-12-15 16:14:52.738000000 typ=180 len=11:120,104,12,15,17,15,53,43,252,252,128
 2000-01-01 00:00:00.123457000 Typ =180 len=11:120,100,1,1,1,1,1,7,91,205,232
 2000-01-01 00:00:00.123456789 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,21 has selected 8 rows   ==============================================

Select Systimestamp from dual

Select Current_timestamp from dual

Select CAST (sysdate as timestamp) "DATE" from dual

Select To_timestamp (to_date (sysdate, ' Yyyy-mm-dd ') from dual

Select To_timestamp (' 2008-11-17 00:31:35 ', ' yyyy-mm-dd HH24:MI:SS:FF ') from dual

What is the format of timestamp saved in Oracle? INSERT into table (Timestamp_field) VALUES (sysdate)
Or
Insert into table (Timestamp_field) VALUES (To_timestamp (to_date (' 2007-11-1 ', ' yyyy-mm-dd '))
After saving

Timestamp_field
-------------------------------
January-November-07 12.00.00.000000 a.m.

If you want to store date and time information in Oracle, you are actually choosing from two types of field data, let's look at the differences between the two data types and what they provide.



Date data type

This data type is so familiar that we think of date types when we need to represent dates and times. It can store months, years, days, centuries, hours, minutes, and seconds. It is typically used to indicate when something has happened or is going to happen. The problem with the date data type is that it indicates that the measure granularity of the time interval for two events is seconds. This problem will be solved when the article discusses timestamp later. You can use the To_char function to wrap date data in a traditional way to represent multiple formats.

Sql> Select To_char (date1, ' mm/dd/yyyy HH24:MI:SS ') "Date" from date_table;

Date

---------------------------

06/20/2003 16:55:14

06/26/2003 11:16:36



The trouble with most of the people I've met is to calculate the number of years, months, days, hours, and seconds between two time intervals. What you need to know is that when you subtract two dates, you get the number of days. You need to multiply the number of seconds per day (1 days = 86,400 seconds), and then you can calculate the number of intervals you want. The following is my solution, which can be used to accurately calculate the interval between two times. I know this example can be a little bit shorter, but I'm trying to show all the numbers to emphasize the way I calculate.

1 SELECT to_char (date1, ' MMDDYYYY:HH24:MI:SS ') date1,

2 To_char (date2, ' MMDDYYYY:HH24:MI:SS ') Date2,

3 Trunc (86400* (date2-date1))-

4 60* (Trunc (86400* (date2-date1))/60) seconds,

5 Trunc ((86400* (date2-date1))/60)-

6 60* (Trunc (86400* (date2-date1)/60)/60)) minutes,

7 Trunc ((86400* (date2-date1))/60/60)-

8 24* (Trunc ((86400* (date2-date1)/60)/60)/24) hours,

9 Trunc ((86400* (date2-date1)/60)/60) days,

Trunc (((86400* (date2-date1)/60)/60)/24)/7 weeks

11* from Date_table

DATE1 DATE2 SECONDS MINUTES HOURS days WEEKS

----------------- ----------------- ---------- ---------- ---------- ---------- ----------

06202003:16:55:14 07082003:11:22:57 43 27 18 17 2

06262003:11:16:36 07082003:11:22:57 21 6 0 12 1



TIMESTAMP data type

The main problem with the date data type is that it doesn't have enough granularity to distinguish between two events which first occurs. Oracle has extended the timestamp data type on the date data type, which includes information about the seconds and seconds of all date data types, and includes the decimal second information. If you want to convert the date type to the timestamp type, use the cast function.

Sql> Select CAST (date1 as TIMESTAMP) "Date" from T;

Date

-----------------------------------------------------

20-jun-03 04.55.14.000000 PM

26-jun-03 11.16.36.000000 AM



As you can see, there is a ". 000000" at the end of the converted time period. This is because when you convert from date, there is no information for decimal seconds, and the default is 0. and the display format is displayed in the default format set by the parameter Nls_timestamp_format. When you move data from a Date Type field in a table to a timestamp type field in another table, you can write the Insert SELECT statement directly, and Oracle will automatically convert it for you.

1 Select To_char (time1, ' mm/dd/yyyy HH24:MI:SS ') "Date" from date_table

Date

-------------------

06/20/2003 16:55:14

06/26/2003 11:16:36

The format of the timestamp data is the same as the date data. Note that the TO_CHAR function supports date and timestamp, but Trunc does not support timestamp data types. This has made it clear that using the timestamp data type is more accurate than the date data type when the difference between two times is extremely important.
　　
If you want to display timestamp decimal seconds information, refer to the following:
　　
1 Select To_char (time1, ' mm/dd/yyyy HH24:MI:SS:FF3 ') "Date" from date_table
　　
Date
　　
-----------------------
　　
06/20/2003 16:55:14:000
　　
06/26/2003 11:16:36:000
　　
In the example above, I'm only realistic about 3 bits after the decimal point.
　　
It is easier to calculate the data differences between timestamp than the old date data type. When you subtract directly, see what happens. The results will be easier to understand, the first line of 17 days, 18 hours, 27 minutes and 43 seconds.
　　
1 SELECT time1,
　　
2 Time2,
　　
3 substr ((time2-time1), InStr ((time2-time1), "+7,2") seconds,
　　
4 substr ((time2-time1), InStr ((time2-time1), "+4,2") minutes,
　　
5 substr ((time2-time1), InStr ((time2-time1), "+1,2") hours,
　　
6 trunc (To_number (substr (time2-time1), 1,instr (Time2-time1, ')))
　　
7 trunc (To_number (substr (time2-time1), 1,instr (Time2-time1, '))/7) weeks
　　
8* from Date_table
　　
TIME1 TIME2 SECONDS MINUTES HOURS days WEEKS
　　
-------------------------　 -------------------------- ------- ------- ----- ---- -----
　　
06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43 27 18 17 2
　　
06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21 06 00 12 1
　　
This means no longer needing to care about how many seconds a day is in trouble calculations. Therefore, getting the number of days, months, days, hours, minutes and seconds becomes a matter of extracting numbers with the SUBSTR function.
　　
System date and Time
　　
Returns the date data type in order to get the system time. You can use the Sysdate function.
　　
Sql> SELECT sysdate from DUAL;
　　
To get the system time, return to the timestamp data type. You can use the Systimpstamp function.
　　
Sql> SELECT Systimestamp from DUAL;
　　
You can set initialization parameters fixed_date Specify the Sysdate function to return a fixed value. This is used in the test date and time sensitive code. Note that this parameter is not valid for the Systimestamp function.
　　
sql> ALTER SYSTEM SET fixed_date = ' 2003-01-01-10:00:00 ';
　　
System altered.
　　
Sql> select Sysdate from dual;
　　
Sysdate
　　
---------
　　
01-jan-03
　　
Sql> select Systimestamp from dual;
　　
Systimestamp
　　
---------------------------------------------------------
　　
09-jul-03 11.05.02.519000 am-06:00
　　
When using date and timestamp types, the choice is clear. You are free to dispose of date and timestamp types. When you try to switch to a more powerful timestamp, you need to be aware that they have a similar place, and there are different places, and it's enough to cause damage. Both in terms of simplicity and spacing size each have advantages, please choose reasonably.

Reference:

Oracle Timestamp length Baidu