Vertica Epoch to Timestamp

VincenzoIncorvaia

Hello guys,
I'd like to know if someone has experience in converting epoch to timestamp.
In particular, given a DB epoch, can I know (approximately) the corresponding timestamp?

Thank you very much,
Vincenzo

s_crossman

Vincenzo,

There is a way to get timestamps for epochs, but it's based on a dc table which doesn't retain a lot of history. The more active the system is the faster older epoch info is flushed. So the value of this may be on how far back you are trying to look. Here's the basics.

This select will get every epoch from the dc_transaction_ends table and show it's corresponding timestamp.
dbadmin=> select distinct(end_epoch),epoch_close_time from dc_transaction_ends where epoch_close_time is not null order by end_epoch;
end_epoch | epoch_close_time
-----------+-------------------------------
171 | 2017-08-21 13:06:33.904602-04
172 | 2017-08-21 13:28:01.340191-04
173 | 2017-08-21 13:28:18.2493-04
174 | 2017-08-21 14:12:14.616824-04
175 | 2017-08-21 14:12:24.866376-04
176 | 2017-08-21 15:15:31.030704-04
177 | 2017-08-21 15:15:31.052995-04
178 | 2017-08-21 15:15:31.073046-04
179 | 2017-08-21 15:15:31.094401-04
180 | 2017-08-21 15:18:08.831412-04
(10 rows)

This one will show the policy for that table regarding how much retention by size and/or time it has. This can be set to increase the amount of retention. It's covered in the Doc set under set_data_collector_policy.
dbadmin=> select get_data_collector_policy('TransactionEnds');

get_data_collector_policy

1000KB kept in memory, 10000KB kept on disk. Time based retention disabled.
(1 row)

I hope it helps.
Regards,
Steve Crossman HPE Vertica

sKwa

Hi!

Try dc_projection_checkpoint_epochs table:

dbadmin=> select distinct checkpoint_epoch, time from
dbadmin-> dc_projection_checkpoint_epochs limit 3;
 checkpoint_epoch |             time              
------------------+-------------------------------
               57 | 2017-07-18 14:04:33.018325+03
              107 | 2017-08-18 02:09:10.005708+03
              107 | 2017-08-15 23:18:53.028526+03
(3 rows)

VincenzoIncorvaia

Thank you very much guys,
you have been very helpful!

Best regards,
Vincenzo