0

I have timestamps looking like this: 2019-06-13 13:22:30.521000000

I am using Spark/Scala scripts to insert them into an Oracle table. Column in Oracle is Timestamp(6) and should stay like that.

This is what I do:

what I have in Spark is a df containing a column with my timestamps: 

+-----------------------------+
|   time                      |
+-----------------------------+
|2019-06-13 13:22:30.521000000| 
+-----------------------------+

I do the following: 

df.withColumn("time", (unix_timestamp(substring(col("time"), 1, 23), "yyyy-MM-dd HH:mm:ss.SSS") + substring(col("time"), -6, 6).cast("float") / 1000000).cast(TimestampType))

and I insert using a connexion to Oracle (insert script was tested and works fine). But in Oracle I only see the following in my table: 

+--------------------------+
|   time                   |
+--------------------------+
|2019-06-13 13:22:30.000000| 
+--------------------------+

The milliseconds aren't included. Any help please? Thank you!

Haha
  • 973
  • 16
  • 43

3 Answers3

0

If your time column is a timestamp type, you can try date_format:

https://sparkbyexamples.com/spark/spark-sql-how-to-convert-date-to-string-format/

Cesar A. Mostacero
  • 720
  • 6
  • 12
0

I thank everyone that tried to help me.

This is what I did to get desired output: 

df.withColumn("time", (unix_timestamp(substring(col("time"), 1, 23), "yyyy-MM-dd HH:mm:ss.SSS") + substring(col("time"), -9, 9).cast("float") / 1000000000).cast(TimestampType))

all other solutions kept returning null or timestamps without milliseconds.

Hope it helps someone.

Haha
  • 973
  • 16
  • 43
-1

I don't know tools you use, but - if it were only Oracle, then to_timestamp with appropriate format mask does the job. See if it helps.

SQL> create table test (col timestamp(6));

Table created.

SQL> insert into test (col) values
  2    (to_timestamp('2019-06-13 13:22:30.521000000', 'yyyy-mm-dd hh24:mi:ss.ff'));

1 row created.

SQL> select * From test;

COL
---------------------------------------------------------------------------
13.06.19 13:22:30,521000

SQL>

[EDIT, as you can't read my mind (at least, I hope so]

As you (AbderrahmenM) said that you have a string but still want to insert a timestamp, perhaps you could use a stored procedure. Here's an example:

SQL> create or replace procedure p_test (par_time in varchar2)
  2  is
  3  begin
  4    insert into test (col) values
  5      (to_timestamp(par_time, 'yyyy-mm-dd hh24:mi:ss.ff'));
  6  end;
  7  /

Procedure created.

SQL> exec p_test('2019-06-13 13:22:30.521000000');

PL/SQL procedure successfully completed.

SQL> select * from test;

COL
-------------------------------------------------------------------
13.06.19 13:22:30,521000

SQL>

Now, the only thing I can't help with is how to call a procedure from Spark. If you know how, then simply pass that string you have and it should be properly inserted into the database; pay attention to correct format mask!

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • The problem that @AbderrahmenM is facing is on `Spark` side. Oracle is only the target db. – Cesar A. Mostacero Nov 25 '19 at 20:49
  • @Cesar, it is *a problem*. As usual, it can be fixed using more than one option. You said that it is on Spark side. I don't know Spark, but - let me try to help the way I know, and that's Oracle side. From my point of view, it doesn't really matter how it is "solved", as long as it works. I'd like to hear what AbderrahmenM says about it (especially a stored procedure suggestion). – Littlefoot Nov 26 '19 at 06:17
  • Thank you for your clear answer. But I am not allowed to touch Oracle. Tables are there and I need to insert rows into them using Spark. Thank you again – Haha Nov 26 '19 at 13:23
  • 1
    You're welcome. Sorry, I can't help you with Spark. Hopefully, someone else will. P.S. Ah! I see you posted an answer; I'm glad if you made it work! – Littlefoot Nov 26 '19 at 13:39