2

I need to have three columns in all my tables to reflect these as per requirements:

  • Create date/time -> Date and time the row was created. This value should never change forever.
  • Modified date/time -> Date and time only portions of the row was last modified. This value will always change if any detail in the row is changed.
  • Updated date/time -> Date and time the entire row was last updated. This value will always change if all details in the row is changed.

In addition I need to maintain same details but at table level->
- date/time table was created
- date/time the last row was inserted
- date/time last edit took place.

So two questions:

1) For the 3 row cases of date/times: Which one needs to be datetime and which one timestamp?
2) For the 3 table level date/times: Can I store this in the same table somehow or do i need a separate table to store this info, and what functions can i use for this?

End goal is to automate these date/times, because lot of data like for lookup tables will be entered/edited by end users from the front end so I need the DB to do all date/times on its own in the back without any manual insertions for these colunms.

Also, I need to perform reporting on these date/times, hence need to ensure they can be manipulated at the code level for breaking down the date/times into days, weeks, months, years, hours & minutes.

Improve this question

2 Answers 2

Reset to default
1

I'm afraid, that is not possible. It is a really stupid drawback but in the docs it says:

For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Table level timestamp and reporting can by done straightforward using MAX() and GROUP BY.

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

1 Comment

Kudos to the one who proves me wrong. I hate this limitation.
0

If you want to do this entirely with SQL, rather than calculating update timestamps in your application code, you'll have to add a another table. Your main table will have only one timestamp field- the creation date, which will default to CURRENT_TIMESTAMP and never change. Your 2nd table, main_updates, will be something like this:

CREATE TABLE main_updates(
main_id INT UNSIGNED NOT NULL REFERENCES main(main_id),
update_type ENUM('partial','complete'),
update_time TIMESTAMP DEFAULT CURRENT TIMESTAMP)

Then you have a choice. If you want a record of all updates ever done, don't set a primary key and only INSERT into this table. If you want to record only the most recent values, set the primary key to be main_id together with update_type, and set update_time to ON UPDATE CURRENT_TIMESTAMP.

Then you can write a trigger to fire whenever you update main.

But really, it's much easier just to let your application layer set the update times.

Improve this answer

2 Comments

Why cant i have one timestamp for current now and two datetime with Now() - one for full row update and one for partial row update?
@Dia: Well, that would be putting the information into the query (which is sent from the application layer), not making the update time intrinsic to the table, which seemed to be what you were asking. To rephrase my answer "But really, it's much easier just to construct the query to set the update times."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.