Is it possible to know when the data in a particaluar table was updated

in MySQL, we can use the following SQL statement to get the last update time:

Is there any SQL doing the same thing in Vertica?


  • Options
    Hi Vertica has an hiden column named by epoch this column hold the last epoch id that the given record has at a commit time . the column is created automaticly at the creation time of the table and you can simply query is value. Thanks
  • Options
    Navin_CNavin_C Vertica Customer
    Hi Jim,

    I have seen epochs table does not keep the data for long time.

    So if we update the table and use this method to check the time of the epoch, at the same instant, it will show the last updated time, but if we  query the epochs table after long interval, you will find  the epoch which the table had for last inserted record, does not exist in epochs table.

    Is there a parameter to increase data retention of this table,
    Ideally this table should show records for all epochs and there closed time as per the docs.

    Is there any other table where we can track this information.


  • Options
    Maybe you can create your own epochs table and update it every hour or so vis a cron job?

    See here for an example:

  • Options
    thanks, is there a way to get the last update time of the table?
  • Options

    Hi ,
    In Vertica 7.1 we can now set retention policy for the Data Collector tables on time based interval unites .


    Example :

    => SELECT set_data_collector_time_policy('TransactionEnds', '1 day'::interval);


    This command  set  retention policy for  dc_transaction_ends table for one day , having this setting set ,  we can easily know the update time of the last day data  using below query :



    select count(*) ,
    time_slice(tx.time,600, 'second') AS moving_interval
    from <Your table> as arc ,(select max(time) as time ,end_epoch from v_internal.dc_transaction_ends where time between sysdate()-1 and sysdate() group by end_epoch ) tx
    where arc.epoch=tx.end_epoch
    group by moving_interval
    order by moving_interval



Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file