Add Timestamps to an Existing Table

Add timestamps to an existing table

The timestamp helper is only available in the create_table block. You can add these columns by specifying the column types manually:

class AddTimestampsToUser < ActiveRecord::Migration
  def change_table
    add_column :users, :created_at, :datetime, null: false
    add_column :users, :updated_at, :datetime, null: false
  end
end

While this does not have the same terse syntax as the add_timestamps method you have specified above, Rails will still treat these columns as timestamp columns, and update the values normally.

Add timestamps to existing table in db Rails 5+

You cannot add columns with not-null constraint to a non-empty table because the existing lines in the table would have empty values right away and therefore the condition fails.

Instead, introduce the columns in three steps:

def change
  # add new column but allow null values
  add_timestamps :products, null: true 

  # backfill existing records with created_at and updated_at
  # values that make clear that the records are faked
  long_ago = DateTime.new(2000, 1, 1)
  Product.update_all(created_at: long_ago, updated_at: long_ago)

  # change to not null constraints
  change_column_null :products, :created_at, false
  change_column_null :products, :updated_at, false
end

Add timestamp to existing table

SQL Server will not track historically when a row was inserted or modified so you need to rely on the JSON data to figure that out yourself. You are going to need a new column to make this efficient to query. Once you have your new column you have some options:

1. Loop through all your records populating the new column with the relevant value from the JSON data.

2. If your version of SQL Server is recent enough, you can query the JSON data directly. Populate this column using a query like this:

   UPDATE MyTable
   SET MyNewColumn = JSON_VALUE(JsonDataColumn, '$.Customer.DateCreated')
   

   The downside of this method is that you need to maintain this

3. Make SQL Server compute the value from the JSON automatically, for example:

   ALTER TABLE MyTable
       ADD MyNewColumn AS JSON_VALUE(JsonDataColumn, '$.Customer.DateCreated')
   

   And, create an index to make it efficient:

   CREATE INDEX IX_MyTable_MyNewColumn
   ON MyTable(MyNewColumn)  
   

How to add timestamps to an existing table with Ecto's timestamps?

The timestamps/1 function accepts an options keyword list, you can set the default value with it.

def change do
  alter table(:channels) do
    timestamps default: "2016-01-01 00:00:01", null: false
  end
end

UPDATE Ecto >= 2.1

You'll need to use the new type NaiveDateTime

def change do
  alter table(:channels) do
    timestamps default: ~N[2017-01-01 00:00:01], null: false
  end
end

If you have more doubts take a look at the documentation

How to add timestamp to the existing mysql table

Try this (UPDATE query). You can use a function, to get same NOW() for both columns created_at and updated_at.

http://sqlfiddle.com/#!2/c204e9/1

UPDATE table_name SET created_at = NOW(), updated_at=NOW() WHERE created_at IS NULL AND updated_at IS NULL

This only works if both columns are null.

How to add a time to an existing timestamp in Postgresql/sql?

Add 9 hours:

update members
set joined = joined + interval '9' hour

---

Related Topics