CODEWITHSUNDEEP
sqloracle-databasesqlplus
RotDot20
RotDot20

Reputation: 51

Date time format in Oracle

I am new to sql and am working on an example. Among the tables I have created, I have the comments table:

CREATE TABLE comments (
club       VARCHAR2(60) NOT NULL,
nick         VARCHAR2(35),
msg_date   DATE,
title        VARCHAR2(100) NOT NULL,
director   VARCHAR2(50) NOT NULL,
subject    VARCHAR2(100),
message    VARCHAR2(1500),
valoration NUMBER(2),
CONSTRAINT PK_COMMENTS PRIMARY KEY (nick,msg_date),
CONSTRAINT FK_COMMENTS_MEMBER FOREIGN KEY (nick,club) REFERENCES membership ON DELETE CASCADE,
CONSTRAINT FK_COMMENTS_MOVIES  FOREIGN KEY (title,director) REFERENCES movies,
CONSTRAINT CK_COMMENTS_VAL CHECK (valoration<11) 
);

I am asked to create a trigger that does the following: if a comment arrives on the same date as another one already stored, register it with the date 'one second later'. The problem I have is that I do not know how to convert the 'one second' later into a date. Any idea on how to solve this problem?

Upvotes: 0

Views: 62

Answers (1)

Chris
Chris

Reputation: 1804

msg_date + interval '1' second

or alternatively msg_date + (1/(24*60*60))

However this whole scenario is fraught with danger. While checking existing messages in the table within the trigger the table may be changing in other transactions and so there is a real risk of race conditions here - two messages both adding 1 second to an existing message will then have the same date. This would be the case whether the check was in a trigger or application code.

If this is a real world scenario I would avoid the trigger, use a timestamp rather than date, where the precision is between millis and nanos and consider how to deal with the lower risk of messages with the same timestamp as a business problem - what is the implication if it does occur.

Upvotes: 1

Related Questions