CODEWITHSUNDEEP
sqloracle-databaseconstraintsddl
user3268401
user3268401

Reputation: 329

Checking timestamps in a constraint in Oracle

I have created a simple table:

CREATE TABLE Messages
(   msgID   number(10) PRIMARY KEY,
    sender_ID   number(10),
    time_sent   TIMESTAMP,
);

Now I want to add a constraint to it that ensures that time sent will be after the year 2014. I wrote:

alter table Messages
add constraint year_check 
check (time_sent > to_timestamp('2014-12-31 23:59:59'));

However I get the following error:

ORA-30075: TIME/TIMESTAMP WITH TIME ZONE literal must be specified in CHECK constraint

I don't want to have a TIME ZONE in my timestamp and have inserted values like this:

INSERT INTO Messages VALUES(1, 1, TIMESTAMP '2014-12-24 07:15:57');

How can I fix my constraint to make this error go away?

Upvotes: 0

Views: 2630

Answers (2)

user330315
user330315

Reputation:

When you lookup the error message in the manual you will see the recommendation:

Action: Use time or timestamp with time zone literals only.

to_timestamp('2014-12-31 23:59:59') returns a timestamp (without a time zone), but Oracle requires a timezone with time zone in a check constraint (although I have to admit I don't understand why)

You can either use an ANSI timestamp literal which is resolved to a timestamp with time zone:

alter table Messages
  add constraint year_check 
  check (time_sent > timestamp '2014-12-31 23:59:59');

or use to_timestamp_tz with an explicit time zone:

alter table Messages
  add constraint year_check 
  check (time_sent > to_timestamp_tz('2014-12-31 23:59:59 +00:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM'));

Btw: I would rather change the condition to use >= on the first of January:

alter table Messages
  add constraint year_check 
  check (time_sent >= timestamp '2015-01-01 00:00:00');

Otherwise you could add a row with 2014-12-31 23:59:59.567

Upvotes: 1

RMB
RMB

Reputation: 1

Try using a format mask:

select to_timestamp('2014-12-31 23:59:59') timest from dual
  2  /
select to_timestamp('2014-12-31 23:59:59') timest from dual
                *
ERROR at line 1:
ORA-01843: not a valid month

select to_timestamp('2014-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS') timest from dual
  2  /

TIMEST
---------------------------------------------------------------------------
31-DEC-14 11.59.59.000000000 PM

Upvotes: 0

Related Questions