Skip to content

Instantly share code, notes, and snippets.

@6aditya8

6aditya8/adding_default_values_for_date_fields.sql

Last active April 1, 2020 14:05
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save 6aditya8/b64ce6bcc69db7db439f12414ad4450d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save 6aditya8/b64ce6bcc69db7db439f12414ad4450d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. 6aditya8 revised this gist Apr 1, 2020. 1 changed file with 3 additions and 1 deletion.
    4 changes: 3 additions & 1 deletion adding_default_values_for_date_fields.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -16,7 +16,9 @@ WHERE
    1;

    -- Create a trigger which ensures that when new rows are added, the default value is NOW().
    CREATE TRIGGER table_name__before_insert BEFORE INSERT ON table_name FOR EACH ROW
    CREATE TRIGGER table_name__before_insert BEFORE
    INSERT
    ON table_name FOR EACH ROW
    SET
    NEW.created_at = NOW(),
    NEW.modified_at = NOW();
  2. 6aditya8 revised this gist Apr 1, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion adding_default_values_for_date_fields.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,7 @@
    ALTER TABLE
    table_name
    ADD
    COLUMN created_at DATETIME NULL;
    COLUMN created_at DATETIME NULL
    ADD
    COLUMN modified_at DATETIME NULL;

  3. 6aditya8 created this gist Mar 31, 2020.
    30 changes: 30 additions & 0 deletions adding_default_values_for_date_fields.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,30 @@
    -- Add the columns created_at & modified_at to your required table.
    ALTER TABLE
    table_name
    ADD
    COLUMN created_at DATETIME NULL;
    ADD
    COLUMN modified_at DATETIME NULL;

    -- Initialize the data with the current timestamp.
    UPDATE
    TABLE table_name
    SET
    created_at = NOW(),
    modified_at = NOW()
    WHERE
    1;

    -- Create a trigger which ensures that when new rows are added, the default value is NOW().
    CREATE TRIGGER table_name__before_insert BEFORE INSERT ON table_name FOR EACH ROW
    SET
    NEW.created_at = NOW(),
    NEW.modified_at = NOW();

    -- Create a similar trigger for updating rows.
    CREATE TRIGGER table_name__before_update BEFORE
    UPDATE
    ON table_name FOR EACH ROW
    SET
    NEW.created_at = OLD.created_at,
    NEW.modified_at = NOW();