Auto update Last Modification Time of an entry to the vertica table

Unnecessary. Just give the column a DEFAULT.

dbadmin=> create table ts (id int, last_ts datetime default sysdate() ) ;
CREATE TABLE
dbadmin=> insert into ts (id) values(1) ;

OUTPUT

  1

(1 row)
dbadmin=> select * from ts ;
id | last_ts
----+----------------------------
1 | 2019-08-31 17:12:03.733695
(1 row)

Thanks for your response, I'm using the above for the created time.
The above goes untouched in case of modification.
What I'm looking for is when was the given row last modified, request to suggest a solution for the same.

There's no corollary for that in Vertica, probably because Vertica doesn't support triggers, and that's "trigger-like". Also, most people tend to avoid updates, since Vertica isn't really optimized for massive amounts of updates or deletes.

The only real solution is to just always supply the last_updated_ts when you update the table.

Thanks got it, would try adding it while making the column entry itself.

Thanks Sudhakar_B for your response, the prime use case of the last modified date I was looking forward is debug.
Epoch surely seems a good way to get the relative order of the entries made but not too sure how shall that be mapped against time as that become of prime importance during debugging process.
One of the way as Vertica_Curtis suggested we can make entry into the table at the time of updation itself , I'm not sure if there are alternatives to it.

Thanks for clarification Kanishka,
If the use-case is debugging, then you can potentially use to_timestamp to convert which is an APPROXIMATION.
Also, data collector table dc_projection_checkpoint_epochs can potentially be used for converting Checkpoint epochs into timestamp.
Certainly, please test it out and share your findings. It might help other members.

Please note: AFAIK, epoch to Time stamp conversion is only approximation in Vertica

select time, checkpoint_epoch   
from dc_projection_checkpoint_epochs
where time > sysdate -1/48
order by 2 desc;
time                 checkpoint_epoch  
-------------------  ----------------  
2019-09-04 19:07:26  62                
2019-09-04 19:04:45  60                
2019-09-04 19:02:26  60                
2019-09-04 19:02:14  59                
2019-09-04 18:57:26  59                
2019-09-04 18:57:08  53                
2019-09-04 18:47:26  53                
2019-09-04 18:57:08  53                

However, if this use-case ever evolves into audit tracking requirements etc, then please use instrumentation of ETL as recommended by @Vertica_Curtis.

You can get the epochs->time approximation from the EPOCHS table as well, but that table only holds the current active epochs. If you want them to last longer, you'll have to increase the HistoryRetentionInterval. So, dc_projection_checkpoint_epoch might be a better option.

In addition to the above, consider the following two options.
You can avoid DELETEs, UPDATEs and MERGEs.
This will reduce delete vectors to improve performance and manage data versioning for debug.
Instead of doing deletes or update to your data, try to use Live Aggregate Projection (LAP) TOP-K with the last version of the data.
In the base (anchor) table you would just do INSERTs.
The anchor table will always increase its size since we insert new data to “replace” or “hide” the old data.
From time to time (every year or every several months) you can “purge” the anchor table to get rid of old versions of the data.
Like so:
Step1: CREATE TABLE new_base_table AS SELECT * from LAP (you create a new table with the “last version” of the data)
Step2: DROP TABLE base_table CASCADE (drop the old base table, it’s projections and LAP)
Step3: ALTER TABLE new_base_table RENAME base_table (rename the “last version” of the data)
Step4: recreate LAP
From now on you continue INSERTing into the base table as usual.
This method will let you know the row creation time and “update/insert” history.

The other option is to copy from time to time the data from dc_requests_issued to a regular table you can maintain
to research for data changes.
dc_requests_issued (might change) currently include the following fields:
time
node_name
session_id
user_id
user_name
transaction_id
statement_id
request_id
request_type
label
client_label
search_path
query_start_epoch
request (for example SELECT x,y,z from mytable;)
is_retry

Additionally just found a post from @Jim_Knicely (from 2014!!!) which provides some details on v_internal.dc_transaction_ends table.
Wow Time just flies.

dc_ tables are internal to Vertica and CAN change without notice. Use it at your own risk!

https://forum.vertica.com/discussion/219883/is-it-possible-to-know-when-the-data-in-a-particaluar-table-was-updated
Hope this helps you.