We have some tables of time-series data where the date-time field in the table is not a timestamp or a timestamp_tz typed field, instead it is a numeric value of the unix epoch. Complaints have come because users need to use to_timestamp functions in reporting, and this also causes some caching issues in PowerBI. One solution would be to update the tables with a timestamp-typed column. Another solution being suggested is that a view could be created over those tables that includes (adds) a "normal" timestamp column. My concern is that wouldn't the population of the view constantly redo to_timestamp conversions thus causing much more overhead than a one-time change? Would appreciate input from vertica experts on this.
Question
how best to add a timestamp field to existing table
+2
+2I would add a timestamp column to the table if you can for two related reasons: the type conversion on the fly will probably have a noticeable performance impact especially for large result sets, and also I've had issues with views containing computed columns since Vertica is not as smart about optimization in this case and doesn't always select correct projection, push down predicates through the view, etc. so you take additional performance hit just by using a view.
+2Thanks Bryan for the response! If others have experience with overlaying a table with timestamp-generating views then additional input will also be appreciated.
+2I fully agree with Bryan to add a timestamp column to the table if you can. That timestamp column can be defined with default-expression.
For example:
ALTER TABLE myt ADD COLUMN f2 TIMESTAMP DEFAULT TO_TIMESTAMP(f1);
Do only once: UPDATE myt SET f2=DEFAULT;
From size perspective, Vertica uses Julian dates for all date/time calculations, all have a size of 8 bytes, which is the same size as you probably do with INTEGER today - a signed 8-byte (64-bit) data type.
A view does incur overhead to access and compute data, and since views do not support inserts, deletes, or updates it might bring confusion to some users.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
