Original author: James Koopmann
The format display of TimeStamp data is the same as Date data. Note that the To_Char function supports Date and TimeStamp, but Trunc does not support TimeStamp data types. This has clearly shown that when the difference is extremely important in two times, the TIMESTAMP data type is used more exactly more than the DATE data type.
If you want to display the decimal second information of TimeStamp, 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 above example, I only realized the content of 3 digits after the decimal point.
Calculating data differences between TimeStAMP is easier than the old Date data type. When you are directly subtracted, see what will happen. The results will be easier to understand, 17 days, 18 hours, 27 minutes, and 43 seconds of the first line.
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 that it is no longer necessary to care about how many seconds in trouble a day. Therefore, get the number of days, months, days, hours, minutes, and seconds, have become a number of things to extract numbers with substr functions.
System date and time
In order to get the system time, return to the DATE data type. You can use the SYSDATE function.
SQL> SELECT SYSDATE from DUAL;
In order to get the system time, return to the TIMESTAMP data type. You can use the SystempStamp function.
SQL> SELECT SYSTIMESTAMP from DUAL
You can set the initialization parameter fixed_date specifies that the sysdate function returns a fixed value. This is used in test dates and time-sensitive code. Note that this parameter is invalid 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 type, the choice is clear. You can dispose of the Date and TimeStamp type at will. When you try to convert to a more powerful TimeStamp, you need to pay attention to both similar places, more different, and enough to cause damage. Both have an advantage in the concise and interval size, please choose reasonably.
