CODEWITHSUNDEEP
mysql
dai
dai

Reputation: 131

INSERT IGNORE for date while ignoring time

I'm inserting data that are unique for all three columns using INSERT IGNORE. How can I exclude the time and check the date if its unique with the other two columns below?

Unique index for columns: timestamp, action, value

Time stamp format: 2016-04-21 13:33:47

My table sample:

2016-04-21 13:33:47, send, email

2016-04-20 11:21:32, send, email

2016-04-19 17:32:65, send, email

What I'm trying to do: If I try to insert the data below, it should be ignored because there's existing data for 2016-04-21, send, email.

2016-04-21 19:33:47, send, email

Upvotes: 2

Views: 1259

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31812

You can use an INSERT SELECT statement with constant data from a dummy table. This way you can include a WHERE clause.

insert into actions (`timestamp`, action, value)
    select '2016-04-21 19:33:47', 'send', 'email'
    from (select 1) dummy
    where not exists (
        select 1
        from actions
        where action = 'send'
          and value  = 'email'
          and date(`timestamp`) = '2016-04-21'
    );

http://sqlfiddle.com/#!9/5882c/1

The data will only be inserted if the condition in the subquery does not have any matches.

Upvotes: 1

Matt Raines
Matt Raines

Reputation: 4218

If you are using MySQL 5.7.6 or higher, you can create a generated column with a unique index.

ALTER TABLE table_name
ADD COLUMN timestamp_date DATE GENERATED ALWAYS AS (DATE(timestamp)) STORED,
ADD UNIQUE (timestamp_date, action, value);

With an earlier version of MySQL, you'll need to add a column to store the date and use a trigger (available from version 5.0.2) to update it whenever you insert new records in the table.

ALTER TABLE table_name
ADD COLUMN timestamp_date DATE;

CREATE TRIGGER insert_table_name_date BEFORE INSERT ON table_name
FOR EACH ROW SET NEW.timestamp_date = DATE(NEW.timestamp);

-- If you already have any data in the table, update it to add the date.
UPDATE table_name SET timestamp_date = DATE(timestamp_date);

ALTER TABLE table_name
ADD UNIQUE (timestamp_date, action, value);

INSERT INTO table_name(timestamp, action, value) VALUES
('2016-04-21 13:33:47', 'send', 'email'),
('2016-04-20 11:21:32', 'send', 'email'),
('2016-04-19 17:32:55', 'send', 'email');

-- 3 row(s) affected

INSERT INTO table_name(timestamp, action, value) VALUES
('2016-04-21 19:33:47', 'send', 'email');

-- Error Code: 1062. Duplicate entry '2016-04-21-send-email' for key 'timestamp_date'

Upvotes: 1

tadman
tadman

Reputation: 211610

The only way an IGNORE will work is if you have one or more columns in a UNIQUE index. In this case you need a DATE field that's derived from your DATETIME.

For example:

ALTER TABLE my_emails ADD COLUMN sent_date DATE
CREATE UNIQUE INDEX idx_sent_date ON my_emails (sent_date)

Then you can populate it:

UPDATE my_emails SET sent_date=DATE(sent_datetime)

With whatever those columns are. From that point forward you'll need to be sure you populate both fields.

Upvotes: 1

Related Questions