1

I use this code to find the create time of a table:

SELECT CREATE_TIME
    FROM   information_schema.tables
    WHERE  TABLE_SCHEMA = 'powersy3_sample'
    AND TABLE_NAME = 'empdet'

But, I need to find the time of particular row created in the empdet table.

I tried to fetch the column names by using this query:

select column_name from information_schema.columns where table_name='empdet'

I tried a different query to fetch the create time and updated time of the particular row but I cannot find a query that works.

How can I achieve this?

Toastrackenigma
  • 7,604
  • 4
  • 45
  • 55
selva surya
  • 103
  • 9
  • 2
    It would be more common to have a column on the row which is defaulted to current time when the row is inserted, don't think the created time of a row is otherwise accessible. – Nigel Ren Feb 22 '18 at 09:14
  • i want the row created time because,using that i can able to detect the changes/modification made to that row,so only i thought of retriving the the time and storing it in another table,by comparing the time i can detect the change!is there is a way to detect the changes made to particular row in a table @ solarflare – selva surya Feb 22 '18 at 09:23
  • Ah I misread your question. As Nigel said: add a column with "on update current_timestamp" (and/or "default current_timestamp"), see [Automatic Initialization and Updating for TIMESTAMP and DATETIME](https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html). You cannot get its value in retrospect for an existing row (apart from checking backup/audit/logfiles/...). – Solarflare Feb 22 '18 at 09:55
  • ah , thankyou! i got an idea how to do it with Timestamp!.@ Solarflare – selva surya Feb 22 '18 at 09:59
  • Also: For more advanced logging (if that is what you are after), you can e.g. use triggers and store changes to the rows, see e.g. [Using MySQL triggers to log all table changes to a secondary table](https://stackoverflow.com/q/779230/6248528) – Solarflare Feb 22 '18 at 10:01

0 Answers0