Automatically insert timestamp if there isn't any in the table

Question

I have a table where I have a long list of timestamps and there could be chances of timestamps missing for an entire day or days and I want to insert a single timestamp for that particular day

For example if there is no timestamp for 2014-04-04 I want it to insert 2014-04-04 00:00:00 for that particular day.

Can this be done in mysql ?

wallyk · Accepted Answer · 2014-08-09 20:32:43Z

1

Yes it can.

Add a trigger for after insert to the table. Make the trigger insert the current timestamp if none has been provided.

Or change the column to have a default value of CURRENT_TIMESTAMP.

Those don't resolve the problem of previous entries having no values, but it does fix the problem for the future. Filling in the rest should be a straightforward update: update mytable set its_timestamp = date(datefield) where its_timestamp is NULL.

answered Aug 9, 2014 at 20:32

wallyk

58.1k17 gold badges92 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bazinga777 Over a year ago

I want it to add a timetstamp for the missing date not the current timestamp

Collectives™ on Stack Overflow

Automatically insert timestamp if there isn't any in the table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related