Oracle timestamp cannot display detailed data on the client

Use the following statement: O_char (pubtime, ' yyyymmdd HH:mm:ss '), for example:

The code is as follows	Copy Code
Select Id,to_char (pubtime, ' yyyymmdd HH:mm:ss '), content from Bj_ref.gz_twinfo_ref where user_id=2603 and To_char ( Pubtime, ' yyyymmdd HH:mm:ss ') >= ' 20140704 20:58:42 ' and author = ' Nanfang Daily ' ORDER by ID ASC;

Can.

Add: TIMESTAMP Data type

It includes information about the minutes and seconds of all date data types, and includes the decimal second information.

SELECT Sysdate,systimestamp from dual

Note that the TO_CHAR function supports date and timestamp, but Trunc does not support timestamp data types.

SELECT Sysdate,systimestamp,to_char (Systimestamp, ' yyyymmdd hh24:mi:ssxff3 ') from dual

When you subtract directly, see what happens. The results will be much easier to understand,

Select Systimestamp-systimestamp from dual

This means that you don't have to figure out how many days and seconds to get through the trouble calculation, and use the SUBSTR function to pick out the numbers!

Conclusion: When using date and timestamp types, the choice is clear. You are free to dispose of date and timestamp types. You can consider using timestamp when your time granularity must be as accurate as the following seconds.

Let's see how to do this: convert the number of days to the "Day and Minutes" format:

Use Date data type:

The code is as follows

Copy Code

Create Table T2 (D1 date,d2 date); INSERT into T2 VALUES (sysdate, sysdate + 1.1234); SELECT D1, D2, D2-d1, F_days2str (D2-D1),             Cast (D2 as TIMESTAMP)-cast (D1 as TIMESTAMP)     from T2 SELECT to_number (SUBSTR (D21, 1, I Nstr (D21, ')) D,             SUBSTR (D21, INSTR (D21, "") + 1, 2) H,             SUBSTR (D21, INSTR (D21, ') + 4, 2) m,             SUBSTR (D21, INSTR (D21, ' ") + 7, 2) S  & nbsp;  from (SELECT cast (D2 as TIMESTAMP)-CAST (D1 as TIMESTAMP) D21             from T2)