CODEWITHSUNDEEP
mysqlsqldatetime
Terra Kestrel
Terra Kestrel

Reputation: 20334

Shift all dates in MySQL database by specific delta value

Is there a way in MySQL to shift all date/datetime columns in a database by a calculated delta value?

I want to take the max date from a static table and column (table and column always the same) and subtract that from the curdate() value to get the number of days behind now that said max is. Then I want to shift all date/datetime values in the database by that number, effectively bringing all records in the database to the current time.

I know that I can use the information_schema.columns attribute to do pull all columns of date/datetime, but I'm unsure how to build this into a working "and sane" query to do the update.

I was thinking of something like:

SELECT concat('UPDATE ', table_name, ' set ', column_name, ' = date_add(', column_name, ', INTERVAL datediff(curdate(), max(given_table.given_column)) day);')
from information_schema.columns
where data_type in ('date', 'datetime') and table_schema = 'mydatabase';`

But there are multiple problems with this, obviously, so I know I can't use this.

Is there a way to calculate a delta between X database column and now, and then shift all date/datetime columns in the database by that value?

Upvotes: 1

Views: 1364

Answers (1)

Milimetric
Milimetric

Reputation: 13549

I don't think there's any magic bullet here, maybe just cleaning up your query a bit. I tried the following and it seemed to work fine for my databases:

 select @diff_in_days := datediff(curdate(), max(given_column))
   from given_table
;

 select concat(
            'update ', table_name,
              ' set ', column_name, ' = date_add(',
                            column_name,
                            ', interval ', @diff_in_days, ' day',
                       ');'
        )
   from information_schema.columns
  where data_type in ('date', 'datetime') and table_schema = 'mydatabase'
;

And to execute it, one way is to put the above in generate.sql, then do:

mysql < generate.sql > update.sql
mysql < update.sql

Of course with the proper parameters for your server, database, user, etc.

Upvotes: 2

Related Questions