anoopprasad · January 28, 2021 21:15 · Apr 16, 2017
diff --git a/find_zero_dates.rb b/find_zero_dates.rb
@@ -0,0 +1,34 @@
+#
+# Check for every table with temporal columns if there are records that have dates set to '0000-00-00'
+
+conn = ActiveRecord::Base.connection
+
+# get a list of tables and columns with a date type excluding merge tables
+query = "select concat(`table_schema`,'.',`table_name`) as table_name,`COLUMN_NAME` from information_schema.`columns` where data_type like '%date%' AND concat(`table_schema`,'.',`table_name`) not in (select concat(`table_schema`,'.',`table_name`) from information_schema.tables where engine='mrg_myisam');"
+list = conn.select_all( query )
+
+# transform the list a bit for later querying
+tables = {}
+list.each do |r|
+  if tables.include?(r["table_name"])
+    # table is already in the array, just push the column name
+    tables[r["table_name"]].push(r["COLUMN_NAME"])
+  else
+    # table is not in the hash yet, add it with its column
+    tables[r["table_name"]] = [r["COLUMN_NAME"]]
+  end
+end
+
+# query for the zero dates
+tables.each do |tn, columns|
+  c = columns.map { |cn| "#{cn} < '1900-01-01'" }.join(" OR ")
+  q = "select count(*) as c from #{tn} where #{c};"
+
+  # run query
+  puts q
+  count = conn.select_value(q)
+  puts count
+  if count > 0
+    puts "#{tn} has #{count} zero date records"
+  end
+end