Java saves the yyyy-mm-dd hh: mm: SS date string to the Oracle Date Field [original]

+ "','" + Mytechnical + "', to_date ('" + mybirthday + "', 'yyyy-MM-DD'), '" + myemail + "', '"+ mytelephone

To_date ('','') does not need to be added to an SQL statement, but the preceding format must be used to obtain the variable.

1. 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 ')

2. 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 accurate to decimal seconds (Fractional_seconds_precision), which can be0 to 9. The default value is 6.

However, the date operation is very simple, 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 easiest way to understand is to use substr to split and convert the difference between two timestamps:

SQL> select substr (t2-t1), instr (t2-t1), '') +) seconds,
2 substr (t2-t1), instr (t2-t1), '') +) minutes,
3 substr (t2-t1), instr (t2-t1), '') +) hours,
4 trunc (to_number (substr (t2-t1), 1, instr (t2-t1, '') days,
5 trunc (to_number (substr (t2-t1), 1, instr (t2-t1, '')/7) weeks
6 from test;

Seco Minu hour days weeks
--------------------------------
00 10 00 0 0
00 10 00 1 0
10 30 01 1 0

Or use a custom function to convert the number of days to the "day, minute, and second" format:

Create or replace function f_days2str (p_days in number default 0)
Return varchar2 is
-- Ver: 1.0
-- Created by xsb on 2005-05-26
-- For: Convert days to days, hours, and minutes
Daysnumber: = nvl (p_days, 0 );
Vdnumber; -- day
Vhnumber; -- hour
Vmnumber; -- minute
Vsnumber; -- seconds
Result varchar2 (100); -- Return Value
Begin
VD: = trunc (days );
Vl: = trunc (days-VD) * 24 );
VM: = trunc (days-VD-vl/24) * 24*60 );
VS: = trunc (days-VD-vl/24-VM/24/60 )*
24*60*60 );
Select decode (VD, 0, '', VD | 'day') | decode (VH,
0, '', VL | 'hourly ') | decode (Vm, 0,
'', Vm | 'mine') | decode (Vs, 0,
'', Vs | 'second') into result from dual;
Return (result );
End;

SQL>

If the accuracy requirement of the final result is not high (when the score is greater than or equal to the score), you can convert the timestamp to date before settlement. This is much simpler:

SQL> select (to_date (to_char (t2, 'yyyy-mm-dd hh24: MI: ss'), 'yyyy-mm-dd hh24: MI: ss'
)-To_date (to_char (T1, 'yyyy-mm-dd hh24: MI: ss'), 'yyyy-mm-dd hh24: MI: ss') * 24*60
2 from test;

10

1450

1530.16667

Convert date to Timestamp