How to convert an Oracle string to a TIMESTAMP type

When converting Oracle strings to TIMESTAMP or Date types in practical applications of computers, we need to use Oracle-related functions. In this process, we need to involve a lot of string applications, next let's take a look at the practical application of Oracle String Conversion!

Use Oracle functions:

TO_TIMESTAMP_TZ ('2017-3-9 17:51:23. 23-05:00 ', 'yyyy-MM-D HH24: MI: SS. ff tzh: tzm ')

TO_DATE (yourdate, 'yyyy-MM-D HH24: MI: ss ');

String to Date

T0_CHAR (yourdate, 'yyyy-MM-D HH24: MI: ss ');

Convert Date to string

Eg:

Update t_fl_flownote set sendtime = TO_TIMESTAMP_TZ ('2017-11-30 10:47:16 ', 'yyyy-MM-DD HH24: MI: ss ')

Transfer others' logs

We all know that date and timestamp are both expressions of date and time, but the two types of precision are different. The former is accurate to seconds, and the latter is precise to fractional_seconds_precision in decimal seconds ), it can be 0 to 9. The default value is 6.

Oracle String Conversion is simple for date type calculation, and many functions can be used for processing; the difference between the two timestamp is that it intuitively shows the number of days, hours, minutes, seconds, and decimal seconds,

SQL> create table test (T1 TIMESTAMP (6 ),

2 T2 TIMESTAMP (6 ));

The table has been created.

SQL> insert into test values (

2 to_timestamp ('2017-01-01 12:10:10. 1', 'yyyy-mm-dd hh24: mi: ss. ff '),

3 to_timestamp ('2017-01-01 12:20:10. 2', 'yyyy-mm-dd hh24: mi: ss. ff '));

One row has been created.

SQL>

SQL> insert into test values (

2 to_timestamp ('2017-01-01 12:10:10. 1', 'yyyy-mm-dd hh24: mi: ss. ff '),

3 to_timestamp ('2017-01-02 12:20:10. 2', 'yyyy-mm-dd hh24: mi: ss. ff '));

One row has been created.

SQL>

SQL> insert into test values (

2 to_timestamp ('2017-01-01 12:10:10. 1', 'yyyy-mm-dd hh24: mi: ss. ff '),

3 to_timestamp ('2017-01-02 13:40:20. 2', 'yyyy-mm-dd hh24: mi: ss. ff '));

One row has been created.

SQL> commit;

Submitted.

SQL>

The difference between the two timestamp is that it intuitively shows the number of days, hours, minutes, seconds, and decimal seconds:

SQL> select t2-t1 from test;

+ 000000000 00:10:00. 100000

+ 000000001 00:10:00. 100000

+ 000000001 01:30:10. 100000

SQL>

However, it is difficult to simply convert the data into a specific precision. It is not feasible to use a method similar to the date type. For example, to score:

SQL> select 1440 * (t2-t1) from test;

+ 000000010 00:02:24. 000000000

+ 000001450 00:02:24. 000000000

+ 000001530 04:02:24. 000000000

SQL>

The results are not originally expected, but multiplied by 1440 for each item of the original "how many days + how many hours + how many minutes + how many seconds + how many decimal seconds" and then processed in the traveling system. The above content is an introduction to Oracle String Conversion of the TIMESTAMP type or Date type. I hope you will get something.