0

I have a problem in Oracle, with a virtual column (I need the timestamp starting from a date column); here my example:

CREATE TABLE TBDATETIME(
  DATETIME_1 DATE,
  DATETIME_2 TIMESTAMP(9) GENERATED ALWAYS AS (
    CAST( TO_CHAR(DATETIME_1, 'DD/MM/YYYY HH24:MI:SS')
      || '.' || TO_CHAR(DATETIME_1, 'FF9')  AS TIMESTAMP(9))
   ) VIRTUAL
 );

INSERT INTO TBDATETIME(DATETIME_1)
VALUES(SYSDATE);

COMMIT;

SELECT *
FROM TBDATETIME;

ORA-01821: date format not recognized
01821. 00000 -  "date format not recognized"
*Cause:    
*Action:

Where is the problem fetching data? I tried a lot of format masks, but nothing helps...

MT0
  • 143,790
  • 11
  • 59
  • 117
user1
  • 556
  • 2
  • 6
  • 22
  • Why do you cast a date to a char and then back to timestamp? – Wernfried Domscheit Oct 23 '14 at 11:16
  • I would like generate milliseconds from systimestamp and then "connect" them to the date in the virtual column... I know it's a bad solutions, but I can't alter the column type and also I can't add a timestamp column to the table. – user1 Oct 23 '14 at 13:46
  • 1
    You cannot use NONE DETERMINISTIC functions like SYSTIMESTAMP in the virtual column definition (functions that may return different values depending on env, time etc - different calls with the same params give different results) – Multisync Oct 23 '14 at 13:59
  • Do you really just want a normal column which defaults to `systimestamp`? If you're always setting `datetime_1` to `sysdate` that would have the same effect; if you set it to some other date then you'd have to set the fraction seconds to zero anyway as anything else would be meaningless. What problem are you actually trying to solve? – Alex Poole Oct 23 '14 at 14:09
  • If I perform an insert like this INSERT INTO TBDATETIME(DATETIME_1) VALUES(SYSDATE); I would like to have these result: SELECT * FROM TBDATETIME; DATETIME_1 DATETIME_2 10/23/2014 16:27:14 10/23/2014 16:27:14.1234567 Where DATETIME_2 is the virtual column. In simple term, I want a column with datetime + milliseconds. – user1 Oct 23 '14 at 14:30
  • Again `SYSDATE` does **not** provide any milliseconds! You have to use `SYSTIMESTAMP` and insert this into a `TIMESTAMP` column. There is no other way. – Wernfried Domscheit Oct 23 '14 at 14:32
  • That doesn't really explain why, and doesn't say if it'll always be `sysdate`. You said you can't add a timestamp column - you mean you're allowed to add a virtual column but not a real one? If you add a real column you could default it if it's always `sysdate`, or use a trigger to set it otherwise. You can't add the current time's fractional seconds with a virtual column though. – Alex Poole Oct 23 '14 at 14:32
  • I understood SYSDATE doesn't have milliseconds! I repeat, my idea was to add milliseconds to a date and a time: So: - cast to string the datetime value - add the milliseconds from a SYSTIMESTAMP after casted the value to a string - cast the whole string to a TIMESTAMP and insert this value in a virtual column. This for future inserts on the table; the currents rows in the table are not affected by this idea (milliseconds remain 000000). If this is impossible, I'll find another way. Thanks to everybody! Igor – user1 Oct 23 '14 at 15:13

1 Answers1

5
DATETIME_1 DATE

TO_CHAR(DATETIME_1, 'FF9')

That's the problem - DATE doesn't have fraction seconds

And it's dangerous to cast char to timestamp (the result depends on nls settings)

I suppose you want this:

CREATE TABLE TBDATETIME(DATETIME_1 DATE, DATETIME_2 TIMESTAMP(9) GENERATED ALWAYS AS (CAST(DATETIME_1 AS TIMESTAMP(9))) VIRTUAL);
Multisync
  • 8,657
  • 1
  • 16
  • 20
  • Thank you very much Multisync! Yes, your solutions goes well, but there is a problem: casting datetime_2 to timestamp will generate always 000000000 as milliseconds. Have you got a solution to this problem? Thanks in advance. Igor – user1 Oct 23 '14 at 12:17
  • 2
    @Igor You want to cast DATE to TIMESTAMP. DATE doesn't have milliseconds. So it's impossible to get milliseconds in this case – Multisync Oct 23 '14 at 12:23
  • Thanks again Multisync; you are right: it's impossible to retrieve milliseconds from date type; but the question is: can I, for example, do somethings like this: CREATE TABLE TBDATETIME( DATETIME_1 DATE, DATETIME_2 TIMESTAMP(9) GENERATED ALWAYS AS ( CAST( TO_CHAR(DATETIME_1, 'DD/MM/YYYY HH24:MI:SS') || '.' || TO_CHAR(SYSTIMESTAMP, 'FF9') AS TIMESTAMP(9)) ) VIRTUAL ); but using TO_CHAR(SYSTIMESTAMP, 'FF9') fires the error: ORA-54002 only pure functions can be specified in a virtual column expression Bye, Igor – user1 Oct 23 '14 at 13:49
  • @Igor I answered in main topic. What do you want to achieve? – Multisync Oct 23 '14 at 14:03